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]

I don't see what you would want to do with these formulas if you have hundreds of columns. This would create two summaries per column, so now you would have 160 to 180 columns. Is this really what you want?

-LB

 

[musini]

Ok Let me explain the crosstab.


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

My row field is Statuscode, Column Previous status code, Summary is Count. The count is nothing but account movements from previous status code to present status code.

If you see row A there are 0,4,7 meance there are 0 accounts moved from A to A, 4 accounts moved from B to A and 7 accounts moved from C to A.

The saem way row B. There are 3 moved from A to B, 0 from B to B and 0 from C to B and same way for row C.

Now I want to see how many accounts exactly moved in to Present status code A,B,C... That is the reason I need a column at the end which gives me the Row total - column total which is the actual number of movements after in's and out for that particular status code.

My row fields are same as column fields. i.e A,B,C. So for Particular column A the grand total would be total accounts moved out of A and for a particular row A the grand total would be total accounts moved in to A. So what I need is total moved in - Total moved out(Will the total present in A now).

Hope this is clear. This a bit complicated to me but if I can achieve it that would be great.

Thanks,
M

 

[lbass]

You didn't confirm my main question. How many instances of the column field are there? If it's only three, a manual crosstab is the solution. If there are 80+, then it's not.

-LB

 

[musini]

I have 80 plus. So, is there a solution to achieve the total difference.

Thanks,
M

 

[musini]

Also say me how it is possible if it is less than 80 plus.

Thanks,
M

 

[lbass]

Can you please clarify something? Originally, you wanted to use a formula that was based on the value of Column C compared to the summary for all other columns. Now you seem not to want to treat column C any differently. Which is it?

-LB

 

[musini]

Yes LB, Orginally they asked for that but now there are few changes in the requirement and what they want exactly is the accounts in to the status code minus the accounts went out of the status code as mention in my 16 Mar 08 14:06's post.

Thanks,
M

 

[lbass]

Using your above example, the results would look like this (I designated these new totals with a "2"), if I'm following you correctly:

           Total    A  A2  B  B2  C   C2
Total        20     3     10      7   
A            11     0  8   4      7
B            3      3      0  -7  0
C            6      0      6      0   -1

...where the row total - column total intersect in only one cell per row/column. You would have to add two new fields to the select clause in your command, something like this:

(
select count(`field`)
from table A
where A.`prevstcode` = table.`prevstcode`
) as prev,
(
select count(`field`)
from table A
where A.`stcode` = table.`stcode`
) as curr

You would have to adapt this to your datasource (punctuation and syntax), and substitute your actual table name and field names (leaving the "A" as is.

Then in the crosstab, you would add {command.prev} and {command.curr} as summary fields, changing the summaries to maximums for each of them. Go to the customize style tab->check horizontal summaries and "show labels". Then select an inner cell for the curr field->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar curr;
if gridrowcolumnvalue("command.prevstcode") =
gridrowcolumnvalue("command.stcode") then
curr := currentfieldvalue;
true

Then select prev->format field->suppress->x+2 and enter:
whileprintingrecords;
numbervar prev;
if gridrowcolumnvalue("command.prevstcode") =
gridrowcolumnvalue("command.stcode") then
prev := currentfieldvalue;
false

Then in the same tab ->display string->x+2 and enter:
whileprintingrecords;
numbervar curr;
numbervar prev;
stringvar x;
if gridrowcolumnvalue("command.prevstcode") =
gridrowcolumnvalue("command.stcode") then
x := totext(curr-prev,0,"") else
x := "";
x

Then suppress the row totals for curr and prev. Resize them along with the suppressed curr column in design mode to minimize their width.

This might look better if you presented it as a separate crosstab with figures only in the diagonal (without the count summary).

-LB

 

[musini]

Thanks so much LB for all your efforts in helping me with this. Did not try your formula yet but looks like it is goin to work for me. Thanks once again.

M