Cognos master detail relationship between queries based

Working with Relational Queries

cognos master detail relationship between queries based

Jul 1, "I want to create a master detail relationship in a report as I have to add two happens is the detail query is 'filtered' based on the value in the. Mar 15, The 2 containers can be merged using master details relationship; The 2 containers should have a common query item to merge the data; Add. Oct 13, Technical tips pertaining to master-detail relationships in Cognos. last blog post, I discussed how to correctly use determinants and stitch queries in Cognos. The sample model that I'm using is based on the CQM engine.

Sales reports, employee performance, regional stats are all reports that use, in some form or another, master-detail relationships. At the most basic level, this functionality allows us to split data into multiple sections or to embed other objects in lists. A few practical examples might be sectioning a crosstab: Or embedding a micro chart in the rows: And running, we get the following report: If we were to trace the run, we would see several separate select statements.

First, it would run a query to get a list of all regions. Second, it would run separate queries to get each crosstab and graph. If we look at the trace, we see exactly that: 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.

cognos master detail relationship between queries based

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.

Cognos Master Detail Relationship

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.

Create a Master Detail Relationship

This happens more with operational systems than data warehouses, but there are some cases where it is useful. In this example, we are looking at specific managers working against specific promotion campaigns. Each details would have different filters applied.

Associate a report page with the master query and use a data container, such as a list or crosstab, for the detail query.

cognos master detail relationship between queries based

You cannot display parent data in the child frame or child data in the parent frame. Also, you cannot perform calculations across master detail queries.

cognos master detail relationship between queries based

You can use a master detail relationship to show data from separate data sources in a single report. However, the data sources must be contained in the same package. With the dynamic query mode, master detail reports with a crosstab report object are optimized to use a single query whenever possible, rather than a separate query for each report object instance.

cognos master detail relationship between queries based

Before you begin To create a master detail relationship using queries that reference two different dimensional data sources, you must first link the members by aliasing the levels that contain the members. Procedure To use a parent frame for the master query and a nested frame for the detail query: Add a second data container to the object you inserted.

You can insert a list, crosstab, chart, repeater table, or repeater into a list. You can add a list to a repeater table or repeater. Add data items to both data containers. To associate a report page with the master query and use a data container for the detail query: Click anywhere in the report page. Click the Show properties iconand In the Properties pane, click the Select ancestor icon and click Page.

Create a Master Detail Relationship

Set the Query property. Click the Toolbox icon, drag a data container to the report. To link a data item in the master query to a parameter in the detail query instead of to another data item, create the parameter in the detail query.

cognos master detail relationship between queries based

Use parameters to filter values at a lower level in the detail query. Click the Queries iconand click the detail query.

Working with Relational Queries

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.