I have a cross tab that has expense and revenues by department. They are shown as positive numbers. However, the sum needs to subtract the revenue and expenses. Is this possible in a crosstab?
If I understand your problem correctly, the only way I could suggest that you get out of this "difficult" situation is by multiplying your expenses by -1. I understand that this is going to be a problem because people do not want to see these numbers as negatives right ?
What you do then is in the "data format" for this column under "Negative Pattern" at the very bottom.. put 0000000000000 or something that will for sure cover the number of digits that you need.
Hope this helped
Yes but the rows and the sum are tied to the same formatting, correct? so I will mask a true negative in the SUM if I do this?
This is a difficult problem, i think :P
That's true. One thing you could do is have your total in a separate crosstab. Depending on the output formats, this is more or less a pain in the as... In pdf and html it becomes quite difficult, but if it's xls then it's quite easy.
Let me know if you would like me to develop more on this.
cheers
This was solved by creating a tabular set. One TM had the positive values and the other TM had the sum of the group with a static name "Total" used to add a new row for the group. Another data item was added to set the sort order of the columns, so total would always trail all other values.
Basically there is no summary calculation in the crosstab, rather, the summary for each group was computed in the query and passed as just another row for that group.