Monday, 30 November 2015

Dimensional Current Month

One of the first things that puzzled me when I tried to do dimensional reporting was how to get the current month. In relational packages, it was very easy, just compare the month value with the current_date (provided they are the same format of course). Doing things dimensionally however is very, very different. But once you get used to it and you know your way around, it is very easy.


There are a couple of ways to automatically get the current month (or day or week or year) depending on several factors. Here is one of them:
1. Check the Member Unique Name (MUN) of your month members by right clicking a member and select Properties:

2. If your MUN is something logical like the one in the image above, then you are in luck!!! As you can see, the MUN of [2014/May] member (the current month as of this writing) consists of:
  • Its dimension path: [Package].[Time Dim].[Time Dim].[Month]
  • The expression ‘->:[PC@MEMBER].’
  • Date code of the month starting day (20140501), a dash (‘-’), and date code of month ending day (20140531) enclosed in square brackets.
3. Create a data item, with a macro that will result to the MUN string:

The image below shows the data item named Current Month in a list (left) and the output when I ran the report:

No comments:

Post a Comment