IBM Support

Create a crosstab report that calculates the difference between two rows



In a crosstab report where a data item is shown under several columns, how do you show the difference between the value in the columns?

Resolving The Problem

This is done using the running-difference function, with the appropriate parameters. You do this by adding a data-item to the query that uses the running-difference function. To ensure that it calculates the right values, you need to include the pivot-columns in the "For" clause. So, if you build a crosstab that shows "Year" accross the top, and "Country" and "City" down the side, with "Quantity" as the displayed measure, you would set our data item to use the following expression:

running-difference([Model].[QS].[Quantity] for [Country],[City])

This tells Cognos 8 to calculate the difference between the columns, but not to carry that difference over to the next row. It's important that the order of the data items in the "for" clause be in the same order as the groupings of the report. For more detail on the FOR clause, review "Using the AT and FOR Options with Relational Summary Functions" document (see link below)

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Cognos Connection","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2;10.1.1;10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018