Crosstab - Summary field/total of summarized field

[dhv919401]

I have a crosstab as below:
Year
Quarter
Month MonthTotal
District1 District 2 DistrictTotal
Unit1 1 18 19
Unit2 3 16 19
Total 4 34 38


Crosstab rows selected: unit
Crosstab columns selected: Year,quarter,Month,District
Crosstab summary: OperatingDays

I need to create another cross-tab in the same report as follows:

Year
Quarter
Month MonthTotal
District1 District 2 DistrictTotal
Unit1 1/19 18/19 1/19+18/19
Unit2 3/19 16/19 3/19+16/19
Total 1/19+3/19 18/19+16/19 (1/19+3/19)+(18/19+16/19)

Basically I need to populate another crosstab taking the Value for each (unit,district) combination and divide it by the total of all districts for that unit.

I am familiar with the crosstab functions like currentfieldvalue etc...

OR I can show these values as a second summary line in the same crosstab also.

I have seen some cool solutions given by lbass on crosstabs in this forum. Could you please help me with your thoughts?
Thank you,

 

[lbass]

What you are showing in the second crosstabs are essentially percentages. If you add the same summary field again and choose "show as percentage of column total", you will get a percentage result which is the equivalent of the ratios. Or I guess the issue is that you want the percentages to be based on the subtotal, not the entire total for the row, i.e., per month, not per year?

-LB

 

[dhv919401]

Exactly. The division should be between the grid cell value/sub-total for that month. The result should come in individual grid cells.
Please let me know if you need any other information.
Thank you for all your help and time.

 

[dhv919401]

I am sorry to miss this detail. I am doing this report in Crstal 9.0. The percentage summary is not there in the available options.

 

[lbass]

How many districts are possible?

-LB

 

[dhv919401]

Right now 8 districts and may be 60 units are there. They could increase eventually.

 

[lbass]

On second thought, the following works. This requires you to have a group on {table.date}, monthly in your main report. Create a formula {@summo}:

sum({table.days},{table.date},"monthly")

Add this as your second summary in the crosstab, and change the summary to maximum. Then select the first summary in both an inner cell and the month total column (per unit)->format field->common->suppess->x+2 and enter:

whileprintingrecords;
numbervar days := currentfieldvalue;
false

Then select the max of {@summo} (your second summary for both inner cell and month totalformat field->common->suppess->x+2 and enter:

whileprintingrecords;
numbervar motot := currentfieldvalue;
false

Then select the same cells->format field->common->display string->x+2 and enter:

whileprintingrecords;
numbervar days;
numbervar motot;
stringvar displ;
numbervar adddispl;
if motot <> 0 then
displ :=
totext(days/motot,2) else //2 decimals
displ := '0';
adddispl := adddispl + val(displ);
displ

Finally, select the maximum of {@summmo} in the column total per district and the month column total->format field->common->suppress if duplicated (NOT suppress)->x+2 and enter:

whileprintingrecords;
numbervar adddispl := 0;
false

Then in the same screen, go to display string->x+2 and enter:

whileprintingrecords;
numbervar adddispl;
totext(adddispl,2)

-LB

 

[dhv919401]

I am very eager to implement this. Tomorrow as soon as I get to work I implement this and reply again. Thank you sooo much for your time and help.

 

[dhv919401]

Unfortunately, this solution I already implemented before. Further explanation:

Year
Quarter
Month MonthTotal
District1 District 2 DistrictTotal
Unit1 1 18 19
Unit2 3 16 19
Total 4 34 38

The solution you mentioned will give me the following:

Year
Quarter
Month MonthTotal
District1 District 2 DistrictTotal
Unit1 1/38 18/38 19/38
Unit2 3/38 16/38 19/38
Total 4/38 34/38 38/38



But I want the following:


Year
Quarter
Month MonthTotal
District1 District 2 DistrictTotal
Unit1 1/19 18/19 19/19
Unit2 3/19 16/19 19/19
Total 4/38 34/38 38/38

I want to divide each cell value by the total of all the districts for that unit. This means the row total. In this case the row total for Unit1 and Unit2 are the same which is 19.

I do not know how to get the total for District1 and District2 for Unit1. This is the problem.

 

[dhv919401]

I created another group by unit and created the summary formula field by the Unit and "monthly". This solved my problem. I still want to keep this chain open as I need to do variations of this report.
I will be bugging for some more time I think......

Thank you very much once again for your valuable time and your kind help....

 

[dhv919401]

Now... another problem. When I am trying to show the column total for each district, it is resetting the adddispl value to 0 for each page and also it is showing the total for the entire month because it is a running total. How do I display column totals and row totals for the displayed values?