One-to-many data set with running totals

[kwalters135]

I am using CR XI full version with a dbase database. I am trying to produce subtotal fields that are based on a one (single account record) to many (8 different balances for the different scenarios) relationship.

GD is the master table, RBD is the detail table.
GD.ACCT links to RBD.ACCT. Each account has 8 records in RBD that are determined by RBD.SCENARIO (numeric field, values 0 to 7).

By grouping on RBD.SCENARIO, I have one detail section in crystal that contains the fields that I need from RBD. For example RBD.NII_M1 (net interest income, month 1). This successfully produces 8 detail sections for each account.

However, the only way I have been able to get 8 different subtotals is to create 8 different group footer sections with 8 different running total fields that use a formula to determine when to evaluate a record based on the RBD.SCENARIO field.

Is there any way I can set up a single running total field in the design view that will produce the correct subtotal for each scenario?

A few more details:

GD.ACCT --(one to many)--> RBD.ACCT

Group #1: RBD.ACCT
Group #2: RBD.SCENARIO
DETAIL: many fields from RBD

 

[IdoMillet]

It's not clear why you need to group at level 2 if there is only 1 record in each such group.

You didn't explain what the subtotals are, at what level, and location (GF2, GF1, ...?).

It would help if you explain & show the desired output.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[kwalters135]

Here is a rough sketch of my design view, G=Group.

G1: @fake group (This is a placeholder group for a crosstab that enables virtual pages.)
G2: GD.ASS_LIB (Asset/Liability field)
G3: GD.Category (Account category field)
G4: GD.Acct (Account number)
G5: RBD.SCENARIO (Account scenario field, 7 per acct., 2 are skipped per record selection formula)
Detail: RBD.NII_M1 RBD.NIC_M1 (net interest income, and net interest change)
Group Footer #3a GD.Category: @subtotal_title (category name)
GF #3b thru #3h on GD.Category: Here is where I have placed my subtotal fields for RBD.NII_M1 and RBD.NIC_M1. 3b contains subtotals for RBD.Scenario = 0, 3c for RBD.Scenario=1, etc. They are running total fields with a formula ({RBD.Scenario=x}) that determines which fields to evaluate.

Sample output:

Acct#1 Retail Demand Account {GH #4 section}

+300bp 1000 10 {detail section}
+200bp 1050 60 {detail section}
+100bp 1100 70 {detail section}
Const 1200 80 {detail section}
-100bp 1300 90 {detail section}
-200bp 800 (20) {detail section}
-300bp 700 (120) {detail section}

Total Non-Maturing Deposits {GF #3a}
+300bp 11000 110 {GF #3b}
+200bp 12050 360 {GF #3c}
+100bp 11500 760 {GF #3d}
Const 12000 870 {GF #3e}
-100bp 13400 990 {GF #3f}
-200bp 8200 (520) {GF #3g}
-300bp 7030 (1520) {GF #3h}

So on the actual report there would be several accounts listed, and then a subtotal section for each category that lists the totals for RBD.NII_M1 and RBD.NIC_M1.

This gives me the output I need, however the time required to design the report is astronomical. What is listed is a small sample of the report. I have twelve months of data per scenario, plus a percentage change formula for each month.

So what I would like to be able to do is figure out an easier way to obtain the subtotals. In design view, I have one detail section that produces 7 detail sections on the actual report for the scenarios that I'm looking for. But the subtotals require a group footer section for each scenario.

Thanks for any help.