Generally any SSRS report contains one date parameter. User never prefers list of values for date parameter, they always want to choose the date value from the Calendar Date Picker. It is very easy to use Calendar Date Picker when we create report against any relational database, but when we create SSRS report against SQL Server Analysis Services then we can’t directly feed Calendar Date Picker value into the MDX query.
This article will answer for these questions:
How to feed calendar date picker value into MDX query?
How to use Calendar Date Picker parameter for MDX based reports?
What are the advantages of Calendar date picker over list of values parameter?
Problem
Most SSRS reports contain at least one date parameter and users prefer to use a Calendar Date Picker. It is very easy to use a calendar date picker when we create a report against a relational database, but when we create a SSRS report against SQL Server Analysis Services then we can't directly feed the Calendar Date Picker value into a MDX query. This tip will demonstrate how to feed a calendar date picker value into a MDX query, how to use a calendar date picker for MDX based reports and the advantages of a calendar date picker over a list of values.
Solution
This tip assumes that you have previous experience building a simple SSRS report against an Analysis Services Data Source. In this tip we will first look at the problem with a simple example and later I will demonstrate the steps to solve the problem.
Lets understand the problem with a simple example
Step 1
I am creating a simple SSRS report against MDXStepByStep SQL Server Analysis Services database. I have already created the data source connection for the MDXStepByStep database. After creating the data source connection I am creating my dataset which gives product Sales Amount information at the day level. My dataset contains one Date Parameter; you can refer to the below image.
Step 2
At the time of the dataset creation if you declare a query parameter then SSRS automatically creates the report parameter along with its parameter dataset. As you can see from the below image @OrderDateDate report parameter has been created automatically. By default all Parameter datasets are hidden, if you want to see Hidden Datasets then right click on Datasets and click on "Show Hidden Datasets".
Once you click on "Show Hidden Datasets", all hidden datasets will be visible. As you can see from the below image dataset OrderDateDate is now visible.
Step 3
I am inserting a Tablix for data viewing. I have chosen all the data fields from DataSet1 in the Tablix. After this modification my report looks like the below image.
Now if I preview the report, I get all the date values in a parameter dropdown as a list of values. All these date values are coming from the OrderDateDate dataset. You can refer to the below image.
If I choose July 1, 2001 from the parameter list and click on "View Report". The image shown below is the output of the report for July 1, 2001 parameter value.
Using a Calendar Date Picker for the Parameter Value
Now I want to use a Calendar Date Picker in place of this list of values. You might be thinking why do I need a Calendar Date Picker if a list of values already fills the requirement. Actually there are couples of advantages of using calendar date picker:
- Report execution time will be faster; this is because the Calendar Date Picker parameter doesn't need a separate dataset to get date values from the database. Executing a separate query to get all date values from the database is an extra burden on SSRS. The Calendar Date Picker is a SSRS built-in feature which gives all date values without hitting your database.
- Navigation will be very easy. You can easily select any date from the calendar date picker, but you struggle to find a particular date from the list of values.
- It can save a lot of time for the user.
The following steps will show how you can set this up.
Step 1
Right click on @OrderDateDate report parameter and click on "Parameter Properties", it will open a Report Parameter Properties window. In the General tab change the parameter Data Type to Date/Time from Text and make sure "Allow Null Value" and "Allow Multiple Values" check boxes are unchecked. Your changes should look like the below image.
Step 2
Now click on the Available Values tab; here you can find that for the @OrderDateDate parameter, values are coming from the OrderDateDate dataset. You can refer to the below image.
Choose None as shown below and click OK.
Step 3
Right click on the OrderDateDate dataset and click on "Query". It will open the query designer window. We need to examine the OrderDateDate dataset MDX query. Let's execute the MDX query by clicking on the Execute button. On successful execution your MDX Query will return 4 columns. You can refer to the below image.
If you look at the Query execution result then you will notice that the ParameterCaption column data was used as a Label Field for the @OrderDateDate parameter and the ParameterValue column data was used as the Value Field for the @OrderDateDate parameter. You can confirm this from Step 2. When I selected July 1, 2001 as the parameter value behind the scenes the [Order Date].[Date].&[20010701] was passed into the MDX Query.
At this stage if you preview the report for July 1, 2001 parameter value then it will throw an error. This is because the parameter value 7/1/2001 12:00:20 AM will be passed into the MDX query. If we convert the parameter value in this[Order Date].[Date].&[20010701] format then the MDX query will accept the parameter value and the report will execute successfully. So our ultimate task is to generate the parameter value in MDX format. In the next step I will build the expression which will generate the parameter value in MDX format.
Step 4
Right click on Dataset1 and click on "Dataset Properties". It will open the Dataset Properties window. Click on the Parameters tab and click on the Parameter Value Expression button. You can refer to the below image.
Once you click on the Parameter Value Expression button, it will open an Expression window. Here we have to build an expression that will be generate the parameter value in MDX format, so my ultimate task is to get the parameter value in this format [Order Date].[Date].&[20010701].
The below Expression will return the parameter value in the required MDX format. In the below expression I am usingformat(Parameters!OrderDateDate.Value,"yyyyMMdd") which will return a value like 20010701. This expression may differ in your case, so make the appropriate changes in your expression.
="[Order Date].[Date].&["+format(Parameters!OrderDateDate.Value,"yyyyMMdd")+"]"
My Expression window looks like the below image.
Step 5
We have done all required changes, let's preview the report. I will select July 1, 2001 from the calendar date picker and click on "View Report". You can refer to the below image.
Congratulations! Now the report is using a Calendar Date Picker parameter. You can verify the results from the below image.
Step 6
You can now delete the OrderDateDate dataset because it is not required anymore.
