MDX Query in SSRS Shared DataSet giving wrong Datatype

Question

I have a shared dataset for a Mobile Report on SSRS 2016. The dataset sits on a SSAS data source, and has an MDX query to pull from the cube.

The primary key pulled back from the query is a date, and is formatted as such in SSAS; however when I use the dataset in Mobile Report Publisher it doesn't acknowledge it as a date datatype, it thinks it's a string. This means I can't use the dataset as a main series for a time chart, as it doesn't think there's anything formatted as a date.

The below is the section of MDX that I think is causing the issue.

SELECT
{
[Unresolved], [Assigned to Provider], [Unresolved past due date], [Other past due date], [Provider past due date], 
[Company past due date], [Provider PDD Daily Change], [Company PDD Daily Change], [Other PDD Daily Change], [Company pre due date], [Company PRDD Daily Change]
} ON COLUMNS,
NONEMPTY(
{
    [Date Snapshot].[Date].[Date]
}) ON ROWS
FROM [Source]
WHERE
( FILTER([Date Snapshot].[YQMD].[Date], [Date Snapshot].[YQMD].CURRENTMEMBER.MEMBER_KEY >= '2015-08-15') )

mprost mprost · Accepted Answer · 2016-11-10T07:54:59

Update: since SQL Server 2016 SP1 the workaround explained below is no longer necessary and dates should be properly recognized without doing anything special:

To try this new functionality out, create a simple MDX query using the Query Designer that includes a date field from your data model. When you consume that shared dataset with Mobile Report Publisher, it will properly recognize the date field from your query without an additional calculated measure being required.

The original text of the answer follows:

The reason as to why this happens is explained in the official Microsoft documentation:

The default return type for Analysis Services queries is a string. When you build a dataset in Reporting Services Report Builder, the string type is respected and gets saved to the server.

However, when the JSON table renderer processes the dataset, it reads the value of the column as a string and renders strings. Then when SQL Server Mobile Report Publisher fetches the table, it also only sees strings.

The official workaround is also explained there:

The workaround for this is to add a calculated member when you're creating a shared dataset in Report Builder.

An example of such a calculated member (taken from here):

cdate(format([Date].[Date].CURRENTMEMBER.MEMBER_VALUE, "yyyy-MM-dd"))

But this workaround (as pointed out by Victor_Rocca in a comment to the official Microsoft documentation) has a big problem:

The downside to this is that it will return all the dates that have been selected in your query, not just the ones that have a value associated with it in your cube (essentially removing the NON EMPTY behavior from the MDX query). When looking at a lot of records across time, this could significantly impact your report performance.

So I think there's a better workaround which is creating a calculated field in the dataset:

with an expression such as this one (assuming the name of your field is 'Date' and that it returns unique names such as [Date Snapshot].[Date].[Date].&[2010-01-01T00:00:00] and [Date Snapshot].[Date].[Date].&[2010-12-31T00:00:00]:

=CDate(mid(Fields!Date("UniqueName"),33,10))

This way you don't have to manually edit your MDX and NON EMPTY behavior is preserved. This new field gets correctly identified as date datatype in Mobile Report Publisher:

In any case, help is on the way, according to this post based on information obtained at last Pass Summit (October 2016), the following is being developed:

Better support for generated MDX. The current date workaround will no longer be needed and make everyone’s life easier.

MDX Query in SSRS Shared DataSet giving wrong Datatype

6 Answers