Trouble using sum(fields) to calcuate in Cross-Tab

[glthornton]

Hi everyone,

I'm using data from an Access database and CR 10 for the report. I'm having trouble trying to perform simple calculations within a cross-tab report. Here is a sample of my report:

[b]            Day 1        Day 2
               Other        Other      Total
Dr. Name  PCP   PCP    PCP   PCP  | PCP  Other PCP[/b]
Dr. S      0     7      12    2   |  12      9
Dr. T      6     1      8     0   |  14      1
Dr. U      13    0      12    2   |  25      2

I am using letting CR perform a Sum for both the PCP and Other PCP fields in the Total area. I read in a previous post, lbass said that you cannot use the totals from these calculated fields. I thought, "okay", well I'll just calculate them again using the Running Total Fields function and that works fine. But I cannot seem to combine these 2 function fields together providing me with a total of all appointments. This is what I'd like to end up with:

[b]             Day 1        Day 2       Total
               Other        Other        Other Appt   Continuity
Dr. Name  PCP   PCP    PCP   PCP  | PCP   PCP  Total       %[/b]
Dr. S      0     7      12    2   |  12    9    21        57.1
Dr. T      6     1      8     0   |  14    1    15        93.3
Dr. U      13    0      12    2   |  25    2    27        92.5

The continuity percentage is Total(PCP)/Total(Appt). So I'm trying to get the last 2 columns. Can anyone possibly assist me in getting this to work?

Thank you,

glthornton

 

[lbass]

Please clarify whether you are still using an inserted crosstab or whether you have created a manual crosstab using running totals.

You could do this by overlapping three inserted crosstabs, if you always had the same number of days in the report. Or there is definitely a way to do this manually.

-LB

 

[glthornton]

lbass, I am using a manual cross-tab report with running totals. The reports that I create have to be "flexible" to the number of days defined within a date range. I see where you are going with this if there were a "fixed" number of days. But I don't think we can take that approach.

Thank you,
glthornton

 

[lbass]

Actually, on second thought, I think you can do this in an inserted crosstab. Remove the PCP type field from the column area, and instead create four formulas:

//{@PCP}:
if {table.type} = "PCP" then 1

//{@Other PCP}:
if {table.type} <> "PCP" then 1

//{@Total}:
{@PCP}+{@Other PCP}

//{@PCP Percent}:
if {@PCP} > 0 then
{@PCP} % {@Total}

Add these as summary fields and then go to the customize style tab within the crosstab expert and check "horizontal summaries" and "show summary labels". You can edit each label to remove the "@" sign. Then suppress the inner columns for {@Total} and {@PCP Percent} and resize them so that their width is minimized. Also click on the percent column and click on the % icon in the toolbar to display the % sign.

I just tested this in 11.0 with very good results. Not sure whether 10.0 has the same capabilities for horizontal summaries and labels, but I'm guessing it might. This approach has the advantage of being able to accommodate any number of days without having to create new running totals and moving things around.

-LB