report with group, need to "un-group" for overall totals 1

[trsquared]

Using CR 8.5, oracle db.

Have a report which groups on siteid, then plan. I take the total number of plans times the cost and come up with a total Results look like:

Site ID: 11F4dC01
plan name Cost Cnt Total

Plan A $4.99 1 $4.99
Plan B $14.99 7 $104.93
Plan C $34.99 2 $69.98

Site ID: 11F62501
Plan A $4.99 2 $9.98
Plan B $14.99 1 $14.99
Plan C $34.99 3 $ 104.97

The problem for me is, after this, I need to show an overall total by plan, so all plan A's total count is 3, total cost is $14.97.

Since I've already grouped by the site id, I'm not sure how to essentially "un-do" that to get a total by plan. (I tried a separate sub-report, but it's taking more than 1 hour to run, and I'm sure that's not the most efficient way to do this)

thanks for your help.

 

[lbass]

You could do this two ways:

1-Create a formula for each plan, like:

//{@PlanA}:
if {table.plan} = "Plan A" then {@total}//where {@total} = {table.cost} * {table.cnt}

Right click on each formula and insert a grand total. Or,

2-Insert a running total using the running total editor:
Select {@total}, sum, evaluate based on a formula:

{table.plan} = "Plan A" //change for each running total

Reset never.

If you have no duplicate data, then use the first approach, since running totals can slow your report. If you had duplicate data, you would use a running total and add criteria into the evaluation formula to prevent summing of duplicates.

-LB

 

[trsquared]

Thanks so much. I went with the running total option and it works like a charm!

 

[synapsevampire]

Another option might be to create a summary by using a Cross-tab, often times this provides a nice format for viewing summary data.

Select Insert->Cross-Tab place the Plan in the Rows, and the Cost in the Summarized fields and make sure it's a SUM.

Note that in LB's first solution, you'll get better performance than using the Running Total, and that the value is available PRIOR to the details section, so it can be used for calculating percentages, etc.

-k

 

[trsquared]

Thanks so much for the add'l recommendation synapsevampire.

The report had a parameter for provider, and coding all those different plans was getting to be a bit much. The cross tab filled the bill and I really appreciate the feedback.

Keep up the good work. This site is a lifesaver!