Help on Crosstab Percentage of Sum 2

[FMHarris]

Example A is what a no frills traditional crosstab would look like.

EXAMPLE A:
--------------------------
D H N S U Tot
a 2 1 2 3 2 10
20% 10% 20% 30% 20% 100%
b 1 2 1 4 2 10
10% 20% 10% 40% 20% 100%
c 3 3 1 2 1 10
30% 30% 10% 20% 10% 100%
Tot 6 6 4 9 5 30
20% 20% 13% 30% 17% 100%
--------------------------

I've attempted building Example B with no luck.

EXAMPLE B:
--------------------------
D H N S U Tot
a 2 1 2 3 2 10
6.6% 3.3% 6.6% 10% 6.6% 33%
b 1 2 1 4 2 10
3.3% 6.6% 3.3% 13.3% 6.6% 33%
c 3 3 1 2 1 10
10% 10% 3.3% 6.6% 3.3% 33%
Tot 6 6 4 9 5 30
20% 20% 13% 30% 17% 100%
--------------------------

Here's what i'm trying to do, I want the percentages under the row data to show their percentage of the group total. And the row summaries to show their percentage of the group total. And the column summaries to show their percentage of the group total. Can someone assist me?

 

[lbass]

Had to look up your version in another thread. CR 10 right? Please always specify. Is your summary a count or a sum? What you are showing here is the percent of the grand total.

Create a formula. If the summary is a sum, the formula should be:

{table.amt} % sum({table.amt})

If it is a count, it should be:

1 % count({table.field})

Add the formula to your crosstab and in either case, insert a sum on it. Then in preview mode, select the percent summary and click on the % icon in the toolbar.

-LB

 

[FMHarris]

That worked great, as usual your extraordinarily quick with the correct answer lbass!!

For the sake of future postings. Can you tell me how would this work if there's more than one grouping?

 

[lbass]

I'm not sure what you mean by "Grouping". Are you referring to a row or column? They are both "groups" within the crosstab. I don't think adding an additional row or column would matter.

-LB

 

[FMHarris]

I figured it out. Thanks for the head start with your initial answer.

 

[porsche2k7]

Hi I am in a similar situation. Can you please let me know how you were able to figure it out. here is what i am trying to acheive using a cross tab in CR XI

EXAMPLE B:
--------------------------
D H N S U Tot

a1 2 1 2 3 2 10
a2 1 1 1 1 1 5
pct(a2/a1) 50% 100% 50% 33.33% 50% 50%

b1 2 1 2 3 2 10
b2 1 1 1 1 1 5
pct(b2/b1) 50% 100% 50% 33.33% 50% 50%

c1 2 1 2 3 2 10
c2 1 1 1 1 1 5
pct(c2/c1) 50% 100% 50% 33.33% 50% 50%

Tot1 6 3 6 9 6 30
Tot2 3 3 3 3 3 15
Tot(pct) 50% 100% 50% 33.33% 50% 50%
--------------------------

 

[lbass]

This is not the same issue. Please provide the name of your row field. If we assume that a1,a2,b1,b2, etc. are all instances of the same field, is there also a field that distinguishes a1 and a2 from b1 and b2?

What kind of summary is being inserted? What is the name of the field you are summarizing?

-LB

 

[porsche2k7]

Hi, Sorry about the confusion. Here are the names of the fields. This should give you a better idea.. I used the cross tab expert with Month-Year as rows and State as colums with Total & Coded as Summarized fields.. I need to included percent coded in the summarized fields as well which would be (coded/total)*100. Any suggestions on how to obtain this..

EXAMPLE B:
--------------------------
CA OH NC SC Tot

Jan-07
Total 2 1 2 3 10
Coded 1 1 1 1 5
pct coded 50% 100% 50% 33.33% 50%
Feb-07
Total 2 1 2 3 10
Coded 1 1 1 1 5
pct coded 50% 100% 50% 33.33% 50%
Mar-07
Total 2 1 2 3 10
Coded 1 1 1 1 5
pct coded 50% 100% 50% 33.33% 50%
TOTAL
Total 6 3 6 9 30
Coded 3 3 3 3 15
Tot(pct) 50% 100% 50% 33.33% 50%

 

[lbass]

Create a formula {@0} to use as a holder field. Add this as your third summary field in the crosstab. Then select the inner cell and row summary for the total summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
false

Then select the coded summary (inner cell and row total)->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar coded := currentfieldvalue;
false

Then select {@0}(inner cell and row total)->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar tot;
numbervar coded;
if tot > 0 then
totext(coded%tot,2)+"%" //2 for two decimals

Go to the customize style tab and in the summary section choose "show labels". Then change the resulting labels to reflect your desired summary labels.

-LB

 

[porsche2k7]

THANK YOU VERY MUCH AND YOU ARE LIKE SANTA.. I included the same for grand totals also and it works great... thanks again.

 

[porsche2k7]

Hi, This is regarding formatting in a cross tab report. Is there a way I can allign the text in the summarized fields to be centered vertically. I am able to center/justified the data horizontally but can this be done vertically.

Thanks,

 

[porsche2k7]

Hi, For the same cross tab report I have a question regarding highlighting. I need to highlight all the pcd coded < 95% in red. When I use the highlight expert on this cell is has three options (value of the field,row and column). I chose value of the fields less than 95 and it does not work. Can you suggest any work around. thanks.

 

[porsche2k7]

Regarding highlighting.. we are using totext and converting the number to string.. hence highlight expert is not able to compare value of this field less than 95.. however the display string requires to return a string.. is there any workaround on how to highlight this field.. thanks in advance..

whileprintingrecords;
numbervar tot;
numbervar coded;
if tot > 0 then
totext(coded%tot,2)+"%" //2 for two decimals

 

[lbass]

There is no way to center vertically that I know of.

The reason the highlighting isn't working is that the actual field value is 0, not the displayed value. Instead of using teh highlight expert, select the summary->format field->borders->color->background->x+2 and enter:

whileprintingrecords;
numbervar tot;
numbervar coded;
if tot = 0 or
coded % tot < 95 then
crYellow else
crNoColor

-LB

 

[porsche2k7]

Got it. Thanks again.

 

[CIBS]

Hi

How do I create a formula to basically give me 2 values the values are that the first accurance of the number diplay 1 and any duplicates display 2. this will be grouped by the days

Also I'm using CR9

Cheers

 

[lbass]

Please start a new thread. Your question has nothing to do with this topic. You also need to clarify, maybe by showing a sample of what you want to do.

-LB

 

[porsche2k7]

Regarding vertical formatting.. is it possible to insert a new line before the summarized fields? Thanks.

 

[lbass]

You could add another summary, move it the position where you want the extra space, and then suppress it.

-LB

 

[porsche2k7]

Thank you.. this will work..