Crosstab summary problem How to create percentofCount in crosstab

[vshah007]

I am creating a cross tab report where I group on State(Row Header), City(Row sub header) and Category(column header). I calculate count of items sold in each category for each city. I also need to show percentage of items sold in each category and city over the total items sold in each city.

Group totals in the crosstab should show total items sold in each category within the state (easy) and % of items sold in each category within a state.

Finally total items sold in each category and % of total items sold in each category over the total items sold

Is there anyway I can do this? PercentOfCount etc did not seem to work.

I have created a view with unions and I calculate all the values within my sql. But I would like to avoid this approach as far as possible

Thanks

 

[lbass]

What version of CR are you using?

-LB

 

[vshah007]

I am using CR 8.5 Developer's edition

 

[lbass]

If you don't have too many categories, then use a manual crosstab. First insert a group on state and then another group on city. If you don't have row inflation, create a series of formulas like the following, one for each category:

//{@CatA}:
if {table.category} = "A" then 1 //substitute your category result for "A"

//{@CatB}:
if {table.category} = "B" then 1 //etc. up to N categories

Then right click on each formula and insert a summary (SUM, not count) at all group levels. Then suppress the detail section.

For your percentages, you would use:

//{@CatACity%} to be placed in the group footer (city):
sum({@CatA},{table.city}) % count({table.item},{table.city})

//{@CatBCity%}:
sum({@CatB},{table.city}) % count({table.item},{table.city})

For percentages at the state level, use:
//{@CatAState%} to be placed in the group footer (state):
sum({@CatA},{table.state}) % count({table.item},{table.state})

At the grand total level, these would look like:

sum({@CatA}) % count({table.item})

If you have row inflation, instead of formulas, you would need to use running totals where you could evaluate on change of field or group in order to get unique counts.

-LB

 

[vshah007]

Thank you for your detailed response. This mannual report is kind of a hardcoding (I have not tried it out but seems like it). I want something that would get automatically generated since none of the grouping fields are fixed. So I created a sql with many unions, so their is a state called z with a city "Total" and so on. Played around a lot with formatting to suppress the unwanted z s etc.

Under the Category I have another group called Type which holds values "Count" and "%"
Only thing that's remaining is, now all the item values (count and percentages) fall under same type and they both are displayed with decimals or as an integer. All my efforts to create a formula to decide whether the decimal shd appear or not have failed. Is such conditional formula possible? If yes, how?
I wrote formula as follows:
//If GridRowColumnValue ("alType") = "%" then Round({spEmployeeAwardsByJustification.TotalAwards},2) else int({spEmployeeAwardsByJustification.TotalAwards})

I have also tried,
If GridRowColumnValue ("alType") = "%" then 1.00 else 1

Hope someone has a solution!

Thanks

 

[lbass]

If these are separate summaries in the same cell, you should be able to right click on each while in preview->format field->number and set the decimals independently of each other. Perhaps I'm misunderstanding.

-LB

 

[vshah007]

They are not seperate summaries. that's same summary (It's a single value. I choose any summary avg / sum)
If I choose 2 summaries they fall one below the other. I need count and % side by side. Hence I added another group called type with 2 types "#" and "%" for each category

CAT1 CAT2 CAT3 CAT4 CAT5 -------------------------------------
#|% #|% #|% #|% #|%
-----------------------------------------------
DC|city1|0 |0.0
|Total|
MD|city2|
|city3|
|Total|
VA|city4|
|city5|
|Total|

 

[lbass]

Are you concatenating SQL expressions in a formula in order to get the horizontal display? If so, you can format them inside the formula, as in:

totext({%count},0) +" "+ totext({%percent},2) + "%"

-LB