Calculated Pct of total

[simpelli]

Greetings,

I have a report that only displays the group level summary (no detail section) All quantity fields in the group are based on calculated running sum fields (shared variables) in the suppressed detail section. There's quite a few conditions that must be evaluated for the quantity to "qualify" for being added to the running sum. There's also similar running sum formulas for adding group totals to get a grand total.

All of this works fine and the quantities are correct. The problem is that the report requires a "% of total" column on each group summary. Since the total is not known until the end of the report, this is not easy to do. I need a way to capture the grand total somehow, and use it to determine the perccentage "earlier" than is should be used.

I tried creating an array (whilereadingrecords), using the same conditions that create each summary, then using the sum of the array elements, but the array didn't populate. (Created it in the report/selection criteria code)

Any tricks to get around this?

 

[lbass]

You could recreate the report in a subreport placed in the report header with a formula for the grand total as a shared variable, which then would be available at each group level in the main report for the percentage calculations.

Or, if you have a small number of groups, you could create each running total so that it is specific to each group and does not reset, and so that the result can be carried forward into the report footer. Both the group and the report level results would then be displayed in the report footer, and the percentage calculations would be performed there as well.

-LB

 

[SMerrill]

LB:
I have a similar problem, but I don't grok your second answer. Can you elaborate?

My goal is to have the denominator of a percentage in the detail section to be the sum of just some rows.

My report has a detail line showing a number and a percentage. The percentage computes with the following code, on a running sum. Unfortunately, the denominator seems to change with every subsequent detail line.

if {#Denominator} = 0 then
  formula = 0 
else
  formula = 100 * {v.Value}/{#Denominator}
end if

The {#Denominator} computes by saying:

SUM the value {v.Value}
EVALUATE if row number 4,5,6 or 7
RESET on change of Group2.

There are only two groups.


Take care,
--Shaun Merrill
Seattle, WA

 

[SMerrill]

I can design SQL too. Should I create another query that computes the sums, and join it on the key fields?


Take care,
--Shaun Merrill
Seattle, WA

 

[lbass]

Please provide more info about the concept behind the report. What do rows 4,5,6, and 7 have in common that leads you to want to evaluate the percentage share of the sum of these fields? Also how are you defining "row number"?

-LB

 

[SMerrill]

The report is a pseudo-crosstab. By this I mean the Crosstab tool was ineffective for this design. So I rearchitected the SQL query behind the scenes to give me conditional sums within groups, which is the only way to write a crosstab-style query in SQL 7. Now I always have eight named columns and nine rows with a Row# in the data.

The row numbers are for each detail line. The Group2 is WeekOfYear, and Group1 is the Telephone Extension, called "DNIS."

My recordset coming into the query now has the following columns:

(Year, Month, WeekOfYear, RowNum, RowTitle, DNIS, Sum1, Sum2, Sum3...)
Where these come over into Crystal is as follows:
Year, Month:      Record Selection Formula
WeekOfYear:       Group2
RowNum, RowTitle: Detail
DNIS:             Group1
Sum1 - Sum8:      Columns

So much for architecture. Certain rows (2,3,4,5 of 8) have a meaning distinct from the other rows, and they must be calculated seperately. Each answer is a percentage of the whole, but the whole is the sum of a column in rows 2,3,4,5.
I can get it to work if the whole is all eight rows, but that's not accurate.

Does that answer your questions?

Take care,
--Shaun Merrill
Seattle, WA

 

[lbass]

If the row # is hard-coded, not a running total, then you can write a formula {@denom} for the details section:

if {table.rowno} in [4,5,6,7] then {v.value}

Then write a formula for the percentage also to be placed at the detail level:

if {@denom} <> 0 then
{v.value} % sum({@denom},{yourgroup2.field}) else 0

-LB