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

crosstab issue

Started by Nandini.t, 12 Apr 2012 04:57:15 AM

Previous topic - Next topic

Nandini.t

Dear all :)

I have a crosstab with following below rows and columns.

Columns:                           Description(calculation)

Number of claims-----         Count of claims
Total Incurred---------         [paid amnt] + [reserve amnt] for [number of claims]


rows:                                 Description(calculation)
[ 0 ; 1 500 ]-------------      Limitation to claims with a total incurred <= 1500€
[ 1 500 ; 3 000 ]-------      Limitation to claims with a total incurred < 1500€ and >=3000
[ 3000 ; 7500 ]---------      Limitation to claims with a total incurred < 3000€ and >=7500
[ 7 500 ; 15 000 ]------     Limitation to claims with a total incurred < 7500€ and >=15000
[ 15 000 ; 75 000 ]----     Limitation to claims with a total incurred < 15000€ and >=75000
[ 75 000 ; 150 000 ]---    Limitation to claims with a total incurred < 75000€ and >=150000
Over 150 000-----------     Limitation to claims with a total incurred > 150000€
'TOTAL--------------------    Sum of all lines


for row level i used below case statement in sigle data item named 'Incurred Bands'

CASE WHEN
[Total Incurred] <= 1500
THEN
'[0;1500]'
WHEN
(([Total Incurred]>1500) AND ([Total Incurred]<=3000))
THEN
'[1500;3000]'
WHEN
(([Total Incurred]>3000) AND ([Total Incurred]<=7500))
THEN
'[3000;7500]'
WHEN
(([Total Incurred]>7500) AND ([Total Incurred]<=15000))
THEN
'[7500;15000]'
WHEN
(([Total Incurred]>15000) AND ([Total Incurred]<=75000))
THEN
'[15000;75000]'
WHEN
(([Total Incurred]>75000) AND ([Total Incurred]<=150000))
THEN
'[75000;150000]'
WHEN
([Total Incurred]>150000)
THEN
'Over 150000'
END

but i am not able to get any amounts. entire crosstab is blank with only one row named  Incurred Bands and columns name number of clolumns and Total Incurred.

wheni check for tabular data i am getting fine result but nt in crosstab.

Please do guide on this.
Any help will be highly appreciated.
Thanks in advance :)

Nandini.t

Dear all

Any updates on this :(
Please do share your ideas. :)

blom0344

The output of your case is a string. A Cognos crosstab will expect a value with an aggregation set otherwise no data is shown.  The best solution is to use a numeric code as output of the case (with an aggregate set) and use a variable to enforce a report expression..

Nandini.t

Hi blom.. very thankful for your valuable reply :)
I have few clarifications.
as per your first statement i altered case as below

CASE WHEN
[Total Incurred] <= 1500
THEN
'1500'
WHEN
(([Total Incurred]>1500) AND ([Total Incurred]<=3000))
THEN
'3000'
WHEN
(([Total Incurred]>3000) AND ([Total Incurred]<=7500))
THEN
'7500'
WHEN
(([Total Incurred]>7500) AND ([Total Incurred]<=15000))
THEN
'15000'
WHEN
(([Total Incurred]>15000) AND ([Total Incurred]<=75000))
THEN
'75000'
WHEN
(([Total Incurred]>75000) AND ([Total Incurred]<=150000))
THEN
'150000'
WHEN
([Total Incurred]>150000)
THEN
'0'
END

'use a variable to enforce a report expression'
can you please elaborate the above statement and why it is required.