Crosstab formulas for columns

building reports crystal reports
1

I have a cross tab built (income statement). My rows are revenue, expenses, revenue per unit, expense per unit, profit, profit per unit, profit %, etc. My columns are years (2007, 2008, 2009, 2008 vs. 2009), etc. I want specific formatting (‘Display String’ option under ‘Format Field’ option) based on what the column is. For my “per unit” and “%” calculations, I’m having to use some code under Display String to get these to show properly for each year (i.e. 2008). But for my difference column (2008 vs. 2009) this does not work and I get the wrong value result. Is there a way to write logic to determine the Display String action based on what my column is? I tried an “If, Then” statement using both the year’s field value and the alias I called the year in the crosstab expert, but the logic does not seem to work. Is there a way to do this? Below is the code I’m using. The else part works for the years but not the difference of years (variance column). Thanks.

//Per Unit
If {ZFI_M50_ZBO_ZFI_M50_Q0001.[D5EHTOYC657IWHX9HIIWDTFTS]} = ‘YTD Act vs. 2008’ or {ZFI_M50_ZBO_ZFI_M50_Q0001.[D5EHTOYC657IWHX9HIIWDTFTS]} = “YTD Actual vs. Exp”
Then
“Test”
Else
(WhilePrintingRecords;
numbervar Sales;
numbervar Units;
if Units <> 0
then totext(Sales / Units,2)
else
‘0.00’)

mikegbuff (BOB member since 2009-05-28)

2

Hi,

To provide proper computation for the 2008 vs 2009, first right click the header name of 2008 then choose calculated member then “select 2008 as first value” then perform this again for the 2009 but the one of the option either sum , difference , product or quotient. New column will inserted.

To check the how the column in crosstab computes, press right click to the value then choose “calculated member” then "edit calculation formula. "

you may add some condition under this computed column.

I hope this will help. :smiley:

nahtanoj :philippines: (BOB member since 2009-07-08)