Help with Running total

[mflahive]

Using CR9...
I created a report that shows different states within a Pool of Note ID's. I am having a problem calculating the percentage since there can be more than one state per Note ID.

Here is what I need the report to show:

State #ofLoans $Balance %Balance
IL 4 2,615,133 27.08%
MO 8 5,335,334 55.25%
OH 4 1,706,925 17.67%

Total 16 9,657,391 100.00%

The report is grouped on Note ID and State. I created running totals to get the balances per state (located in State Group Footer) and the total balance for the pool (located in the report footer).

The problem I am having is using the #totalBalance in the State group while calculating the %. I was trying to use the following formula (#BalancePerState/#totalBalance)*100
but since there can be more than one State per loan my percentage looked like this:

%Balance

100.00%
67.11%
17.67%

100.00%

I tried to create a sum formula to get the total balance but I couldn't find anything that worked and I'm not sure what else to try. If anyone has a suggestion for a formula, I'd appreciate it.
Thanks.

 

[kskid]

Just create another running total but select percentage of total

Cheers,

-LW

 

[mflahive]

Hi LW -
I don't have that option available as a "type of summary" for a running total.
Thanks though.
M

 

[lbass]

Please explain your report structure a little better. It sounds like NoteID is the inner group, but it is not clear. If you are looking for the percentage at the report level, you need to change your state running totals so that the evaluation section specifies the state in a formula:

{table.state} = "IL"

Then use reset never, in order to carry the running total to the end of the report. For the {#totalbalance}, it sounds like you need to do a sum of {table.balance} for unique NoteIDs. You could create a formula like the following {@totalbal} to be placed in the detail section:

whileprintingrecords;
numbervar totbal;
stringvar notes;

if instr(notes, {table.NoteID}) = 0 then
(notes := notes + {table.NoteID};
totbal := totbal + {table.balance});

Then create a display formula to be placed in the report footer:

whileprintingrecords;
numbervar totbal;

Then for the calculation, use formulas (one for each state) like:
whileprintingrecords;
numbervar totbal;

{#Illinois} % totbal

-LB

 

[mflahive]

Thanks LB, but I can not specify the state in a formula because the report will be run for different Pools and the states will vary from Pool to Pool. I had to group on State so the report will only list the states for a particular Pool. The example I referenced is just what I am using to test the report with.

Group 1 Header (State)
Group 2 - Header (NoteID)
Details
Group 1 Footer
Group 2 Footer
{#Count} {#BalancePerState)
Report Footer
{#TotalCount} {#TotalBalance)

{#BalanceperState} - Sum Balance, evaluate on change of Group #2 NoteID and reset on Group #1 State.
{#TotalBalance} - Sum Balance, evaluate on change of Group #2 Note ID and reset never.

I hope that explains a little better what I am trying to do. In order to make my formula to calculate the percentage work I just need to figure out a way to get the total to work on the State Group.

 

[lbass]

What you need to do then is run a subreport in your report header. In the subreport, add the Note ID field and {table.balance}. Do not add the state field. Then you can right click on and insert a grand total on {table.balance}. Create a formula in the subreport to share this grand total:

whileprintingrecords;
shared numbervar grtot := sum({table.balance});

This formula must be placed on the subreport canvas. You can suppress all sections of the subreport, but you cannot suppress the main report header.

Then in the main report, create a formula to be placed in the state group footer:

whileprintingrecords;
shared numbervar grtot;

{#BalancePerState} % grtot;

Then just click on the percent sign in the toolbar to display the correct percent.

-LB

 

[mflahive]

I'm sorry, I thought that I had put that this is a subreport but I didn't. I didn't mean for you to do more work than you had to.

 

[lbass]

You can still use this solution. Place the subreport that calculates the total in the report header of the main report, and then reference the shared variable within the second subreport.

-LB

 

[mflahive]

Thank you very much LB! It works great!
M