When you have a report that pulls data from an SSAS cube, you have most likely used a date range filter on which time series you would like to display. It is not so straightforward though, to create date-parameters in SSRS and in your MDX query you refer to these queries. Because MDX needs to interpret the date values in a completely different syntax, we have to format the data. This post shows you how to accomplish this. This post does not describe how you should format the MDX to make this work, I assume that you have your dataset ready to go to use in your report.
The first step is to create two dimensions for the date range. The types of these parameters is “Date/Time”. There are no specific other settings for these parameters, as these are the date pickers that the end user can use to select a date range. So when you start the report, you will only have these parameters:
The next thing to do is create parameters that contain the formatted values that the MDX query can read. To accomplish this, create two more parameters called “FromDate” and “ToDate”, of type “Text”, and set the visibility on “Internal” (this causes the parameter to be hidden, but the values will change when the selection of other parameters change). On the “Available Values” tab, select “None”. On the “Default Values”, the following expression should be used:
=”[Order Date].[Date].&[” & Format(Parameters!FromDate.Value, “yyyy-MM-dd”) & “T00:00:00]”
The second parameter should contain this expression as the default value:
=”[Order Date].[Date].&[” & Format(Parameters!ToDate.Value, “yyyy-MM-dd”) & “T00:00:00]”
When you run the report now, you will see that the selected dates will be applied to your dataset. If not, you can always set the last two parameters to “Visible” and run the report. That way you will see how these parameters are formatted. Most of the errors that I got is that the descriptions of the cube dimensions were not completely correct.
While designing data warehouses, SSIS packages, etc., like myself you’ve probably ran into the issue of having to format different date and time values that ends up in a long string of SQL code or expressions in SSIS. The purpose of this post is to collect some links to various pages where these kind of date and time formatting can be found.
Getting only date part of a datetime field
First of all, the function that I used the most is when I’m trying to get only the date part of a datetime field type. For example, when I’m looking at a sales order fact table, an order could have been placed at August 15th 2011 at 9:15am. This date would be stored as 2011-08-15 09:15:00:000. The issue here, is that it’s difficult to design your SSAS cube and use this field as a link to your time dimension table, because the time dimension table usually holds the dates only. Therefore, the following function could be used:
FLOOR( CAST( GETDATE() AS FLOAT ) )
(thanks to Ben Nadel)
When you’re using SQL Server 2008 and above, you can also use the following function that is a lot easier to use. Since the date and time fields were introduced in SQL Server 2008, this won’t work in 2005 and earlier versions:
Some simple date formats can be found here. These are just some standard SQL date formats but these could help you tremendously to avoid having to concatenate several strings together.
Calculations: First and last day of the month
You might have ran into the issue of calculating dates in relation to a certain input date. This page shows you how to get the first or last day of the month in relation to a certain date.
Calculations: First and last day of the year
Just as calculating the first and last day of the month, you can see here how you can calculate the first and last day of the year.
Other than the ones above, if you are looking for a calculation in terms of number of days, weeks, months, years, etc., the standard DATEADD and DATEDIFF functions in SQL would do the trick.
It could happen that you move around your cube files and with the deployment, you run into some errors. A common error that I’ve encountered again is the following.
The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection.
This happens mostly when you have copied a .cube and .partitions file to another project and added this to the new SSAS project/solution.
The underlying meaning of this error is that in your cube file, you have a role defined in your old project that has set specific permissions on this cube. When this role does not exist under the same name and the same permissions set in the new project, your deployment will fail with the above error.
I’ve always deleted the permissions on the cube and re-created them again with the roles that I’ve used in the new project. You can do this by opening up the cube file in Notepad or another text editor, and look for the text CubePermissions. Everything starting at this term (including the tag of CubePermissions as well), remove that node from the file.
Apart from possible other errors that you might stumble upon, your copied cube should now be able to be deployed.