Creating MDX for based on the date parameter.

In many reporting requirement we have to generate reports based on the date selected by end user or it can be for current date, current week or current year.

Here I am going how we can do same with the help of MDX –

1. When it comes to current year, current month, current date….

For creating MDX for it you can use VB function in the MDX which will help you to get current date

Example is as follows –

SELECT [Measures].[Order Quantity] on 0,

STRTOSET("[Date].[Calendar Year].&["+FORMAT(VBA!Now(),"yyyy")+"]") on 1

FROM [Adventure Works]

Same can be done for current month or day.

2. If you have to show data based on user parameter then following MDX can help you –

SELECT [Measures].[Order Quantity] on 0,

STRTOSET("[Date].[Date].&["+FORMAT(cdate(@date_param),"YY-MM-DD")+ "T00:00:00]") on 1

FROM [Adventure Works]

3. If you have two start date and End date then –

SELECT [Measures].[Order Quantity] on 0,

{STRTOSET("[Date].[Date].&["+FORMAT(cdate(@Startdate_param),"YY-MM-DD")+ "T00:00:00]")

: STRTOSET("[Date].[Date].&["+FORMAT(cdate(@Enddate_param),"YY-MM-DD")+ "T00:00:00]")}on 1

FROM [Adventure Works]

So.. This how we can create dynamic MDX…………………