12 May 2010

Year-Qtr-Month MDX Set definition for page filtering

I ran across a need for a Performance Point Server (PPS)page filter using the Date hierarchy which was a bit of a head scratcher. Here's the story and the solution. Hope you can make use of it. And I'm sure there are about 6 ways to solve this problem - the one presented here is what I came up with at the time.

Problem: I have multiple SSRS charts on a PPS page which use the same date page filter. The user requirement is to allow selection of the year, quarter, or month which is then consumed by each of the charts. The order of the date elements is to start at the most recent year and go back 5 years. If the current year is 2010 the presentation is 2010, 2009, 2008... As you expand each year (using the native tree display functionality of a PPS filter) you should see quarter, then expand quarters and see months. Sounds simple enough.

(keep in mind that the query samples below execute as standalone queries in SQL Manager. The PPS filter definition is just the set which is defined on the rows axis in each query).

The first pass at the filter definition shows the years in the range I want:Here are the results:
We can see the years are listed on the Rows, but they are not in descending order and do not display the quarters or months. I revised the above query to sort the years in descending order using the MDX ORDER() funtion. Luckily I could sort by the intrinsic member property MEMBER_KEY.

Here's the query:

And the result:

Well, the year ordering looks right, but now the problem is how do I show the quarter and month? I'll use the MDX GENERATE() function to use each year as CURRENT_MEMBER and get the descendants for each Year (which is CURRENT_MEMBER) to the month level. here's the quesry using the GENERATE(). The first set specified in the GENERATE() is the same year range set I used in the earlier queries. The second set in the GENERATE() is the DESCENDANTS() of CURRENT_MEMBER which is each Year


Here's the query:


Here's the result:
This is much closer to what I need. But in a user review they voiced a legitimate concern. They said, "I understand the descending years, but when I look at quarters and months I expect to see them in their normal calendar order." I guess this is a need you don't recognize until you see the months listed backwards. It made sense but was a bit of a puzzle to solve. The answer is to change when the ORDER() is done compared to the GENERATE(). The trick is to ORDER the year set within the first set passed to the GENERATE() function, and keep the DESCENDANTS() function (second set passed to the GENERATE() function) outside the ORDER().

Here's the query:




Here's the result:
Ok, that's it. Standby for next weeks post which shows how to replace the hardcoded begin and end years in the above set with a dynamially generated range of years.