Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can you subtract values in a Cross-tab report?

Status
Not open for further replies.

wanzek1

Technical User
Jun 13, 2012
79
US
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!
 
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
 
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
 
Ummm... Multiply your entire formula by -1.



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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top