Techniques for Bursting Cognos Reports:
For Cognos, bursting and multi-dimensional data sources (OLAP sources or multi-dimensional models built with Framework Manger) need to leverage some additional techniques. This includes master/detail reports that involve multi-dimensional data sources, whether purely multi-dimensional or a combination of multi-dimesional and relational sources.
One key issue is linking the burst key data item a [master] multi-dimensional query to a burst recipients table [detail] which can only be defined in a relational query subject.
For details, see Creating Burst Reports using Dimensional Data Sources and Link Members for Two Data Sources in the Report Studio Users Guide.
A master/detail link is essentially a report join (vs. query join) where the master query and detailed query are matched on a data item which contains the same value.
The problem is matching multi-dimensional members to relational “values”. Running a multi-dimensional query results in Member Unique Names (MUNs), not their captions. For example:
[Sales].[Retailers].[Retailers].[Retailer country]
When executed in a query, this expression produces a Country for each member:
MUN: [Sales].[Retailers].[Retailers].[Retailer country]->[Retailers].[710].[90001]
Caption: United States
If a master/detail link link is built by dragging a multi-dimensional data item and a relational data item into the link expression, the master/detail engine is comparing a MUN with a relational value, which will fail:
Retailer Country (multi-dimensional level) = Retailer Country (relational column)
Results in the following comparison:
[Sales].[Retailers].[Retailers].[Retailer country]->[Retailers].[710].[90001] = United States
If the master and detail data sources are different multi-dimensional sources which have different MUNs for the same caption (e.g. United States) then the link will also fail.
The solution is to use the caption vs. the MUN for master/detail links. This is achieved with a simple Cognos query function – caption().
caption(<Multi-dimensional expression>)
Example: caption([Retailer Country])
Assume this data item is named “Retailer Country Caption”.
Now the master detail expression is:
Retailer Country Caption = Retailer Country
results in:
United States = United States
In summary:
- When mixing multi-dimensional and relational sources in a master/detail relationship, including linking a multi-dimensional source with a relational burst table, use the caption() function for the multi-dimensional expression.
- When queries from two different multi-dimensional data sources are used in a master/detail report, then both must have a caption() data item which generates the same caption.
- When queries from two different relational sources are used in a master detail, it may be required to manipulate the values of the relational columns used in the master/detail link to ensure that their values will match.