Create a "Difference" Row in Crosstab

[akhattri]

Hello Everyone,
I am creating a crosstab report in which the main requirements are:
1) Extract Summary of total monthly admits for Period '200810'
2) Extract Summary of total monthly admits for Period '200710'
3) Calculate the difference during each month for the above said periods.

(Both the above values are getting pulled from a single query item).

I was able to create the Report for getting the total counts for each month for both the periods. However I have been unable to add the difference row at the bottom which gives the difference of the above 2 rows.

I tried to create a Union Query, with the first query having filter for period '200810' and second query having filter for '200710'. I dragged in the query items available, then I tried to create a report. However the problem is the report gives back the data only for the first period. My logic behind this was that I will create a Union Query and then Calculate the difference between the 2 columns for 2 periods.

One other thing that I was unable to figure out was that when creating the Union, in the Insertable Objects only Query Items with common name were shown and not both the Queries. This sort of hinders as to which Query Item I can pull from which query.

Just to give you a visual of what I am trying to achieve:

                Jan        Feb          March       April     .....................
200810        10         15            11            6
200710         3          17             8            4
Difference     7           -2            3             2

Any thought/ideas/inputs from you folks is appreciated.

[almeids]

It sounds like you are mixing approaches - if you are using a crosstab, your measure is in a single data item for all periods and you can't "subtract it" per se.  To add rows or columns other than summaries you need additional records in your result set.
You are sort of on the right track with the union, but what you want to do is union your 2007-8 query with another query which returns the difference.  You may be misunderstanding how a union works - you won't see data items "from both queries", you'll see one set of data items which represent the result set combining records from both queries; both queries must be identically "shaped".
So, assuming your existing query with the 2007-8 data contains the data items YEAR, MONTH and VALUE, you want to add another query with the exact same number, type, and sequence of data items.  This query will also retrieve the 2007-8 data, i.e. its filters will be identical to the first query.  For YEAR, which you are displaying as rows, you want a static string like 'Difference'.  MONTH comes from the database, unchanged.  For VALUE, you need a calculation something like IF (year=2007) THEN (value*-1) ELSE (value)...though of course you should use something dynamic like a parameter value for the conditional test rather than hardcoding the year.  When Cognos rolls this up, it will effectively yield the difference.  You may also need a sequencing column you can sort on so "Difference" becomes your last row.  Also, if YEAR is numeric, you'll need to cast it to character so the data types of your first column in the queries are compatible.

[Suraj]

I answered in Cognos forum as well...
>>>
This is pretty much straight forward if you have just one measure and two years.
See the attached screenshot for reference:
create a data item with this expression: running-difference(Quantity for Order month)
and put it below the years in row.

[almeids]

Suraj, that is brilliant.  Bravo.
I'm afraid once again my Cognos 8 ignorance is showing, as I don't think you can do that in ReportNet.

[akhattri]

Thanks for replying Suraj and Almeids !! You guys Rock 8)

I am trying to implement the solution suggested by Suraj, will let you know if it works in my specific scenario!!

Thnx!

[Suraj]

Suraj, that is brilliant.  Bravo.
I'm afraid once again my Cognos 8 ignorance is showing, as I don't think you can do that in ReportNet.

Believe me, you are good. :)

AKhattri,
You may have to change the sorting of year so that it treats the later as the one to subtract from or multiply by -1 if you get the reverse substraction.

[akhattri]

Hi,
The Running Difference is not giving the correct answer.

Interestingly, the actual values in crosstab are coming out perfectly fine as anticipated, but it is just the difference which is giving headaches  :(

Actually, for the "Measure" field in my crosstab, I created a new Data Item which performs a Count(Person_ID) and then tries to generate a cross tab, depending on this value (Since that was the only way I could have the count of the number of admits, as measure has to be numeric). My Column Data Item is also a generated query item  which performs the extract of the month from another Date field.
Also the Cross tab has  2 levels of grouping before the croosstab is generated.(i.e grouping on level, population and then a cross tab), though removing grouping doesn't make a difference....

The 'Difference' seems to be totally disjointed from the 2 rows from which it s calculating the difference!

Is it because of this 'grouping' or ' new 'measure' that I am getting the wrong answer?

[rockytopmark]

Remove the aggregation functions from your data item and let the crosstab perform the aggregation.  This is the best practice.

[akhattri]

The Cross tab I have created has multiple level of rows in it. I figured out that this apparently is causing issues. It seems the formula suggested by Suraj needs some modification for cases when multiple level of rows are involved. I am attaching the report output and the formula for running-difference.

Any ideas how to take care of it when multiple level of rows are involved?

[almeids]

Worst case you can always take my brute force approach...not elegant but guaranteed to work since you do the math!  ;D