Cognos Report Studio: Filters

 Understanding Detail and Summary Filters

There are two main types of filters you can add to your report in Report Studio – Detail filters and Summary filters. Detail filters will limit rows within the report. Summary Filters will limit groups that have been defined within the report.

Detail Filters

Detail filters will limit your data by each row of data returned by the database. If you add a detail filter using the filter icon on the toolbar while working on the report, Cognos will add a filter to the detail object in the query section.

There are two main properties of a detail filter - Application and Usage.

Usage will determine whether this filter is required (the user has no choice but to use this filter), optional (the user can decide to use or ignore this filter), or disabled (the filter is in the report specification but not being used). Disabled will primarily be used during the testing period of a report. You can turn the filter on and off without actually deleting it.

Aggregation determines when the filter is going to be applied. You can apply the filter before or after aggregation. Before aggregation will limit the data at the most detailed level of data available in the database.

For example, look at the two tables above. The table on the left is not summarized. It contains multiple records for each product type. The table on the right is summarized so only one row per Product type will be shown. Applying a filter before aggregation will apply the limit to the table on the left. Applying it after aggregation will apply the filter to the table on the right. 

If we were to apply the limit quantity > 5,000,000 before aggregation, we are only going to get the individual records that meet the criteria. The second and third records for Knives Product type do not have values of more than 5,000,000 so they do not get kept in the results set. If we were to apply the same filter after aggregation, we will get all records for that account, because Cognos is summarizing the data first.

Summary Filters

Summary filters are going to limit your data based on fields that you have grouped in your report. If you add a Summary filter using the filter icon on the toolbar while working on the report, Cognos will add a filter to the cube object in the query section.

The Summary Filter does not have the Before/After aggregation property that the detail filter had. Instead, it has a property for Level. Level allows you to choose at which level of summary you would like to apply the filter.

In our example, a list report has been grouped by Product Line and Product Type

If we add a Summary Filter, we have two different levels to which we can apply this filter. We can either limit on any Product Line that has a total Quantity > 5,000,000 or any Product Type that has a total Quantity > 5,000,000. 

Cognos Report Studio: Join Query

You Create a Join relationship to join two queries.

In general, join relationships are created in Framework manager. However, you can create join relationship in report studio if what you are trying to do is not modeled in Framework manager.

A Join is a way to combine fields from two tables by using values that are common to each.

After creating a join query you need create Join relationship and Cardinality.

This is one to many relationship.

This is considered to be an inner join that returns rows when there is at least one matching record found the both tables. So this will return Product line Rev with associated budges because the joined query will look for the records where the same Product line appears in the Budget query and Revenue query.

Create a Join Relationship

1.      Go to Query Explorer, from toolbox drag query object to work area. Rename the query to qProductLineRevene. Double click on the query and drag Product Line Code, Product Line and Revenue from Sales (query) folder to Data Items pan.

2.      Go back to query explorer, from toolbox drag another query to work area and rename the query to qProductLineObject. Double click on the query and drag Product Line query items from Sales Target (Query) Folder to Data Items pane.

3.      Go back to query explorer and drag Join query to the work area.

4.      Double click on the join /click on Join and from properties pan click on Join relationship.

5.      Click New Link.

6.      To create the link, click a data item in the left query, and then click a data item in the right query. In our example that is Product line code in Left query and Product Line code in right query.

7.      By default you will have one to many cardinality. According to your requirement you can change the cardinality. You can easily understand cardinality affect in the relationship impact description.

8.      Go back to query explorer and double click on the join query and drag the data items from the source tab to the data items pane. 

Showing Multiple data containers on the same page in HTML and PDF

If you multiple data containers like list, crosstab and chart in one page, you can control how the report is rendered in HTML and PDF by setting the rows per page property for each container. 

For HTML output, the report property option "Page break by data container for interactive HTML" controls whether the default number of rows is rendered for each data container on each page. You must set the "Page break by data container for interactive HTML" option to Yes. The default value is No.

If the rows per page property not set for any of the data containers, 20 rows per page are rendered in HTML and each page is filled in PDF. The first data container is rendered until there is no data, followed by the next data container. The number of rows that appear in a PDF page depends on the Font size of the report. 

If the Rows Per Page property is set for each data container, the specified numbers of rows are rendered in HTML and PDF on each page until there is no more data.

How to set the report property option:

1. In report studio, go to file menu and click on Report Properties

2. Change the 'Page break by data container for interactive HTML' drop down to 'YES'

How to improve the performance of Master Detail Relationship

How to improve the performance of Master Detail Relationship

Creating a master details relation ship between two data containers is similar to creating join between two tables. when you create Master detail relation each row from parent query is compared with each record in child query. Hence the performance will be degraded.

How to improve the performance without loosing the functionality:

1. You need to create a filter in the child query. For example if you are linking Product line in Master and child queries. You need to create a ?ProductLine? Prompt in Child query

2. Now change the link between Product Line to Product Line parameter in child query like shown below. 

 With this approach we are filtering the child query without comparing record to record. Hence the performance will improve drastically. 

Multiple Crosstabs Columns alignment

Multiple Crosstabs Columns alignment

When we have multiple Crosstabs in a single page. If you have very less number of columns in your reports. Most of the time there will not be any issue. But if you any high number of columns then we will get into column alignment issue. Cognos will search all the cells in each column and decide the column width based on its highest cell value length. If you have a two different crosstabs, with first Crosstab having 1M and the second Crosstab with 1K data then you will have the issue. To fix this issue we have different approaches.

Method 1:

Fix all columns with and make the Crosstab Table proper to fixed with. With this option you can improve the performance as well.

1. Select each column and set the width size.
2. Select the each Crosstab and in Table Properties Check fixed size.

Method 2:

1. Unlock the report and Drag blocks into Crosstab nodes and Fact cells.
2. Set the Width size for the blocks.
3. Now drag the cell contents to the blocks.
4. In this approach you will get formatting issue for the cells. As we are moving the cell contents to the blocks. Columns formatting’s will not be applied in the Block. Hence you need to update them manually.

In a very rare cases in both these approaches you will end up with the same issue. The problem for this is may be your column labels length is very high. In these cases you need to split the columns label. Select column label and from the Properties under Font & Text property Change the word break property to break all.

Hide Crosstab Columns based on prompt selection

How to hide Crosstab Columns based on prompt selection without Using Conditional Styles and Variable:

1. Create a value prompt with all the optional columns (In this example I am using prompt Name as pOptClms). 

2. Change the Optional columns Data Items/Query Items expressions as for Quantity Column:

IF ( 'Quantity' in ?pOptClms? ) THEN

    ( [Sales (query)].[Sales].[Quantity] )

ELSE 

    ( Cast(Null as Integer) )

Note: If you want hide any dimension then you need to change the else part as (Cast(Null as Char))

By change the expression with If Statement that will make the prompt as Mandatory. If you want to have optional prompt then you need to Use macros. Replace the If condition with the below macro:

‘Quantity’ in (#promptmany(‘pOptClms’, ‘token’,’’’XYZ’’’)#)

3. Similarly you need to change the expressions for all the optional columns.

4. Now from the Data menu click on Suppress and then click on Suppression Options.

5. From the Suppression options: Click on Columns only and then Uncheck Zero values, Divide by zero and Overflow values.

Run the report and see the result.

This approach can be very useful when you have to align multiple Crosstabs.

Download Sample