Cognos Hub: Master-Detail Relationship in Cognos Report Studio
Create a master detail relationship to deliver information that would otherwise require two or more reports. For example, you can combine a list with a chart. Oct 13, Today, I wanted to offer technical tips pertaining to master-detail relationships in Cognos. Sales reports, employee performance, regional stats. Master-Detail Relationship in Cognos Report Studio. 1. Create a List report with just one column Product Line. 2. Unlock the Lock Symbol --> Insert another List.
Use parameters to filter values at a lower level in the detail query. Click the Queries iconand click the detail query.
Click the Toolbox icon and drag the Filter object to the Detail Filters box. In the Expression Definition box, create the parameter.
Click the Pages iconand click the report page. Click the Show properties icon, and In the Properties pane, click the Select ancestor icon and click Page.
Click the data container containing the details. In the Properties pane, click the Master detail relationships property. In the Master box, click the data item that will provide the primary information. To link the master query to a data item, in the Detail box, click the data item that will provide the detailed information.
Cognos Master Detail Relationship In Report Studio - MindMajix
To link the master query to a parameter, in the Parameters box, click the parameter that will provide the detailed information. To delete a link, select the link and press the Delete key. If your detail query object is a chart, you can disconnect a chart title from the master query.Cognos Tutorial - Multiways of creating Master Detail Relationship in Report Studio
Click the chart title, and from the Properties pane, click Master detail relationships. Clear the Use the master detail relationships from the chart check box. If the master detail relationship includes a chart as the detail and you are using automatic axis ranges, you can set the same axis range for all the detail charts: In the Properties pane, under General, double-click the Axis range property. Under Maximum and Minimum, click Automatic.
The master detail report option is only available with automatic axis ranges. Under Master detail reports, select the Same range for all instances of chart check box.
To avoid seeing the same data item twice in the report, click the data item in the data container driven by the detail query. In the report object toolbar, click the More icon and click Cut. This removes the item from the report display but keeps it in the query. If we were using DQM, and if the master-detail optimization governor was enabled, Cognos might be able to drop the number of queries down to 3.
One for the list of regions, one for the crosstab, and one for the graph. The queries would be cached and Cognos would intelligently split the data there.
IBM How to create a Master Detail Relationship - United States
In addition to sectioning data, we can pass values to parameters. This allows us to solve complex requirements. Dynamic Filters By populating a table with filter expressions, we can generate different pages with the same crosstab but different filters.
This happens more with operational systems than data warehouses, but there are some cases where it is useful.
How to Create Master Detail Relationship in Cognos Report Studio
In this example, we are looking at specific managers working against specific promotion campaigns. Each details would have different filters applied. This does not need to be limited to specific campaigns. This is not limited to filters, columns can be applied as well.
Create a Master Detail Relationship
By using the token prompt inside a field, and populating that with a field reference, it is possible to dynamically modify a list. As a practical example, consider a scenario presented by one of my past clients. A series of reports needed to be bursted to a number of managers.
Each manager had a unique set of filters to be applied. Some of them needed to see data at a high level of granularity, and others needed to see detailed data. The standard method, by joining the burst table to the necessary fact tables, would not work here due to the vast range of requirements. One way to solve this would be to have each manager create a report view for each report he needed.