Cross Tab Eliminating total. 1

[musini]

Hi I am Working on a cross tab report in crystal XI.

My data is like this.

col1 col2 count
A B 4
B A 3
A c 7
C B 6

In the cross tab Col1 is in the ROWS of cross tab, col2 is in the COLUMNS of cross tab. and the count should be dispaled as summary field. Every thing is working fine but I am getting a total field as one row and one column in the report which I do not need. Below is the o/p what I am getting and what I want.

O/P I am getting.

Total A B C
Total 20 3 10 7
A 11 0 4 7
B 3 3 0 0
C 6 0 6 0

What I want is same as above but with out the total column and the toal row I am not if it is possible but if yes please help me.

Thanks,
M

 

[lbass]

Go to the customize style tab in the crosstab expert and check "Suppress row totals" and "suppress column totals".

-LB

 

[musini]

Sorry to ask such a simple question here. Thanks for the reply.

M

 

[musini]

One more question on cross tab as this is my first cross tab report. In the above example I have mentioned is there a possibility of getting the total of only column A, B and eliminate column C from the TOTAL. I want to display columns A,B and C but I do not want it to be included in the total.

Thanks,
M

 

[lbass]

You would have to add another column based on the following formula {@ABnotC} and make it your column number 1:

if {table.field} in ["A","B"] then "A and B" else "C"

Then you will have a subtotal for both A and B and also one for "C". You can then suppress the subtotal for "C" by using a conditional suppression formula on the label and cells:

gridrowcolumnvalue("@ABnotC") = "C"

Note that the text in the parens replaces the curly brackets around the formula name with double quotes. The only problem is that I don't think the suppressed column will disappear. Although you can try checking "suppress blank columns," I have found that this does not work consistently.

-LB

 

[musini]

LB Thanks so much. Your solution did work but the problem is I do not have only two columns in the report. I have around 100 columns out of which I do not want one column to be included in the TOTAL. This formula becomes very lenngthy and the columns names are not small as A, B and C but are words like "current code" and small mistake would not provide the desired result. Is there any other way of writing the formula, so that it eliminates this tedious process.

Just curious to know if there is any.

Thanks,
M

 

[lbass]

if {table.field} <> "C" then "Other" else "C"

-LB

 

[musini]

Thanks LB. Seems like the supress is causing the problems while exporting the report to excel. In the report the supress works fine only for the row total I want to supress but if I export it to excel all the row totals are exported as blank. Is there any solution to this.

M

 

[lbass]

You could try handling this by using two separate crosstabs, one for the column C, and the other for all others. You would use conditional formulas in each, like:

if {table.field} <> "C" then {table.field} //= "C"

The easiest approach would be to add the one-column crosstab first and then overlay the second with multiple values. You would have to suppress the labels and remove the grid lines on the labels for the second crosstab.

-LB

 

[musini]

LB Thanks. It looks like this solved the problem of the supressing but the second crosstab i.e if {table.field} <> "C" then {table.field} is not displaying "C" column in the crosstab but the TOTAL column is considering "C" column and giving me the total including the "C" values in it.

My criteria is I need the total for all the other columns except column "C" and just the column "C" aside.

This is what I did as you said. I created a formula like this.
if {table.field} <> "C" then {table.field}.

Used formula field in the crosstab column insted of the {table.field} and this works fine as far as displaying columns are considered. After doing this I am displaying the row totals as a column "TOTAL" but this column is summing the numbers from the column "C" aslo which I do not want.

Thanks,
M

 

[lbass]

No, the formula should not do that. What is your summary field and what type of summary are you using? If you are summarizing a formula, please show the content of the formula.

-LB

 

[musini]

Lb, Before I was using the formula field
if {table.field} <> "C" then {table.field}
for the column of the cross tab and the summary was on the table column "count" directly.
But now I changed that summary "counnt" field to a formula field like this.
if {table.field} <> "C" then {table.COUNT}
and this works as what is expected.

Thanks,
M

 

[musini]

Thanks so much for all the Help. I am still working on it and post any questions related to it further.

M

 

[musini]

LB. As I told now the report looks exactly how I wanted but when I export it to excel only one crosstab(1st placed one) is being exported out of the two. Any solution?

 

[lbass]

Sorry, but I rarely export and am not sure what might be causing that or how to fix it. Maybe someone else does.

-LB

 

[musini]

Hi,

I have a small problem again. In the below example I need a new cross tab column to be added to show the (column C value)+(row sum of that particular row) - (column sum of that particular column)

EX: This is my output now.

Total C Total A B
Total 15 15 13 3 10
A 5 5 4 0 4
B 6 6 3 3 0




I need a new column to the above o/p like this.

Total C Total A B D
Total 11 11 7 3 4
A 5 5 4 0 4 5+4-3
B 6 6 3 3 0 6+3-4

In the above example
5+4-6 is 5 is total for C, 4 is total for A Row, 3 is total for A column.
same way 6+3-4 is 6 is total for C, 3 is total for B row, 4 is total B column.

May be LB should understand this as he was helping me do this from the begining. I am not sure if this possible but the requirement asks for that. If adding a new column is not possible is there any other way to do this. I am using one cross tab in the reort.

Thanks,
M

 

[lbass]

This would be much cleaner if you set it up as a manual crosstab. If this is part of a larger report, I would add a subreport so that you can do the manual crosstab. You would just group on your row field, and then create one conditional formula for each column field and then insert summaries on these formulas.

If you want help with this, please identify your row, column, and summary fields using the convention {table.field}, and identify what kind of summary you are using.

-LB

 

[musini]

This would be much cleaner if you set it up as a manual crosstab.

If I have to do manual cross tab, then I have to place it in the details section, this is what I believe, correct me if I am wrong. If I am right my cross tab is in the report header section and how to display the cross tab and the manual cross tab for the last column(D) side by side. My cross tab aroung 80 or 90 fields in it.

I understud what you said by is displaying not a problem.

Thanks,
M

 

[musini]

Or if there is a way to atleast do only the (5-3) and the (6-4) insted of (5+4-3) and (6+3-4) and display it as a column in the same cross that should also be fine.

Thanks,
M

 

[musini]

Ok I got what you were saying about the subreport.
My Rowfield is {command.stcode}
My column field is {command.prevstcode}
my summary field is {command.count} (Now I am doing a sum on the count field in the crosstab)

Thanks,
M