formula using crosstab fields

the problem is that i don´t always want to add in the grand total, the 2 crosstabs could be 1 for sells and 1 for costs, then i want to substract the two values, then i want to multiply this total for other and get other information.

that´s why i do it in parts.

Thanks again.

Okay, in that case I have to say that I don't believe what you want isn't directly possible.

You could possibly figure out a way to use formulas to accumulate your totals (although I'm not sure that you could do this, I won't rule it out) but you would need one formula per column which isn't going to work if you have a variable number of columns.  Even techniques such as creating 12 formulas and suppressing the ones you don't need won't work because a crosstab doesn't give you the information you'd need for conditional suppression.

The only way I can think of to achieve the result you want is to create a seperate report that will show only the bottom line totals that you want and then embed this as a subreport in your main report (probably in the report footer).  I don't know if this subreport would be a crosstab or just a normal report - depends on your data structure.

This is an imperfect solution and will likely take tons of effort to make it work correctly but AFAIK that's you're only option.

frodoman

ok frodoman, i'll try that and tell you if it works,

Two more questions and i close this thing:

i´m not quite sure if it´s possible (or how it´s possible) to change the totals of the crosstab and put another operacion(add, substract, multiply, divide)

2nd:

using crosstab. in one of my crosstab i have this info.

Product January Feb. March
x              1         2       3
y              5         6       7
total         6          8      10

can i make a condition that make all the values in row of product "y" show "0" instead of their actual values?}

so i get .
Product January Feb. March
x               1         2       3
y               0         0       0
total          1         2       3

Thanks.

I believe this comment answered the question:

Comment from frodoman
Date: 09/08/2004 03:30PM EDT


Last posting from perfectshark was additional questions that should be posted separately.

frodoman

This comment also answers the question. I would recommend split between Frodoman/ebolek
Comment from ebolek
Date: 09/07/2004 03:49PM PDT

Regards
Emre

Emre,

I disagree with you on this one.  The original question stated: "THe month columns today are only 3, but next month could be 4, or 6, so i need the total2 field to present the 12 months if it´s necesary and only 3 or 4 if that´s the case.
"

Because of the variable column requirement he needs to use a crosstab.  Your solution would give him totals wanted but would invalidate his stated requirements.

frodoman

Ok... I found a lot of help with your comments, but none of you give me a true answer to the problem.

The key, was... using a variable to define the sum field in a third crosstab, and make a condition that makes (x and y) positive values and (a and b) negatives values.

Then just needed to hide all the values in the third crosstab except the grand total. And that was it.

As a note, this was the key to make a report i needed to present, i use like 5 variables and 11 crosstabs... i know it should be a better way, but still don´t know it.

Thanks to everyone, i´m gonna split the points.