I came across an interesting MDX challenge this week; within a cube’s Date dimension, how to show December twice, once where it should be and again as the opening value for the following year. i.e. for each year I need to show Dec (prev yr), Jan, …, Nov, Dec.
Why? Well if you consider the following pivot chart, you can clearly see growth from Jan to Feb, Feb to Mar, etc., but it’s very difficult to see the growth between Dec and Jan.
So to make things easier to a user we want to repeat the Dec value and insert it before Jan as an opening value for the year. Now we can’t duplicate December’s data in the DW or Cube for obvious reasons, so we need to create a virtual copy of December using some MDX.
Step 1 – Create a new virtual month member called ‘Open’
CREATE MEMBER CURRENTCUBE.[Date].[Month Name].[Open] AS null;
Step 2 – Create a named set ‘Dec to Dec’ that includes Open as well as Jan-Dec
CREATE STATIC SET CURRENTCUBE.[Dec to Dec]
AS {[Date].[Month Name].[Open]
,[Date].[Month Name].[Month Name].[Jan]
:[Date].[Month Name].[Month Name].[Dec]};
Step 3 – Set the ‘Open’ member to show the value for ‘Dec’ in the previous year
SCOPE ([Date].[Month Name].[Open]); THIS = ([Date].[Year].PREVMEMBER, [Date].[Month Name].[Dec]); END SCOPE;
When you then select the ‘Dec to Dec’ named set in Excel we do indeed see 13 months per year, including our new ‘Open’ month. There are two problems however:
- Open appears at the end not the start. To fix this open up the ‘Field Settings’ of the ‘Dec to Dec’ field in the pivot table, select the ‘Layout and Print’ tab and untick ‘Automatically order and remove duplicates from the set’
- If you filter the pivot to only show a single year, Excel decides to hide the ‘Open’ member (it is Excel, as MDX works fine!). To fix this go to ‘PivotTable Options’ then on the ‘totals and Filters’ tab, untick ‘Include filtered items in set totals’.
Hey presto, your pivot table will now look something like this:
You can now clearly see the growth in Dec to Jan, and everyone’s happy.
<Frog-Blog Out>
