Getting Mobile Reports to Recognize SSAS Dates

Scenario: Some of the controls in SSRS 2016 mobile reports (previously known as Datazen) require a date field. The Mobile Publisher would refuse binding the control to an SSAS shared dataset if a column of a Date data type is not present. That’s because when you create a dataset, you write an MDX query and MDX returns the field name as a string.

Solution: You can use add a calculated member to the query that returns the MEMBER_VALUE property. MEMBER_VALUE returns in the underlying data in its native data type. For this to work in Multidimensional, you need to set the Value property of the Date attribute of your Date dimension to an underlying column of a Date data type. The following example, shows a sample query that uses the Adventure Works Tabular model.

WITH
MEMBER [Measures].[Total Sales] as ([Measures].[Internet Total Sales] + [Measures].[Reseller Total Sales])

MEMBER DateCasted as
Iif([Measures].[Total Sales]=0, null, [Date].[Date].CurrentMember.MEMBER_VALUE)

SELECT

{

[Measures].[DateCasted],

[Measures].[Total Sales],

[Measures].[Internet Total Sales],

[Measures].[Reseller Total Sales]

}

ON 0,

NON
EMPTY [Date].[Date].Children
ON 1

FROM [Model]

Because the DateCasted column is now of a Date data type, Publisher now allows me to bind the dataset to a TimeNavigator control that requires a date column.