How can I use a CASE WHEN or IIF statement in JasperReports Server Crosstab?

[lee.owens]

Hello,

I am building a crosstab report in JRS and I'm trying to calculate a daily average sales figure for each month.

The report looks like the below

 

 

The trading days calculation is set up dynamically for the last 3 months as a derived table, so i have 3 values for each of the previous months.

I'm now trying to set up some logic, so that if the column header = 3 months ago, then divide the value by "Trading Days 3 Months Ago". If the column header = 2 months ago, then divide the value by "Trading Days 2 Months Ago" respectively.

I have tried to play around with the IF and CASE WHEN functions in the cacluated field options, but I can't seem to make it work.

Any ideas?

Thank you

 

[jkeri]

I would suggest perhaps doing as much of the CASE work in the SQL . Ive had situations similar to yours and it was easier to get the SQL to do it.