IBM Cognos Proven Practices
Using Relative Time Categories within Report Studio Prompts
Nature of Document: Tip or Technique; Product(s): IBM Cognos BI; Area of Interest: Reporting
This content is part # of # in the series: IBM Cognos Proven Practices
This content is part of the series:IBM Cognos Proven Practices
Stay tuned for additional content in this series.
This guide demonstrates a method of using transformer's relative time categories within Report Studio Prompts. The guide shows how to create prompts that default to a relative time category value whilst also allowing the user to use any other time category. The methods shown do not use any Java scripts.
The guide is applicable to IBM Cognos Report Studio versions 8.4 and above.
Exclusions and exemptions
This technique is only suitable for OLAP sources that have relative time categories.
Users are able to use Relative time categories when creating self authored reports with Analysis Studio or Query Studio. Professional Report authors often want to provide similar functionality in reports that they create within Report Studio, giving users the ability to run a report for any selected time category or for a relative time category.
By providing access to relative time categories users have a better experience using professionally authored reports:
- When scheduling reports.
For example prompts answers do not have to be constantly changed to take into account a month change.
- When running reports.
Prompts can be set to default to the relative time category – for example defaulting to the current month.
The techniques demonstrated by this paper show how to provide this functionality without the use of Java script. The techniques will use a typical powercube that contains a current month relative time category. Whilst the technique demonstrates current month the technique will work for any relative time category or for multiple relative time categories within the same prompt.
Two techniques are shown. The first is a simple report where the relative time is either used to filter or slice the report. The second is where the relative time category is used with additional relative time functions within the report.
Using Relative Time Categories to Slice or Filter
This section shows how to provide a prompt that will allow the user to filter on the current month or any other month from the time dimension.
Throughout this example we will use the Great Outdoors Sales (cube) sample package which is based on the Great Outdoor Sales cube.
Figure 1: Great Outdoors Sales (cube) metadata tree
Within the time dimension of this cube we can see that:
- There is a hierarchy of Years that has Month Level.
- There is a hierarchy of Current Month. That has a member of Current Month with a child of 2007/Jul. The hierarchy has a level of Current month and another level of Month.
Here is an example report that we have been asked to produce:
Figure 2: The IBM Cognos sample crosstab report
The report has:
- A measure of Revenue.
- Product Line as rows.
- Retailer type as columns.
The report author wants to provide the user with the ability to run the report for any chosen month and also allow the user to choose current month. We will produce this report.
First we will start by producing the basic crosstab.
- Open Report Studio using the Great Outdoor Sales powercube as a package.
- Create a new crosstab report.
- Drag revenue to the measures area of the crosstab.
- Drag the Product Line level from the Products dimension to the rows.
- Drag the Retailer type level from the Retailers dimension to the columns.
The report should look like:
Figure 3: The IBM cognos sample report in Report Studio
We will now add the prompt to the report that allows the user to filter on a month. We will add this prompt to the report page itself to give a more dynamic report.
- Delete the Block containing the title text from the Page Header.
- Drag a Value Prompt from the Insertable Objects toolbox into the page header. The prompt wizard for the Value prompt will start.
- Change the Create a new parameter to ChosenMonth as in Figure 4.
Figure 4: Prompt Wizard: Parameter
- Press Next.
- Untick 'Create a paramterized filter'. We will create our own slicer later.
- Press Next.
Change the name of the Create new query to 'Prompt month'.
It is good practice to label our prompt queries as such so that the following report developers can easily differentiate between data and prompt queries.
In the Value to use we will use the Month Level from the Years hierarchy from the Years dimension.
Figure 5: Prompt Wizard with the Name and Values to use values populated
After completing the prompt wizard your crosstab report should now have the value prompt displayed in the report header as demonstrated in Figure 6.
Figure 6: Sample report in Report Studio with value prompt
We can see the new value prompt within the page header. We will make the report more dynamic by auto submitting the prompt. We will then add the current month as a static member of the prompt.
- On the properties of the Value Prompt change the Auto-Submit property from No to Yes.
Figure 7: Value prompt properties showing the auto-submit set to Yes
- On the Static Choices property of the prompt add the following:
A display value of 'Current Month'.
A use value of '[great_outdoors_sales_en].[Years].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month]'. This is the MUN – member unique name – of our Current Month relative time category.
Figure 8: Value prompt properties: static choices
- Set the Default Selection property of the prompt.
Figure 9: Value prompt properties: default selection
We want our report to default to the Current Month so again we have used the MUN of our Current Month Relative time category used in step 2 above.
We now need to slice our main data query by the chosen month value.
Within Query 1 add a Slicer Member Set to the slicer pane and the expression editor will open. Normally you would use a slicer definition similar to:
However we are not going to use this syntax. This syntax assume we know the level that we will want to filter. We want our report to be able to filter on two different levels. Whilst for normal months we want to filter on the level [great_outdoors_sales_en].[Years].[Years].[Month], for the relative time filter we will want to filter on the level [great_outdoors_sales_en].[Years].[Current Month].[Current Month]. Clearly the conventional filter syntax does not allow this.
Instead we will use a macro. In the Expression editor type:
#prompt( 'ChosenMonth', 'MUN', '[great_outdoors_sales_en].[Years]. [Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month]' ) #
- ChosenMonth is the name of the parameter.
- MUN is the datatype of the prompt.
- [great_outdoors_sales_en].[Years].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month] is the MUN – member unique name – of the default value of the prompt. For our report this is the MUN of the Current Month relative time category.
Figure 10: Slicer member expression editor with the expression
When we first run the report we can see that our prompt has defaulted to 'Current Month'. We can now use the prompt to change to any other month or to the Current Month like in Figure 11:
Figure 11: Cognos Report Studio Report Viewer
Prompting for a Relative Time Member or Ordinary Member and Using Relative Time functions
The previously demonstrated technique works when the report author want to either slice or filter on a month. The technique will not work when the report author then needs to perform additional relative time functions on the chosen month.
For example let us consider the following crosstab report with Revenue as rows and both a chosen month and previous month on the columns.
Here the user wants to choose a month and show the previous month. This is a typical month on month comparison report and is a common requirement. If we repeat the first technique here we will find that it will not work. Why is this? In order to calculate the 'PreviousMonth' the PrevMember function is used:
prevMember( [ChosenMonth] )
Whilst this function will work fine if the user has chosen a prompt answer from the level member [great_outdoors_sales_en].[Years].[Years].[Month] the function will not work if the user has chosen the member [great_outdoors_sales_en].[Years].[Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month]. The relative time member has no previous member so the function will not return the expected output.
Creating this type of report with a relative month prompt is a little more tricky.
The first problem to tackle is how to turn the Current Month member into a member of the [great_outdoors_sales_en].[Years].[Years].[Month] level so that relative time functions will work.
If we examine the time dimension we can see that the Current Month Member has a child of the month. In Great Outdoors Sales (cube) package we can see that the Current Month member has a child of 2007/Jul. Using the dimensional function FirstChild on the Current Month member will return us the 2007/Jul member.
However this is not the same member as the 2007/Jul found in the Months level. Let us compare the two MUNs to show this:
First 2007/Jul from the Current Month hierarchy:
and now the 2007/Jul from the Years hierarchy:
We can also see in the time dimension that the 2007/Jul found underneath Current Month has no siblings so the PrevMember function would not return the desired result.
Handily there is a dimension function that allows us to easily return the corresponding member of a member in a different hierarchy – this is the linkMember function.
linkmember(firstchild([great_outdoors_sales_en].[Years]. [Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month] ) , [great_outdoors_sales_en].[Years].[Years].[Month] )
Linking the PrevMember and LinkMember functions together we can turn the relative Current Month member into a real month and then perform our relative time functions.
From the first example our prompt either returns the Current Month member or a member from the months level. The function we have created above will not work on a member from the month level due to the FirstChild.
These functions will only work on the Current Month member. If we try and perform these same functions on any other value from the prompt- that is values from the Month level – then we will not get the expected results due to the FirstLevel function.
We will use an If-then-else to determine if our user has chosen Current Month. If they have we will invoke our linkMember based function. If they haven't then we will simply use the month our user has chosen. The If statement will look similar to:
if (chosenMonth = 'Current Month') then ( linkmember based function) else (chosenMonth)
This If statement does have an issue though. Both results from an If statement must come from the same hierarchy. At first glance it may appear that this is true for this If statement. It is not. If the user chooses Current Month the link member is a member from the Years hierarchy whilst the chosenMonth is a member from the Current Month hierarchy. We resolve this issue by having the prompt not return a member!
Let us start putting all these pieces together into an example report.
- Create a new crosstab report within Report Studio based on the Great Outdoors Sales (cube) package. We will build our prompt first.
- Delete the Block containing the title text from the Page Header.
- Drag a Value Prompt from the Insertable Objects toolbox into the page header. The prompt wizard for the Value prompt will start. Create a new parameter called 'ChosenMonthEx2'.
Figure 12: Prompt Wizard: Create a new parameter
- Untick 'Create a paramterized filter' and click next.
- Tick 'Create a new query'. Call the query 'Prompt Month Ex2'.
Figure 13: Prompt Wizard: Create new query
Click Finish. We will now refine the prompt query further.
- From the Query Explorer access the Prompt Month Ex2 query. Add a data item to the query data items. For the expression definition use:
caption ([great_outdoors_sales_en].[Years].[Years].[Month] )
Give the data item a name of Month Caption.
- Add another data item to the query data items. For the expression definition use:
roleValue ('_memberUniqueName', [great_outdoors_sales_en].[Years].[Years].[Month] )
Give the data item the name Month MUN.
Return to the report page to complete the definition of the Value prompt. Select the Value prompt.
- In the Use value select 'Month MUN'.
- In the Display value select 'Month Caption'.
- Set the Auto-Submit property to 'Yes'.
- Enter the Static Choices.
Figure 14: Value prompt properties: static choices box
Enter a static choice of 'Current'.
- Enter the Default Selection of 'Current'.
Figure 15: Value prompt properties: default selection box
We will now work in the Query rather than directly on the crosstab. Go into the query that the crosstab is based upon.
- Add the Revenue measure to the query.
- Add a data item to the query. The expression definition is:
linkmember( firstchild( [great_outdoors_sales_en].[Years]. [Current Month].[Current Month]->:[PC].[@MEMBER].[Current Month] ), [great_outdoors_sales_en].[Years].[Years].[Month])
- And the Data Item is to be called 'Current Month as Real Month'. This gives us a data item for the Current Month as a member from the Years hierarchy.
- Add another data item to the query. The data item name is 'ChosenMonth'. The expression definition for the data item is:
if ( ?ChosenMonthEx2?= 'Current' ) then ( [Current Month as Real Month] ) else ( #substitute('Current', '[great_outdoors_sales_en].[Years].[Years].[Month]->: [PC].[@MEMBER].[20040101-20040131]', prompt( 'ChosenMonthEx2','token','Current') ) # )
This appears to be complicated statement and the Else part of the clause is worthy on an explanation. Again we are using the IBM Cognos macro syntax. The ChosenMonth prompt will return a string.
If the string is 'Current' then the substitute function will replace that string with
This is just a member from the Months level of the Year hierarchy. However we know that if the ChosenMonth is 'Current Month' the first part of the If clause is used so this member will never actually be used.
If the ChosenMonthEx2 prompt is not 'Current' then the macro will simply return the prompt – which happens to be the MUN of the chosen month.
This complicated macro ensures that no matter what the user chooses – either Current or a real month – a member from the Month level of the Year hierarchy is returned. We now have an If condition which always returns a member from the same hierarchy level.
- Add another data item to the query. The data item name is PreviousMonth. The expression definition for the data item is:
Return to the Crosstab page.
From the Insertable data items tab:
- Drag the Revenue data item to the rows.
- Drag the ChosenMonth data item to the columns.
- Drag the PreviousMonth data item into the columns alongside the ChosenMonth.
If we run the report now we can see that it has defaulted the prompt to the Current Month. The crosstab on the report is showing us the chosen month 2007/Jul and the Previous Month. If we select another month from the prompt the report still works.