Home > Home, MDX, SSAS, SSRS > Creating Date Parameters in SSRS and pass it to MDX query

Creating Date Parameters in SSRS and pass it to MDX query

Date Parameters in SSRS will open the calendar object, after selecting date it will pass to MDX as date format default in SSRS.

Let’s create one sample SSRS report with MDX.

Generate a report with birthdate as start and end date, based on these dates show employee names with gender.

Default MDX created by drag and drop in SSRS query designer for MDX as shown below:-

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Employee].[Birth Date].&[1955-05-07T00:00:00] : [Employee].[Birth Date].&[1985-05-07T00:00:00] ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

 1.DefaultMDX

If you see in above MDX the date is like its stored in the member 1955-05-07T00:00:00

 

Lets create parameters for this report.

Parameters created like in below screenshot:-

2.MDX-Parameters

 

Mapped the parameter in MDX . After modification MDX looks like

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( strtomember(@FromDate) : StrtoMember(@ToDate) ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

After completing wizard by clicking next à next it will generate report like below screenshot:-

3.DefaultReport

 

Date Parameters in above report is look like drop down not like usually we want as date calendar icon which should open as calendar and user can select date easily.

Now we need to modify MDX and parameters to provide the date calendar facility in the report.

Modify the MDX as below query:-

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( Strtomember(“[Employee].[Birth Date].&[“+@FromDate +”]”) : strtomember(“[Employee].[Birth Date].&[“+@ToDate+”]”) ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

After that please modify the parameters as shown in below screen shot:-

4.ParametersModified

 

If you run the report after changes it will throw error, modify the SSRS parameters also.

Create two more parameters which actually have the date data type.

@ BirthFromDate as shown in below screen shot

5.NewParameter

 

@BirthToDate as below screenshot:-

6.NewParameter2

 

So above created parameters will pass date as in the format to old parameters which are bond with the report

Format : 1946-06-13T00:00:00

Edit old FromDate Parameter and do as shown in below screenshot:-

7.OLD-Parameter-Modification

 

Expression pasted in above expression is

=cstr(Year(Parameters!BirthFromDate.Value)) +”-” + cstr(iif(Month(Parameters!BirthFromDate.Value)<10,”0″+cstr(Month(Parameters!BirthFromDate.Value)),Month(Parameters!BirthFromDate.Value)) )+ “-“+cstr(iif(day(Parameters!BirthFromDate.Value)<10,”0″+cstr(Day(Parameters!BirthFromDate.Value)),Day(Parameters!BirthFromDate.Value)))+”T00:00:00”

Same expression should be pasted in default value of the parameter

Same procedure for Old parameter ToDate.

Onle change is the expression as given below:-

=cstr(Year(Parameters!BirthToDate.Value)) +”-” + cstr(iif(Month(Parameters!BirthToDate.Value)<10,”0″+cstr(Month(Parameters!BirthToDate.Value)),Month(Parameters!BirthToDate.Value)) )+ “-“+cstr(iif(day(Parameters!BirthToDate.Value)<10,”0″+cstr(Day(Parameters!BirthToDate.Value)),Day(Parameters!BirthToDate.Value)))+”T00:00:00”

Both old parameters should be hidden.

Run the report, you will find the report like below screenshot:-

8.FinalReport

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: Home, MDX, SSAS, SSRS
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: