Can you subtract values in a Cross-tab report?

[wanzek1]

I have a cross-tab report that has 2 rows. The first row is the source "current" row and the second is the "history" row. There are several columns one for each month for the next 18 months. I would like to be able to in place on the column total for each month column to subtract the current row from the history row. It currently automatically does a sum and I would like the difference.

Is this possible?

My rows are the source and my column is the period.

Thanks!

 

[kenhamady]

The simplest method is to make one number a negative value (by writing a formula or modifying the existing formula). Then you format the cross-tab so that it doesn't show the negative sign. The sum ends up begin the difference.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[wanzek1]

This worked to get my total correct but I struggling getting the sign to reverse correctly. I know I can simply go into the format number option and check the reverse sign for display. However, this then reverses the sign in my current row as well so I end up getting one negative and one postive row. I also tried the option under Negatives to show none. This also doesn't work because if the number is truly negative I need it to show negative.

Is there a formula I could use to reverse for display?

Thanks

 

[LyleU]

Ummm... Multiply your entire formula by -1.



Lyle
----
"For every expert, there is an equal and opposite expert." - Arthur C. Clarke.

 

[kenhamady]

Well my first method is simple but it only works if the numbers are all positive.

Instead lets try a calculated row, but this only works if you are using CR 2008 or later:
1) Right-click on the LABEL of the row that you want to subtract FROM
2) pick "Calculated Member" > "Select xx as First Value"
3) Right-click on the other LABEL
4) pick "Calculated Member" > "Difference ..."

This will add a row that is the difference of the two, then don't show the total.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/