How to use Cognos Report Studio to change the default calculation Total ()

Question

I have a crosstab report that calculates bounce rates for my products; it has two measures (PASSCOUNT, FAILCOUNT) and the calculation of FAILRATE (FAILCOUNT / PASSCOUNT + FAILCOUNT)

The layout of the report is as follows:

OEM MODEL TESTYEAR TESTMONTH PASSCOUNT FAILCOUNT FAILRATE 

When I select the "Total" icon, it logically adds each of the columns like this:

 2012 OCT 7547 697 0.08 NOV 9570 373 0.04 DEC 1879 107 0.05 ------------------------ Total 18996 1177 0.17 

My user, however, wants TOTAL FAILRATE to be

 TOTAL FAILCOUNT/(TOTAL PASSCOUNT+TOTAL FAILCOUNT) 

which translates to

 1177 / (18996+1177) = 0.058 

How can I create this total in the report? I am reading about creating a query calculation, but I do not understand that this is the right approach.

Cognos Report Studio 8.4 IBM DB2 UDB

Answer 1

You really can. There are a couple of methods.

In your report request, you have separate request elements that make up your desired calculation that works fine (PASSCOUNT, FAILCOUNT, etc.). You can create a new data item / expression in the same list of query elements and edit the definition. On the left side, you can use columns from the original db data source, but there is another panel that you can choose to actually reuse the other query / calculation elements that you defined in the same query (PASSCOUNT FAILCOUNT FAILRATE). Cognos knows that when you create an expression using other columns in the same query, you first enable these query elements to allow your calculation to depend on them. You want to make sure that your computed / derived column is listed after the other dependent elements of the query (in fact, this may not matter, but it makes sense when looking at it). In addition, I believe that you will need to set the bounce rate query element / expression to “Calculated” as its aggregate method.

The summary line may / may not be a bit more complicated. Without a reporting studio right in front of me, it can be smart enough when you add a totals section to a list report and use your new bounce rate expression ... to extend the correct calculation or you may need to use another method for the resume, which is report expression.

You create a report expression not in the request, but on the report gui page (in the toolbar along side tables, singltons, etc.). It has an expression builder just like in a query, but you will notice that the set of functions is different from what it executes after the query is executed, and as the results return, such simple things as you are fine, this it's just math, but you'll notice that other database functions are not available in report expressions simply because they occur at the output of html, and not during query execution against the database.

Hope this helps. As a result, you create a calculated column based on generalized calculations of other columns in the same set of queries / results. This would be theoretically the same as this SQL statement, which would not work because SQL does not allow this directly, but I hope it helps to explain what cognos does.

Select 1 + 2 as FAILCOUNT, 2 + 3 AS PASSCOUNT, (FAILCOUNT / PASSCOUNT + FAILCOUNT) AS A MALFUNCTION FROM PURITY - Cognos can use the results of other aggregate calculated columns in one query, and if you use sq sql, you can see how it organizes the SQL for this.

Thanks,

Tim

Answer 2

It is important to select the correct amount in the crosstab.
When you add a common line, make sure you select Automatic Resume:

This parameter ensures that aggregation is determined individually by each request data element.
If it still does not give the expected result, then in the query explorer select the query that is used in the crosstab, and in the FAILRATE data element, select the calculated option (in the properties panel):

Answer 3

The crosstab reports what happens when the column data is displayed first n and then row level data, so this is the cause of your problems. To enable this click on the heading of the column column of the “FAILRATE” list and assign it the solution Order = 1 if the order of the solution is NULL. The trick is that Solve order will control the calculation of FAILRATE after everyone else is done.