I was working on a few Cognos Report Studio reports that were using different cubes. Part of the requirement was that users be able to drill from the one report to the other. The reports also included context filters (slicers) from value prompts. These value prompts use the cube MUNs (member unique name) and when drilling from one report to the other, these prompt selections should be passed along to the target report.
Drilling from one dimensional report that is using member unique names as a prompt to another report that is using a different cube can be difficult to set up, since the member unique name for an entity (say item ‘Tents’) in cube 1 will be different than the same item in cube 2, e.g.
Cube1: [Cube1].[Products].[Products].[Item]->:[PC].[@MEMBER].[Tents]
Cube2: [Cube2].[Products].[Products].[Item]->:[PC].[@MEMBER].[Tents]
We need a way to translate the MUN from cube 1 to cube 2. The solution is to use the substitute macro. The problem with the substitute macro is that it only substitutes the first occurrence. So the problem becomes…what to do when we have multiple products selected?
Note that for this example I’m defining two data items in the query, a default value item, and the filter/prompt item that uses the default value so that the prompt can be optional.
Source report, using Cube1
DefaultProduct: [Cube1].[Products].[Products].[Products]->:[PC].[@MEMBER].[All Products]
ProductPrompt: #promptMany(‘SelectedProducts’,’memberuniquename’,'[DefaultProduct]’,’set(‘,”,’)’)#
Target report, using Cube2
Define the default member:
DefaultProduct: [Cube2].[Products].[Products].[Products]->:[PC].[@MEMBER].[All Products]
Oftentimes the promptMany macro is defined using the macro’s ability to prepend the “set(” and appending the closing “)” values to the selected results so that the macro returns a set() of values ready to be used, like this:
#promptMany(‘SelectedProducts’,’memberuniquename’,'[DefaultProduct]’,’set(‘,”,’)’)#
The idea here is to split the array returned by the promptMany macro into components, then perform the substitute on each component, then join them back together.
For this to work we will remove the ‘set(‘ and ‘)’ prepend/append text values from the macro and put it outside, around the macro definition, like this:
set(#promptmany(‘SelectedProducts’,’memberuniquename’,'[DefaultProduct]’)#)
Next we add the logic to split, substitute and join the prompt results:
ProductPrompt:
set(
#join(‘;’,
substitute(‘Cube1′,’Cube2’,
split(‘;’,
promptMany(‘SelectedProducts’,’memberuniquename’,'[DefaultProduct]’)
)
)
)#
)
The same approach can be used to use a single value prompt to filter data for two different displays using different cubes on the same report, e.g. a dashboard type situation.
If you are interested in gaining more insight into Report Studio, check out our on-demand webinar Tips for Intermediate Cognos Report Studio Authors.
Thanks to our own Johan Grobler, Senior BI Consultant with Senturus, for this tip.
