Dimensional Current Month Part 2

In a previous post, it was shown how to obtain the current month in dimensional package. That was on the assumption that the Member Unique Name (MUN) can be generated using a logical function. In the unfortunate event that, that is not possible, here are a couple of techniques to get the current month.

I. Modify your data in  such a way that the last value of your Month level is the current month. For example today is May 2014, that month should be the last value of your month level. Then you can use the function closingPeriod:

[Current Month] = closingPeriod([Package].[Time].[Time].[Month]

Most of the time though, future months are present in the Month data item. So another technique to use is:

II. Create an attribute that will have a value of ‘Y’ if the member is the current Month. Something like:

Set this attribute as the Member Description in the month level of your time dimension then publish your package.

In your report you can use the RoleValue function together with filter to obtain the member with the ‘Y’ value, which is the current month (or more accurately the member which value is the same as the current month).

Stripping Dimensionality of a Member

For those who are new to DMR, one should always remember that working with dimensional data is different from working with relational data. It means that for dimensional data, the value you see displayed is the caption, while the relationships behind the members and the measure are based on business keys. Unlike relational, dimensional data roll ups are based on business keys, not the displayed value or captions. The image below shows the difference for the given hypothetical data:

There are two Jan, Feb and Mar in Dimensional Rollup Output because it is getting rolled up based on their month keys. Since Jan of 2013 has a key of 201301 while Jan of 2014 has a key of 201401, their values will not be combined. However their captions is the one getting displayed, not the businesskeys (unless explicitly specified). There is a simple trick to ‘remove the dimensionality’ of the members. First take the caption, then convert it into string. I usually do it this way:

 [Rel Month] = substr(caption([Dim Month]);1)

Here is the new output.

Use carefully though, for complex reports, this might compromise the performance.

Top 5 Report

I see this asked a lot on forums. Its really simple but can be a quite challenging issue for beginners. They usually know how to compute for the top 5 (or 10 or 20), but how to display only the top 5?

Here is a report that will display the Quantity sold for each month and show the top 5 months with most quantity sold in descending order. Also shown is how Rank is calculated.

If we run the report we get the following result:

Now the question is, how do we display the top 5 only? The obvious answer is to filter [Rank] < 6 (or [Rank] <= 5). But if you add this filter in your query, it does not seem to work (for relational reports).

The solution is rather simple; add the filter condition in the detail filter section of your query. Then set the application property to ‘After Auto Aggregation’.

Below is our final output.

Dimensional Assignment

This is another very basic and simple thing, yet it gave me a very big headache during the early part when I was converting reports from relational to dimensional.

In relational models, we usually assign a value from a prompt this way:

[Month] = ?prompt_month?

This assignment is usually placed in the Detail Filters section, thus it is a filter.

In dimensional models, filtering and assigning a prompt value is quite different. Assigning a value from a prompt is done using the ‘->’ operator:

[Month]->?prompt_month?

This is done in the data item section. Thus this operator is choosing a certain member… which at first, sounds like the same thing as a filter. Well maybe in a sense it is a filter of a single value.

Because in filters you can choose a range (between), several values (in), values greater than or less than (>=, <=),  and not equal to (<> or is it !=). Assignment operator cannot do these things. It can only assign one value.

Access User's My Folder

I recently found out that an administrator can access user’s My Folders  in a ‘limited’ capacity. Here’s how:

1. Open Cognos Administration

2. Go to Security Tab. Open LDAP folder (this is the only example I can provide)

3. Navigate to the location of the user or search for the user. A user in Cognos should be clickable. Click on the user to access its properties.

4. Click on My Folders and the contents of the user’s My Folders would be accessed.

The administrator can now copy and paste reports and other objects to the user’s My Folders.

Incorrect Percentage Summary

Lots of times we come across a report that has a percentage computation and a row summary. Upon checking the percentage summary, we realize it is incorrect. The percentage summary is being added instead of being computed using the summary totals. The image below shows a simple example:

As you can see, the Target% summary is incorrectly calculated as the sum of 112.1 + 262.3. The correct value is 192.3 (4323/2248).

This is caused when the summary was made before adding percentage calculation. The more complicated fix is to delete the summary, add the percentage calculation, then summarize the data.

A simple fix can be done however, by setting the Solve Order of the percentage data item to a higher value. This will result to calculating that data item last. In this case, the Solve Order property is set to 2.

After running the report, the correct value is obtained.

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:

Prompt Multiple Members, Display All if None is Chosen

I came across this code in one of our existing dimensional reports. This will prompt for members of a certain level, display all of the members of that level if none is chosen, otherwise display only the chosen members. The chosen level is also shown at the left side of the screenshot.

The image below is how the report looks. The columns display the chosen members (with amounts – the columns are suppressed). If the prompt is left bank, all members of the level will be displayed.