Wednesday, March 5, 2008

Current Date Parts for SSRS Reports using SSAS Cubes

Recently, while working with a client on the development and deployment of a set of SSRS reports using SSAS as a data source, we came up with a nifty method of defaulting date-part report parameters to the current date.

When creating SSRS reports based on SSAS, a common scenario is to want date-part parameters that map to various attribute hierarchies within a date dimension. For example, in most SSAS solutions, a date dimension has attribute and user hierarchies that contain parts of a date. If these are added to a report as parameters, you might want the various date parts to default to parts of the current date. So, a report parameter based on a year attribute might need to default to the current year; a report parameter based on a month attribute might need to default to the current month.

Most solutions that I've seen to this problem go about it via MDX (by adding new "current date" calculated members to the date dimension) or via SSRS expressions (using various functions to create the right default values). The solution we came up with, however, relies on the fact that the date dimension in SSAS is typically processed from an underlying date table in the relational database used as the data source for SSAS. Let's look at an example of how this might work (using Adventure Works).

First, let's create a new report for Adventure Works using SSAS as the data source. In an SSRS project, you can use the Report Wizard to create a new report. Let's assume you need to create a report showing reseller sales, allowing users to filter the report by year and month. Using the Query Builder made available in the Report Wizard, we can create the following query for the report:

SSAS Query

Notice the parameters that are included (for the [Date].[Calendar Year] and [Date].[Month of Year] attributes) along with the default values ([CY 2001] and [July] respectively) that I selected. After finishing the Report Wizard and then applying a bit of formatting to the report, here's what it might look like:

Initial Reseller Sales Report

Notice the report parameters at the top, along with the default values that got filled in (based on the selections I made when the parameters were created in the Query Builder). If you inspect the report parameters, you can see what the format of each of the default values looks like:

Year Parameter Default Value Format

Month Parameter Default Value Format[4]

Notice that the default for the year parameter is the string "[Date].[Calendar Year].&[2001]" and the default for the month parameter is the string "[Date].[Month of Year].&[7]". If you look at the designs of these attributes in the SSAS solution, these map to values in the key columns DimTime.CalendarYear and DimTime.MonthNumberOfYear.

Now, suppose users of this report want the year parameter to default to the current year and the month parameter to default to the current month. Within the AdventureWorksDW relational database, the following query returns the key columns listed above for each date in the DimTime table:

select
CalendarYear
,MonthNumberOfYear
from
dbo.DimTime

If we apply some formatting to the columns returned, we can get values back that look like the default parameter strings noted above:

select
'[Date].[Calendar Year].&['
+ convert(varchar, CalendarYear)
+ ']' as CalendarYearValue
,'[Date].[Month of Year].&['
+ convert(varchar, MonthNumberOfYear)
+ ']' as MonthofYearValue
from
dbo.DimTime

And if we apply a where clause to the query, then we can return the formatted values for a single date -- in particular, the current date (likely using the getdate() function). Now, in the AdventureWorksDW database, the DimTime table doesn't have dates up through the current date; thus, we'll simply ask for the maximum date in the table. And we'll wrap this query into a stored procedure named dbo.GetDefaultDateParts:

create procedure dbo.GetDefaultDateParts
as

select
'[Date].[Calendar Year].&['
+ convert(varchar, CalendarYear)
+ ']' as CalendarYearValue
,'[Date].[Month of Year].&['
+ convert(varchar, MonthNumberOfYear)
+ ']' as MonthofYearValue
from
dbo.DimTime
where
-- FullDateAlternateKey = convert(varchar, getdate(), 101)
FullDateAlternateKey =
(
select
max(FullDateAlternateKey)
from
dbo.DimTime
)

With this stored procedure in place, we can now update the report to use these new default values. First, we need to add a new data set to the report, based on the AdventureWorksDW relational database, calling this new stored procedure. Here's what the properties for the new data set might look like:

Data Set for GetDefaultDateParts Stored Procedure

And here's what a query of the data set would return:

GetDefaultDateParts Data Set Results

So, with this new data set available, we can simply update the report parameters such that each parameter's default value comes from the data set (instead of being based on the values that were originally selected when the report parameters were created). Here are the updated report parameters:

Updated Calendar Year Report Parameter

Updated Month of Year Report Parameter

Note that each parameter has been set to be single-valued (although this doesn't necessarily have to be the case) and that each parameter now gets its default value from the appropriate field of the new data set.

Finally, previewing the report will show that the parameters get filled in with our new default values (2004 for the year and August for the month):

Report with New Default Values for Parameters

Now, in this particular case, it looks like Adventure Works didn't have any reseller sales for August 2004. So, the default values we created via the new stored procedure might not be optimal. But, now that we have a stored procedure for returning the default values, it could be updated to return the date parts for the last date with data, the last date of the last "closed" financial month, or just about anything else that makes sense for our users and our business scenario. The stored procedure could even be updated to accept parameter values to control it in various ways, with different reports calling for different dates for example.

There are certainly some cases in which this may not work. For example, if you are using an SSAS-based date dimension (with no underlying date table in the relational database) or if have a very slim date dimension table and are creating additional columns within your DSV. So, your mileage may vary. But, for quite a number of scenarios and situations, this seems to work out well and avoids some of the complexity I've seen people create just to do this type of thing.

I've uploaded a copy of the Reseller Sales report sample displayed above along with a T-SQL script for creating the stored procedure. Both are available here:

Give it a try and let me know if this seems like a useful technique...

2 comments:

Huslayer said...

Perfect Article, keep up the good work, god bless you and your family for sharing the knowledge.

your article has enlighten me of what I was doing wrong in my query.

Thanks

PRakehcha said...

Question Error: Overload resolution failed because no accessible 'DatePart' can be called without a narrowing conversion Pin

Hi,

I want to add a date filter parameter .But I get an error when I use this expression..
Can you please help?

I am trying to get the last sundays date from the current date:

="[Date].[Date].&[" & Format(CDate(DateAdd("d", - DatePart("w", Now(), 1)-6, Now()), "yyyyMMdd")) + "]"