Creating a custom calculation in a Cognos Crosstab

[morgr]

OK, I have been banging my head against a wall for a while now and have come to realize I need some help. I am trying to do what I consider to be a relatively simple calculation in the Report Studio. Basically I want to divide two columns in a crosstab report. My original data looks like this:

Year Element Amount
2007 Revenue 100
2007 Profit 10
2008 Revenue 200
2008 Profit 20
2009 Revenue 300
2009 Profit 35
2010 Revenue 400
2010 Profit 42

I then place this data in a crosstab to generate the following:

Year Revenue Profit % of Revenue
2007 100 10 10%
2008 200 20 10%
2009 300 35 12%
2010 400 42 11%

My problem is I cannot get the calculation (i.e. "% of revenue") to work. I found this exercise to be very easy in a list report, but i could not find a solution in a crosstab. I tried the following formulas:

1. [Profit]/[Revenue]
2. [Element]='Profit'/[Element]='Revenue'
3. tuple([amount], 'Profit') / tuple([amount], 'Revenue')

Any help with this situation would be greatly appreciated.

 

[adamak81]

HI
I want to get default financial quarter year displayed on my prompt page when i run the report

my report look like this

on the prompt page

value prompt 1. financial quarter year period start

value prompt 2. financial quarter year period end

so if i run the report i should get last complete quater year displayed.on prompt 1. as well prompt 2.
ihave seen the issues previously but cant find right answer can any one help plz.....

 

[blom0344]

total((CASE WHEN [Element] = 'Profit'  THEN [Amount] ELSE 0 END))/
total((CASE WHEN [Element] = 'Revenue' THEN [Amount] ELSE 0 END)
[code]

Add this as a query calculation to the right of the crosstab and make sure to add the format % to this dataitem in the crosstab..

Ties Blom