Cross-tab and Percentages

[twilliamson2]

I am using CR10.

I created a Crosstab that looks like the following example.

Purchase #1 Purchase #2 Purchase#3
JOE 3% 5% 7%
BOB 0% 7% 10%
KATE 9% 2% 8%

How can I increase the decimal on a percentage in a cross tab? For example, I would like the value for Bob's Purchase #1 to be displayed at 0.35%. I used the "increase decimal icon" but nothing happens. Please help!

 

[synapsevampire]

Right click it and select format field->number->customize and change the rounding and decimals to 2 places.

-k

 

[twilliamson2]

I performed that operation. I am using a percent of a distinct count in my crosstab and it will not allow me to increase the decimal size.

 

[synapsevampire]

So I see, never tired it, I guess the distinct count part confuses Crystal's widdle brain.

You could build out a manual crosstab, otherwise I don't see a simple solution here, sorry. I'd tried it with a standard summary and it worked fine.

-k

 

[lbass]

There is a way to do this within an inserted crosstab. First you need to use a command to return the total count that you are basing the percentage on. Go to database->database expert->your datasource->add command->and enter:

Select count(table.`purchaseID`) as tot//whatever the field is you are counting
From `table`table

If you want to evaluate against the row total or column total, you would add a "Group by" line based on the row or column field. Since you are using a distinct count, you would have to add "distinct" into the command also, but how you do this varies by datasource (as does the punctuation), and I wasn't sure how it would work for you.

Then add {command.tot} as another summary in the crosstab, and drag it so that it is the topmost summary. In preview mode, change the summary to "maximum". This should now match the grand total for your crosstab. Then right click on {command.tot}->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tot := currentfieldvalue;
true

Resize the field in preview mode so that it is as narrow vertically as possible.

Then select the count summary ->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar subtot := currentfieldvalue;
false

Then add a final summary based on a formula:

whilereadingrecords;
0

Right click on it->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar tot;
numbervar subtot;
totext(subtot % tot, 2)+"%"

This will now display the percentage with two decimals. This solution is based in part on a solution for adding percentages in crosstabs that was in one of Ken Hamady's newsletters.

-LB