If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Calculating (Dividing) 2 Totals Which Exist In the Summary Row

Started by jdarsenault, 25 Jan 2008 02:19:07 PM

Previous topic - Next topic

jdarsenault

With Cognos 8.1, I have a "Billable Percentage" list report which looks & behaves like a crosstab report because Cognos says that what I'm trying to accomplish does not work in 8.1 but is fixed in 8.2. I'm not at or going to 8.2 any time soon.

The Plot...
I have computed columns for 'charge type'(s) which contains a person(s) charge hrs. per charge type.
I use computed columns to display the charge hrs horizontally (crosstab) rather than vertically.
Because a person can have hrs. charged to many charge types, I group span on the person's name so that all charge hrs for all charge types per person apprear in the same row horizontally(crosstab) (i.e.John Doe = line 1, col 1 = charge type 1 hrs, line 1, col 2 = charge type 2 hrs) rather than vertical & staggered. (i.e.John Doe = line 1, col 1 = charge type 1 hrs, John Doe = line 2, col 2 = charge type 2 hrs). You get what I mean.
I'm able to total these hours in the summary row just fine.

More Plot...
Let's say that I'm reporting on 2 weeks worth of work...that would equal 80 hrs of allowable time. If person 1 charges 80 hrs to Charge type 1, that would be a true 100%. If person 2 charges 65 hrs to charge type 1 & 15 hrs to charge type 2, that would be a true 81.25% & a true 18.75% respectively.
Being 2 people charge to charge type 1, there is a potential of 160 hrs to be charged. 1 person charged to charge type 2, so there is a potential of 80 hrs to be charged. This translates to a total of 140 hrs /160 hrs. = 90.6% for Charge Type 1 & 15hrs/80 hrs = 18.75%  for charge type 2.

The Problem...
I'm stuck on dividing the total potential Charge Type hrs. by the true total charge type hours.
Remember I have charge hrs. per calculated charge type per person group spanned, to keep the data looking flat (crosstabbed).
In the summary row, when I try true total charge type hours divided by potential Charge Type hrs (140 /160 ) I'm getting a sum total of true % for both people (1.1875) & not the intended calculation of "total charge type hrs/potential charge type hrs."

In Crystal I would simply create a subreport to get this number & plug it into the main report. How do I accomplish this with C8?

Anyone's advice & guidance would be greatly appreciated.

Thanks

JD

Suraj

First of all play with Aggregate and rollup Aggregate properties to see if you get correct numbers.
Alternatively, you can unlock the report and remove that total from the summary cell and then insert your own calculation from a data item.
That should allow you to calculate what ratio you wanted regardless of what report summarizes.

almeids

If you do wind up doing your own calculations and haven't used it before, look into the FOR clause of the aggregate functions.

porkins

Quote from: Suraj Neupane on 25 Jan 2008 04:20:13 PM
First of all play with Aggregate and rollup Aggregate properties to see if you get correct numbers.
Alternatively, you can unlock the report and remove that total from the summary cell and then insert your own calculation from a data item.
That should allow you to calculate what ratio you wanted regardless of what report summarizes.

It doesn't..