Friday, August 5, 2011

Filter SSAS Cube Data for a Date Range

When we retrieve cube data for SSRS reports, we may want to filter those data for a particular date range. In the below example I filter the Sales data of Stores for a Month range.

SELECT [Measures].[Sales] ON COLUMNS,
[Stores].[Store].members on ROWS from [Sales]
WHERE ([Date—Year_Month_Day].[All Date—Year_Month_Day].[" & Parameters!FromYear.Value & "].[" & Parameters!FromMonth.Value & "]:[Date—Year_Month_Day].[All Date—Year_Month_Day].[" & Parameters!ToYear.Value & "].[" & Parameters!ToMonth.Value & "])

That's all. It takes the From and To Year, Month values from those parameters and filter the Sales measure value. In this example I used a created hierarchy([Date—Year_Month_Day]) for the Date dimension.

No comments:

Post a Comment