Tuesday, August 23, 2011

List Prompts for Dimensional Reports

If you are writing a dimensional report and prompting on particular member, you'd usually present this in a tree prompt. But in the case that you want to guide the prompting options a bit or if the dimension is very simple, this format might be confusing to users. For example, I have a filtering dimension called 'Annual Fund Indicator' with 1 level and 2 values ('Y' and 'N'). So I want to slice my cube based on some set of these values. The easiest way would be to use a tree prompt and allow the usser to select any combination of the flag.

The user can select 1 value, or both. There are some functional drawbacks to this though. For a complex hierarchy, a tree prompt makes sense. Users can navigate the tree and select the members that make most sense to the report. But with a simple set of selectable items, the tree becomes cumbersome administrative overhead.

  • This is the presentation of members at 1 level of a tree with no children. Users are confused that the tree prompt has the expansion + option but nothing below it
  • Users are confused about the wording on the prompt and can be confused about the lexical rules for slicing both Y and N values (they would have to select both) or 'Y' but not 'N'
  • Here the member captions are shown as values, it may be confusing to users to translate the code 'Y' to 'Transactions that have the annual fund indicator set to yes' or more functionally 'Yes, Include Annual Fund amounts'

So for these, we should use a list prompt like this.

When you select a member from a tree prompt, the value used for the parameter is a Member Unique Name (MUN). We still want each of these options to resolve to MUN addresses, but now we'll have to construct them ourselves. For the bottom 2 options 'Non Annual Fund Transactions' and 'Annual Fund transactions' I created a query to select the MUN and a Caption as the Use and Display values for this prompt.

Annual Fund Include Indicator - [Cube].[Annual Fund].[Annual Fund Include Indicator], this expression is a level so will return each member of that level when the query executes. This will be the Use Value of the prompt.

Annual Fund Label - So, I want a specialized label for each member, to do that I need to get a string value of each member to run through my case statement. This case statement uses the roleValue() and currentMember() dimensional functions to get a string value of the business key of the current member and creates an appropriate caption. This will be the Display Value of the prompt.

roleValue('expression', member) - expression can be any of these: 
'_businessKey', '_memberCaption', '_memberDescription', '_memberUniqueName' and will return a string value of the expression.
 currentMember(hierarchy) - returns the current member of the specified hierarchy, since we are traversing a level at runtime for Annual Fund Include Indicator, the current member will be each member of the level.

So that gets me the MUN and caption for those values. But I want a 3rd option for the 'Both Annual Fund and Non-Annual Fund Transactions' I created a static choice in the list with the MUN for the top of the Annual Fund Indicator hierarchy. So this will slice me both 'Y' and 'N' values when I select it (as they are children of the top of the hierarchy).

So when all this is said and done we end up with a list prompt that behaves like a complex tree prompt and I use it in my query like any other MUN filter:

[CUBE].[Annual Fund].[Annual Fund].[Annual Fund]->?Annual Fund?

I did this in IBM Cognos 8.4.1 but would work the same way in Cognos 10.

No comments:

Post a Comment