4

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

The report layout looks thusly:

                   OEM
                   MODEL
TESTYEAR TESTMONTH PASSCOUNT FAILCOUNT FAILRATE

When I select the Total icon, it logically adds up each of the columns like so

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 custom total in the report? I am reading about creating a Query calculation but I am not clear this is the right approach.

Cognos Report Studio 8.4 IBM DB2 UDB

Chris
  • 1,667
  • 6
  • 34
  • 52

3 Answers3

3

It's important to pick the right total in Crosstab.
When you add the total row, make sure you pick Automatic Summary:
Cognos Automatic Summary
This option will make sure the aggregation is determined individually by each query data item.
If it still does not give you expected result, then on the query explorer, pick the query that is being used on the crosstab, and on the FAILRATE data item, pick the calculated option (on the properties panel):
Cognos Aggregate function property

Ran Avnon
  • 778
  • 4
  • 7
  • 2
    I did indeed pick "calculated". The solution was in the solve order option: I picked "2" (greater than 0) and it worked. – Chris Jan 07 '13 at 21:10
  • I think I missed your point when I first responded. It makes sense and I am beginning to understand that, when crosstabs are in use, calculations (and their aggregation types) drive each result accordingly. This does not seem to be the case for list reports though. – Chris Jan 07 '13 at 22:47
3

You can indeed. There a couple methods.

In your report query you have the individual query items that comprise your desired calculation working fine(PASSCOUNT, FAILCOUNT, etc...). You can create a new data item/expression in that same query item list and edit the definition. In the left hand side you can choose to use columns from your original db data source but there is another pane you can select to actually re-use other query items/calculations you have definied in that same query (PASSCOUNT FAILCOUNT FAILRATE). Cognos knows that when you create an expression using other columns in the same query to resolve those query items first in order to resolve your calculation that is dependent upon them. You want to make sure your calculated/derived column is listed after the other dependent query items(Actually this may not matter but it makes sense when looking at it). Also I believe you will have to set the the failure rate query item/expression to "Calculated" as its aggregate method.

The summary line might/might not be a bit trickier. Not having report studio right in front of me it might be smart enough when you add the totals section to the list report and use your new failure rate expression... to extend the correct calculation or you may have to use another method to do the summary which is a report expression.

You create a report expression not in the query but on the gui page of the report(its in the toolbox along side tables, singltons etc...). It has an expression builder just like in the query but you will notice the function set is different because it is being done after the query runs and as the results come back so simple things like you are doing is fine which is just math but you will notice other database functions are not available on report expressions simply because they are occuring in the html output and not during the query run against the database.

Hope this helps. In summary you are creating a calculated column based on the summarized calculations of other columns in the same query/result set. This would be in theory the same as this SQL statement which will not work because SQL does not allow this directly but hope it helps explain what cognos is doing.

Select 1+2 As FAILCOUNT, 2+3 AS PASSCOUNT, (FAILCOUNT/PASSCOUNT+FAILCOUNT) AS FAILRATE From SOMETABLE -- Cognos is able to use the results other aggregate calculated columns in the same query and if you gen sql you can see how it arranges the SQL to do this.

Thanks,

Tim

0

In the crosstab reports what happens is the columnar data is rendered first n then the row level data, so this is the cause of your problems ..To resolve this click on the List Column Title 'FAILRATE' & assign it a Solve Order = 1, as long as the solve order of total is NULL..The trick is that the Solve order would control the calcuation of FAILRATE after all others are done.

Saransh Khasria
  • 1