T-SQL v MDX: Pick the hierarchy and populate the report parameter values

April 2, 2015

When dealing with report parameters, you want the experience to be as seamless as possible for the end user. In SQL Server Reporting Services, we have the tremendously useful feature of having dynamic parameters, in which you can set a particular report parameter based on the choice of a previous one. For example, if you have parameters such as Country and City, you can set it up so that you pick the Country as Australia, and get the City choices filtered down to Adelaide, Melbourne, and Canberra, but if you choose the UK as your Country, your City choices become London, Brighton, and Dover.

In T-SQL, we do this by making the City parameter use (for its Available Values set) a dataset which is dependent on the Country parameter. Something like “select City from Cities where Country = @country” – it’s quite straight forward.

But when you want to be able to pick something entirely different, the story becomes more complex.

Suppose you want to be able to filter a report either by Size or by Type. Typically you’d do this with two parameters, with an Any or All option near the top of each list. This makes a lot of sense, and even caters for the scenario of filtering the report by both Size and Type. But sometimes this doesn’t quite suit the situation.

Imagine you’re filtering a report by date. You can either filter by the Financial Year, or by the Calendar Year. Or in fact, by the Week, Month, or Quarter. One option is to just pick the Start Date and End Date, and let the user work out when the Financial Year should be – another option is to let the user choose the type of period, and then populate the second parameter with a bunch of choices accordingly.

In T-SQL, we could have a query which leverages a table of numbers to produce a bunch of reporting periods based on the type. Something like this:

DECLARE @periodtype VARCHAR(50) = 'calweek';    
SELECT EnglishMonthName + ' ' + CAST(CalendarYear AS CHAR(4)) AS PeriodName        
, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd         
FROM dbo.DimDate         
GROUP by CalendarYear, MonthNumberOfYear, EnglishMonthName         
HAVING @periodtype = 'month'         
UNION ALL         
SELECT CAST(CalendarYear AS CHAR(4)) AS PeriodName         
, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd         
FROM dbo.DimDate         
GROUP BY CalendarYear         
HAVING @periodtype = 'calyear'         
UNION ALL         
SELECT CAST(FiscalYear AS CHAR(4)) AS PeriodName         
, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd         
FROM dbo.DimDate         
GROUP BY FiscalYear         
HAVING @periodtype = 'fiscalyear'         
UNION ALL         
SELECT 'Week ' + CAST(WeekNumberOfYear AS VARCHAR(2)) + ', ' + CAST(CalendarYear AS CHAR(4)) AS PeriodName         
, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd         
FROM dbo.DimDate         
GROUP BY CalendarYear, WeekNumberOfYear         
HAVING @periodtype = 'calweek'         
ORDER BY PeriodStart;

DECLARE @periodtype VARCHAR(50) = 'calweek';

SELECT EnglishMonthName + ' ' + CAST(CalendarYear AS CHAR(4)) AS PeriodName

, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd

FROM dbo.DimDate

GROUP by CalendarYear, MonthNumberOfYear, EnglishMonthName

HAVING @periodtype = 'month'

UNION ALL

SELECT CAST(CalendarYear AS CHAR(4)) AS PeriodName

, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd

FROM dbo.DimDate

GROUP BY CalendarYear

HAVING @periodtype = 'calyear'

UNION ALL

SELECT CAST(FiscalYear AS CHAR(4)) AS PeriodName

, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd

FROM dbo.DimDate

GROUP BY FiscalYear

HAVING @periodtype = 'fiscalyear'

UNION ALL

SELECT 'Week ' + CAST(WeekNumberOfYear AS VARCHAR(2)) + ', ' + CAST(CalendarYear AS CHAR(4)) AS PeriodName

, MIN(DateKey) AS PeriodStart, MAX(DateKey) AS PeriodEnd

FROM dbo.DimDate

GROUP BY CalendarYear, WeekNumberOfYear

HAVING @periodtype = 'calweek'

ORDER BY PeriodStart;

In this scenario, we basically choose which of the queries we’re going to run using a variable, and a query parameter in SSRS. It works nicely, and thanks to Startup Expression Predicates in Filter operators, only actually runs one of the queries.

, with the results for ‘month’ being something like:

You may be thinking about the fact that SSRS will only let us have a single value for a parameter entry – not two. This can be overcome by concatenating the two values, and then splitting them in the query, or by having these values stored in a separate table. To concatenate them, just use:

1	, CAST(MIN(DateKey) AS CHAR(8)) + CAST(MAX(DateKey) AS CHAR(8)) AS PeriodValue

And then in the actual report query, doing something like:

1	WHERE f.DateKey BETWEEN CAST(LEFT(@Period,8) AS INT) AND CAST(RIGHT(@Period,8) AS INT)

But in Analysis Services, things are done a little differently. We have hierarchies instead, and need to refer to them somehow. We need to be able to pull out items such as [Transaction Date].[Fiscal Calendar].[Fiscal Month].&[201501], or [Transaction Date].[By Week].[Week].&[20141122].

We’re not supposed to create MDX tuples using T-SQL. You could, but it’s not the right approach. It’s almost never the right approach.

I’m going to start by letting the user select a @ReportPeriodType, which is going to be one of the following values:

[Transaction Date].[Fiscal Calendar].[Fiscal Month]
[Transaction Date].[Fiscal Calendar].[Fiscal Quarter]
[Transaction Date].[Fiscal Calendar].[Fiscal Year]
[Transaction Date].[Calendar].[Year]
[Transaction Date].[By Week].[Week]

And then use a query like this:

WITH

MEMBER Measures.ParamName AS STRTOVALUE(@ReportPeriodType + ".Hierarchy.CurrentMember.Name")

MEMBER Measures.ParamUniq AS STRTOVALUE(@ReportPeriodType + ".Hierarchy.CurrentMember.Unique_Name")

SELECT

{ Measures.ParamName, Measures.ParamUniq } ON COLUMNS,

STRTOSET(@ReportPeriodType + ".Members") ON ROWS

FROM [MyCube]

And if I want to filter this to only things from the last 2 years, I could use:

1	WHERE (STRTOMEMBER("[Transaction Date].[Date].&[" + @today + "]").LAG(730) : STRTOMEMBER("[Transaction Date].[Date].&[" + @today + "]")

Let’s look at what I’m doing here. I’m passing in a parameter which is a string, which is the level of a hierachy. I’m going to use the members of this level on the Rows axis. To construct that, STRTOSET(@ReportPeriodType + ".Members") does the trick nicely. I could just leave it at that, and use the MEMBER_CAPTION and MEMBER_UNIQUE_NAME properties of each dimension member, but the field name will change according to the level, and I don’t want to have to work out the impact of this in SSRS. It’s much easier to have a couple of calculated members to grab the Level’s Hierarchy’s CurrentMember’s Name and Unique_Name, which I throw onto the Columns axis, and I’m sorted. My WHERE clause works just fine, so long as I’m not grabbing data out of the [Date] hierarchy, for filtering the list of parameters down to a manageable size. If I had a hidden hierarchy, I could easily pass that in with confidence that my report writers aren’t going to try to ask for hierarchy members from it.

Notice that the main part of my query has no mention of the dimension name, or hierarchy name, or anything. This can be used for the members of ANY hierarchy, in ANY dimension. I simply need to pass in the Dimension.Hierarchy.Level string that I want. Here are some of the results if I pass in [Transaction Date].[Fiscal Calendar].[Fiscal Month].