Cross tab formula

[Stevan23]

Hi all,

We are trying to come up with a forecast report and are trying to calculate the opportunity cost. We have created a formula - ABS(IF(D22<0),(-D22-E22)) - in Excel where this is basically saying if the net quantity difference between the forecast, actual and sales for the second most right column (the second largest egg size) then subtract the largest egg net quantity (the further most right column) from the second largest egg net quantity and make it an absolute value. This will tell us the quantity downgraded (the larger eggs being packed as smaller eggs which loses us money). This formula repeats leftward until the smallest egg size where the net quantity is set at 0.

Can this formula be replicated in crystal cross tabs?

Many thanks.

 

[Madawc]

Crosstabs can only use values calculated on the basis of a single detail line, but this can include quite complex formulas. That seems to be the case with the exampleyou gave.

This is based on Crystal 2008. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options.

As far as I can remember, version 8.5 would only use data fields for crosstabs. Version 10 did allow it. I never used version 9.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Stevan23]

Thanks Madawc, sorry about that. We are indeed using Crystal Reports 2008.

Several of the rows are formula based (calculating percentages, differences etc).

I am struggling to come up with a formula that contains the right logic to perform this calculation.

I made a formula using an if then else statement to see what would happen with any row that was negative based on group name and this worked but have no idea on how to write the formula to behave like the previous post.

Would anyone be able help with the code side?

I've read that you can get GridRowValues etc and I don't know if that would be a starting point?

Any help would be much appreciated.

 

[Madawc]

As I said, it needs to be based on a detail line before it can be summed in a crosstab.

If that won't work, look up 'Mock Crosstabs'.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP