Cross Tab- Totals

[mcrisf]

How can i use in a formula the total of a crosstab?
Crystal doesnt show me those in the report fields.I need to subtract 2 different totals.
thx

 

[lbass]

I know this refers back to your previous thread. Please show a sample crosstab and identify what total you want to subtract from what total. It's really not clear.

-LB

 

[mcrisf]

I will try again...
i want on columns PRODUCTS, on rows SALESREPs.
measures MTD, YTD, %, GoalsMTD, GoalsYTD, %
i want Totals (for every measure MTD, YTD..etc) of Products in row and Totals of Products in columns.
the question is:
Can i subtract two totals of product in columns?
i cannot make a formula, because i dont see them avalaible in the report fields.
thx

 

[lbass]

Please try to respond to requests. I asked for sample data. It is still not clear which totals you want to subtract.

-LB

 

[mcrisf]

Sum of MTD - Sum of GoalsMTD and Sum of YTD-Sum of GoalsYTD (in the CrossTab's bottom)...but those are not available in the report fields.

 

[mcrisf]

P1 P2
mtd ytd goalmtd
SR1 4 5 6
SR2 8 8 9


tot 12 13 15


Question how can i do : 12-15?

 

[lbass]

Thank you. In an inserted crosstab, select the mtd total->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar mtd;

if gridrowcolumnvalue("measures") = "mtd" and
gridrowcolumnvalue("period") = "P1" then
mtd := currentfieldvalue;//this sets the variable to the value in this cell
false //this says don't suppress

Instead of "measures" you should put the field that results in the "mtd", "ytd", etc. Remove the curly brackets and replace with "". For "period", do the same, substituting the name of the field for your highest order row.

Next select the goalmtd column total->format field->suppress->x+2 and enter:

whileprintingrecords;
whileprintingrecords;
numbervar goalmtd;

if gridrowcolumnvalue("measures") = "goalmtd" and
gridrowcolumnvalue("period") = "P1" then
goalmtd := currentfieldvalue;
false

Then you can create a formula in the main report, in a section below the one in which the crosstab is executing, like the following:

whileprintingrecords;
numbervar goalmtd;
numbervar mtd;
goalmtd-mtd

If you want to display the difference within the crosstab, you would have to create a formula:

whilereadingrecords;
0

Add this as a second summary (below the first). Suppress the inner cells and then select where you want this displayed. Let's say you want it below the goalmtd total. You would select {@0}->format field->DISPLAY STRING and enter:

whileprintingrecords;
numbervar goalmtd;
numbervar mtd;
if gridrowcolumnvalue("measures") = "goalmtd" and
gridrowcolumnvalue("period") = "P1" then
totext(goalmtd-mtd);

-LB

 

[mcrisf]

thank you so much LB and i appreciate your patience..thx again...!

 

[mcrisf]

but i dont understand this part
"
Then you can create a formula in the main report, in a section below the one in which the crosstab is executing, like the following:

whileprintingrecords;
numbervar goalmtd;
numbervar mtd;
goalmtd-mtd

If you want to display the difference within the crosstab, you would have to create a formula:

whilereadingrecords;
0
"
could you be more clear?thx

 

[lbass]

Which do you want to do? Display the result within the crosstab or in some other report section?

-LB