(For more resources related to this topic, see here.)

In this article, we will cover some fundamental techniques that will be used in your day-to-day life as a Report Studio author. In each recipe, we will take a real-life example and see how it can be accomplished. At the end of the article, you will learn several concepts and ideas which you can mix-and-match to build complex reports. Though this article is called Report Authoring Basic Concepts, it is not a beginner’s guide or a manual. It expects the following:

• You are familiar with the Report Studio environment, components, and terminologies
• You know how to add items on the report page and open various explorers and panes
• You can locate the properties window and know how to test run the report

Based on my personal experience, I will recommend this article to new developers with two days to two months of experience.

In the most raw terminology, a report is a bunch of rows and columns. The aim is to extract the right rows and columns from the database and present them to the users. The selection of columns drive what information is shown in the report, and the selection of rows narrow the report to a specific purpose and makes it meaningful. The selection of rows is controlled by filters. Report Studio provides three types of filtering: detail , summary , and slicer. Slicers are used with dimensional models.). In the first recipe of this article, we will cover when and why to use the detail and summary filters.

Once we get the correct set of rows by applying the filters, the next step is to present the rows in the most business-friendly manner. Grouping and ordering plays an important role in this. The second recipe will introduce you to the sorting technique for grouped reports.

With grouped reports, we often need to produce subtotals and totals. There are various types of aggregation possible. For example, average, total, count, and so on. Sometimes, the nature of business demands complex aggregation as well. In the third recipe, you will learn how to introduce aggregation without increasing the length of the query. You will also learn how to achieve different aggregation for subtotals and totals.

The fourth recipe will build upon the filtering concept you have learnt earlier. It will talk about implementing the if-then-elselogic in filters. Then we will see some techniques on data formatting, creating sections in a report, and hiding a column in a crosstab.

Finally, the eighth and last recipe of this article will show you how to use prompt’s Use Value and Display Value properties to achieve better performing queries.

The examples used in all the recipes are based on the GO Data Warehouse (query) package that is supplied with IBM Cognos 10.1.1 installation. These recipe samples can be downloaded from the Packt Publishing website. They use the relational schema from the Sales and Marketing (query) / Sales (query) namespace.

The screenshots used throughout this article are taken from Cognos Version 10.1.1 and 10.2.

Summary filters and detail filters

Business owners need to see the sales quantity of their product lines to plan their strategy. They want to concentrate only on the highest selling product for each product line. They would also like the facility to select only those orders that are shipped in a particular month for this analysis.

In this recipe, we will create a list report with product line, product name, and quantity as columns. We will also create an optional filter on the Shipment Month Key. Also, we will apply correct filtering to bring up only the top selling product per product line.

Getting ready

Create a new list report based on the GO Data Warehouse (query) package. From the Sales (query) namespace, bring up Products / Product line , Products / Product , and Sales fact / Quantity as columns, the way it is shown in the following screenshot:

How to do it…

Here we want to create a list report that shows product line, product name, and quantity, and we want to create an optional filter on Shipment Month. The report should also bring up only the top selling product per product line. In order to achieve this, perform the following steps:

1. We will start by adding the optional filter on Shipment Month. To do that, click anywhere on the list report on the Report page. Then, click on Filters from the toolbar.
2. In the Filters dialog box, add a new detail filter. In the Create Filter screen, select Advanced and then click on OK as shown in the following screenshot:

   

3. By selecting Advanced , we will be able to filter the data based on the fields that are not part of our list table like the Month Key in our example as you will see in the next step.
4. Define the filter as follows:

   [Sales (query)].[Time (ship date)].[Month key (ship date)] = ?ShipMonth?

5. Validate the filter and then click on OK.
6. Set the usage to Optional as shown in the following screenshot:

   

7. Now we will add a filter to bring only the highest sold product per product line. To achieve this, select Product line and Product (press Ctrl and select the columns) and click on the group button from the toolbar. This will create a grouping as shown in the following screenshot:

   

8. Now select the list and click on the filter button again and select Edit Filters . This time go to the Summary Filters tab and add a new filter. In the Create Filter screen, select Advanced and then click on OK.
9. Define the filter as follows:

   [Quantity] = maximum([Quantity] for [Product line]).

10. Set usage to Required and set the scope to Product as shown in the following screenshot:

    

11. Now run the report to test the functionality. You can enter 200401as the Month Key as that has data in the Cognos supplied sample.

How it works…

Report Studio allows you to define two types of filters. Both work at different levels of granularity and hence have different applications.

The detail filter

The detail filter works at the lowest level of granularity in a selected cluster of objects. In our example, this grain is the Sales entries stored in Sales fact . By putting a detail filter on Shipment Month, we are making sure that only those sales entries which fall within the selected month are pulled out.

The summary filter

In order to achieve the highest sold product per product line, we need to consider the aggregated sales quantity for the products.

If we put a detail filter on quantity, it will work at sales entry level. You can try putting a detail filter of [Quantity] = maximum([Quantity]for[Productline])and you will see that it gives incorrect results.

So, we need to put a summary filter here. In order to let the query engine know that we are interested in filtering sales aggregated at product level, we need to set the SCOPE to Product . This makes the query engine calculate [Quantity]at product level and then allows only those products where the value matches maximum([Quantity]for [Product line]).

There’s more…

When you define multiple levels of grouping, you can easily change the scope of summary filters to decide the grain of filtering.

For example, if you need to show only those products whose sales are more than 1000 and only those product lines whose sales are more than 25000, you can quickly put two summary filters for code with the correct Scope setting.

Before/after aggregation

The detail filter can also be set to apply after aggregation (by changing the application property). However, I think this kills the logic of the detail filter. Also, there is no control on the grain at which the filter will apply. Hence, Cognos sets it to before aggregation by default, which is the most natural usage of the detail filter.

See also

• The Implementing if-then-else in filtering recipe

Sorting grouped values

The output of the previous recipe brings the right information back on the screen. It filters the rows correctly and shows the highest selling product per product line for the selected shipment month.

For better representation and to highlight the best-selling product lines, we need to sort the product lines in descending order of quantity.

Getting ready

Open the report created in the previous recipe in Cognos Report Studio for further amendments.

How to do it…

In the report created in the previous recipe, we managed to show data filtered by the shipment month. To improve the reports look and feel, we will sort the output to highlight the best-selling products. To start this, perform the following steps:

1. Open the report in Cognos Report Studio.
2. Select the Quantity column.
3. Click on the Sort button from the toolbar and choose Sort Descending .
4. Run the report to check if sorting is working. You will notice that sorting is not working.
5. Now go back to Report Studio, select Quantity , and click on the Sort button again. This time choose Edit Layout Sorting under the Other Sort Options header.
6. Expand the tree for Product line . Drag Quantity from Detail Sort List to Sort List under Product line as shown in the following screenshot:

   

7. Click on the OK button and test the report. This time the rows are sorted in descending order of Quantity as required.

How it works…

The sort option by default works at the detailed level. This means the non-grouped items are sorted by the specified criteria within their own groups.

Here we want to sort the product lines that are grouped (not the detailed items). In order to sort the groups, we need to define a more advanced sorting using the Edit Layout Sorting options shown in this recipe.

There’s more…

You can also define sorting for the whole list report from the Edit Layout Sorting dialog box. You can use different items and ordering for different groups and details.

You can also choose to sort certain groups by the data items that are not shown in the report. You need to bring only those items from source (model) to the query, and you will be able to pick it in the sorting dialog.

Aggregation and rollup aggregation

Business owners want to see the unit cost of every product. They also want the entries to be grouped by product line and see the highest unit cost for each product line. At the end of the report, they want to see the average unit cost for the whole range.

Getting ready

Create a simple list report with Products / Product line , Products / Product , and Sales fact / Unit cost as columns.

How to do it…

In this recipe, we want to examine how to aggregate the data and what is meant by rollup aggregation. Using the new report that you have created, this is how we are going to start this recipe:

1. We will start by examining the Unit cost column. Click on this column and check the Aggregate Function property.
2. Set this property to Average .
3. Add grouping for Product line and Product by selecting those columns and then clicking on the GROUP button from the toolbar.
4. Click on the Unit cost column and then click on the Summarize button from the toolbar. Select the Total option from the list.
5. Now, again click on the Summarize button and choose the Average option as shown in the following screenshot:

   

6. The previous step will create footers as shown in the following screenshot:

   

7. Now delete the line with the <Average (Unit cost)> measure from Product line . Similarly, delete the line with the <Unit cost> measure from Summary . The report should look like the following screenshot:

   

8. Click on the Unit cost column and change its Rollup Aggregate Function property to Maximum .
9. Run the report to test it.

How it works…

In this recipe, we have seen two properties of the data items related to aggregation of the values.

The aggregation property

We first examined the aggregation property of unit cost and ensured that it was set to average. Remember that the unit cost here comes from the sales table. The grain of this table is sales entries or orders. This means there will be many entries for each product and their unit cost will repeat.

We want to show only one entry for each product and the unit cost needs to be rolled up correctly. The aggregation property determines what value is shown for unit cost when calculated at product level. If it is set to Total , it will wrongly add up the unit costs for each sales entry. Hence, we are setting it to Average . It can be set to Minimum or Maximum depending on business requirements.

The rollup aggregation property

In order to show the maximum unit cost for product type, we create an aggregate type of footer in step 4 and set the Rollup Aggregation to Maximum in step 8.

Here we could have directly selected Maximum from the Summarize drop-down toolbox. But that creates a new data item called Maximum (Unit Cost) . Instead, we ask Cognos to aggregate the number in the footer and drive the type by rollup aggregation property. This will reduce one data item in query subject and native SQL.

Multiple aggregation

We also need to show the overall average at the bottom. For this we have to create a new data item. Hence, we select unit cost and create an Average type of aggregation in step 5. This calculates the Average (Unit Cost) and places it on the product line and in the overall footer.

We then deleted the aggregations that are not required in step 7.

There’s more…

The rollup aggregation of any item is important only when you create the aggregation of Aggregate type. When it is set to automatic, Cognos will decide the function based on the data type, which is not preferred.

It is good practice to always set the aggregation and rollup aggregation to a meaningful function rather than leaving them as automatic.