SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

I guess we’ve all seen it before, VS/SSDT/BIDS automatically generates datasets for input parameters (which is fine to start with).

But for Date/Time parameters it always (by default) selects the ‘Text’-datatype (drop-down list), instead of the ‘Date/Time’-datatype (nice calendar).


What we want to achieve
So this is what we want to achieve (in red):

SSRS datetime input parameter instead of text for mdx





Start a clean slate
First step is to drop the generated date/time parameter(s) we’re not going to use and create a new one:

SSRS datetime input parameter instead of text for mdx - properties






Make sure to select the ‘Date/Time’ as the datatype, which enables the nice calender-type input. Leave the ‘Available Values’-section unchanged/empty.


Hussle the Date-value in the correct format (YYYY-MM-DD)
A difference between both datatypes is that the ‘Date/Time’-datetype will return values formatted based on your local/regional settings (e.g. YYYY-M-D or in my case D-M-YYYY).
Where I need it to be YYYY-MM-DD (including 0’s), so something like:
2015-12-01

To achieve this, we need to cast/convert a little bit and include a ‘0’ when we need it:

=IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)



Turn it into a MDX value
The final step is to get it into the desired value for MDX:
[Dim Creation Date].[Date].&[2015-12-05T00:00:00]

Here’s the full code expression which you can use as a parameter value (expression):

="[Dim Creation Date].[Date].&["
+CSTR(DATEPART(DateInterval.Year,CDate(Parameters!DateFrom.Value)))+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))
)
+"T00:00:00]"



Use the expression as an input value for your dataset
The final step is to use the expression:

SSRS datetime input parameter instead of text for mdx - dataset properties









Done!
That’s it! Bye bye ugly drop-down list 🙂

SSRS datetime input parameter instead of text for mdx - final result











SSRS/MDX Parameter values based on a (filtered) dimension (which actually relates to records in the facts)

[Dim Period] dimension values:
For this demo, I have a dimension called [Dim Period] in my SSAS cube which looks like:

ssas-cube-dim-period-id-values


All values
So here’s how you could diplays all values (children) of a certain dimension:
ssas-cube-dim-period-id-values-all-code

ssas-cube-dim-period-id-values-all-results



Filtered by NOW()/FORMAT

([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")


ssas-cube-dim-period-id-values-filtered-by-now-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Filtered by HAVING

HAVING NOT ISEMPTY([Measures].[--Value--])


ssas-cube-dim-period-id-values-filtered-by-having-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Code
Here’s the MDX script I used:

--All members in the [Dim Period] dimension:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM [CUBE]

--Filtered based on the current date:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT
--Here's a trick that will work on date/time values:
([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)

--But for other non time related dimensional members, you need to a different method:

WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC)
HAVING NOT ISEMPTY([Measures].[BS Balance])
ON ROWS
FROM [CUBE]








SSRS MDX – Month/period parameter dataset (until current month + order descending)

Here’s how to accomplish this:
mdx-ssrs-month-period-until-now-order-descending-code

The results:
mdx-ssrs-month-period-until-now-order-descending-results

The code:

WITH MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Dim Period].[Id].CURRENTMEMBER.LEVEL.ORDINAL

SELECT
{
[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT ( [Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)








MDX Cheat Sheet – Calculated Measures

Here’s an addition to the MDX Cheat Sheet I posted earlier:
https://clinthuijbers.wordpress.com/2014/05/03/mdx-cheat-sheet/

But this one focusses on SSAS/MDX Calculated Measures (e.g. Full Year SUM() or YTD())

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

Dim Period Hierarchy

How it looks like:
MDX Cheat Sheet - Calculated Measures - MDXCode1
MDX Cheat Sheet - Calculated Measures - MDXCode2

Here are the results:
MDX Cheat Sheet - Calculated Measures

Download it!:
MDX Cheat Sheet (Calculated Measures)








MDX Cheat Sheet

I’ve Googled for MDX Cheat Sheets, found a couple, but never exactly what I was looking for.
So I made my own, hope this one explains things a lot easier for you (and also as a reference for myself) 😉

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

Dim Period Hierarchy

Code:

MDX Cheat Sheet

Results:

MDX Cheat Sheet results

Here’s a more graphical explanation on how things work:
SSAS Dimension hierarchies levels

Download it!
SSAS MDX Cheat Sheet


Here’s another MDX Cheat Sheet I posted after, all about Calculated Measures:
https://clinthuijbers.wordpress.com/2014/08/15/mdx-cheat-sheet-calculated-measures/








Welcome BI Semantic Model (BISM)!

Frank from MIB
Microsoft announced the BI Semantic Model @ the PASS Summit.
Chris Webb was there and he gave his opinion on his Blog

The quotes below are from Chris Webb’s blogpost here. Thanks for that Chris!

 

The BISM – BI Semantic Model – is the name for the new type of Analysis Services database that gets created when you publish a PowerPivot model up to the server. It’s SSAS running in the special in-memory mode, and SSAS instances will either work in this mode or in regular MOLAP mode. In Denali we’ll be able to install a standalone instance of SSAS running in in-memory, BISM mode without needing Sharepoint around.

MS are clear that BISM is the priority now. While MOLAP SSAS isn’t deprecated, the efforts of the SSAS dev team are concentrated on BISM and PowerPivot and we shouldn’t expect any radical new changes. I asked why they couldn’t have just kept SSAS as it is today and bolted Vertipaq storage on as a new storage mode (we will, of course, be able to use SSAS cubes in ROLAP mode against SQL Server/PDW with Vertipaq relational indexes) but I was told that it was seriously considered, but didn’t turn out to be easy to implement at all. The other question I asked was why they are abandoning the concept of cubes and explicitly multidimensional ideas in favour of a simpler, relational model, and they told me that it’s because multidimensionality put a lot of people off; I can see that’s true – yes, a lot of people have been converted to the OLAP cause over the years, but we all know that many relational people just can’t stomach/understand SSAS today. The vast majority of people who use SSRS do so directly on relational sources, and as we know while there’s a great demand for things like Report Builder, Microsoft has had nothing that worked really well to enable end user reporting in SSRS; BISM, as I said, is aimed at solving this problem.





MDX CASE Example (Calculated Member – DateTime Format)

Rewrote my previous post, so it can be used within your MDX statement.

WITH
MEMBER [Measures].[MonthName] AS
CASE MONTH([Time].[Month].MEMBERVALUE)
WHEN 1 THEN “January”
WHEN 2 THEN “February”
WHEN 3 THEN “March”
WHEN 4 THEN “April”
WHEN 5 THEN “May”
WHEN 6 THEN “June”
WHEN 7 THEN “July”
WHEN 8 THEN “August”
WHEN 9 THEN “September”
WHEN 10 THEN “October”
WHEN 11 THEN “November”
WHEN 12 THEN “December”
END

[Time].[Month] value= [Time].[Month].&[2010-01-01T00:00:00]

For “January 2010”, just use:
WHEN 1 THEN (“January ” + STR(YEAR([Time].[Fiscal Month].MEMBERVALUE)))