Hi,
I have a requirement where in I've got a crosstab and am displaying school percentage as the first row and class percentage as the next row and when school percentage-(minus)class percentage is >35 then i need to display text as 'Pass' else 'Fail'.Percentages are getting displayed correctly however displaying the text as Pass and Fail based on the percentages is not happening in the crosstab.
Could someone let me know whether it is possible to display text based on calculation in a crosstab as a measure if so how.
Thanks in advance
It's should definitely be possible. Just to check, are you using > 0.35? If you are, try changing the Aggregate Function to Calculated.
Cheers
The following post explains it all in detail:
http://www.cognoise.com/community/index.php/topic,10244.0.html
I am calculating percentages individually and based on the difference i am using >35 (If Diff >35 then ('Pass') Else ('Fail')).I tried changing the aggregate function to calculated aswell but it didn't work. Anyway il check the link posted by blom and will let you guys know if it works .
Thanx for your responses
Hi Blom,
Thanks for providing the link. I did go through the link but the case statement posted appears to be related to multilingual reports if i am right.Could you please let me know how to apply it in my scenario.
Thanks
It was just an example... If you use the inner case expression with the appropriate reference to your dataitem you should be alright (assuming you understand how a case construction works)
Hi Blom,
I tried using the case statement in a data item and made the aggregation as calculated,still no data was getting displayed.
Case statement used is
Case
When (Diff<35) then ('Fail')
When (Diff>35) then ('Pass')
Else (' ')
End
Could you please tell me whether my approach is rit
Thanks
Well, I never mentioned setting the aggregate of the measure to calculated. AFAIK, Cognos crosstabs ignores any measures that have no true aggregate set (total,min,max)
The case statement looks suspicious to me. Percentages are always expressed as a fraction of 1.
Can you try the following:
Case
When (Diff< 0.35) then ('Fail')
When (Diff>= 0.35) then ('Pass')
Else Null
End
Hi,
I have done exactly same thing for my report. Please see below steps to do the same.
Create a calculation.
CASE
WHEN [Pass] <= 35)
THEN
(1)
ELSE
(0)
END
Insert the above calculation as a crosstab column, unlock the report and select "Define Contents" property to "yes". Drag a Layout calculation into that column and insert the following expression.
CASE [data item name from above calculation]
WHEN 1
THEN ('Pass')
WHEN 0
THEN ('Fail')
ELSE
('')
END
HTH