group summary

rbuser

i have a summary report each top group needs a sum of all its details
which need to sum all its detail (basically no details just the sums).

payor 1:
returnA:
state1: sum(dbField1) sum(dbField2)...
state2: sum(dbField1) sum(dbField2)...
state3: sum(dbField1) sum(dbfield2)...
returnA sum(state..3 dbField1) sum(state1..3 dbField2)...

returnB:
state1: sum(dbField1) sum(dbField2)...
state2: sum(dbField1) sum(dbField2)...
state3: sum(dbField1) sum(dbfield2)...
returnB sum(state..3 dbField1) sum(state1..3 dbField2)...

payor 2:
returnA:
state1: sum(dbField1) sum(dbField2)...
state2: sum(dbField1) sum(dbField2)...
state3: sum(dbField1) sum(dbfield2)...
returnA sum(state..3 dbField1) sum(state1..3 dbField2)...

returnB:
state1: sum(dbField1) sum(dbField2)...
state2: sum(dbField1) sum(dbField2)...
state3: sum(dbField1) sum(dbfield2)...
returnB sum(state..3 dbField1) sum(state1..3 dbField2)...

etc...

on the main report i have a group for the break; and a subreport
for each of the containing a summary band for its detail. i
just haven't been able to group/summarize the detail as needed.

any suggestions?
thanks!
-martha

nicocizik

Hi Martha,

You might try creating a group around each stateX in you subreport and then
using the GroupFooter to sum up the fields. This would require you to order
your dataset based on "state".

--

rbuser

yes that's what i tried. my main report is grouped on the payor; each
subreport (representing each return) is grouped on state and the sum()
in the group footer; each subreport also has a summary band for the all
state's totals. what i get is each record is listed separately and
summed separately regardless if they belong to the same state or not.
i'm still trying all sorts of things to work around this. any other
suggestions or pointers?
thanks!
-martha

nicocizik

Hi Martha,

Are you sure that your detail dataset is sorted on the grouping field? It
looks like the group is breaking on every field which means that for each
traversal ReportBuilder makes on your dataset, there is a new value in the
group field.

--

rbuser

i know it's probably something to do with the sorting that i just
haven't been able to get straight between the master and detail sets.
the master table has the restricted set of records, sorted by 'payor',
'return', 'state'; it uses a key to link to the detail that contains an
unrestricted set of data (which i have attempted various sorting on
'state'; 'payor/state', etc.); the detail is linked to the master on the
'id' key. so what i am attempting is to traverse the master, retrieving
a specific detail record to sum together until the state group changes;
summing these details for the 'return' group changes.

i'm still plugging away on it without much success.
Thanks!
-martha

nicocizik

Hi Martha,

Let me know if I'm wrong here but I'm assuming that the payor and ID fields
are synonymous in the master and detail tables. You might try ordering on
ID, State in your detail dataset. Something like the following?

Master Table
-------------

ID Payor
--- -------
1 Paul
2 John
3 George
4 Ringo

Detail Table
-------------

ID Payor State Amount
--- ------- ------ ---------
1 Paul A $1000
1 Paul A $2000
1 Paul B $1000
2 John A $500
2 John A $600
3 George A $1000
3 George B $1000
3 George B $300
3 Geroge C $200
4 Ringo B $3000
4 Ringo B $5000
4 Ringo C $2500

This would be ordering on ID, State, and therefor would keep the group going
for each alike state for an individual payor.

Hope this helps.

--

rbuser

well in a perfect world... yes there is a key similar to that but not to
the detail set (i just work here ;-) ) over the years things have gotten
somewhat bizarre. this is what i'm working with:

master payor table: master payee table:

detail Master table:
stmtID PayorId PayeeID Return State
1 3 1 A M
2 3 1 A Z
3 3 1 A M
4 2 2 A M
5 3 3 B M
6 3 4 A M
7 3 4 B M

detail Return A
stmtID Amount1 Amount2 Amount3
1 1000.00 2000.00 300.00
2 100.00 30.00 20.00
3 200.00 100.00 25.00
4 100.00 100.00 100.00
6 100.00 20.00 20.00

detail Return B
stmtId Amount1
5 $1000.00
7 $200.00


in the summary report we don't care about the payee just summing return
amounts by state; something like

George
Return A
State M 1300.00 2120.00 345.00 (sum of stmt 1,3,6)

State Z 100.00 30.00 20.00 (sum of stmt 2)
return A 1400.00 2150.00 365.00 (sum of all states)

Return B
State M 1200.00 (sum of stmt 5, 7)
Return B 1200.00 (sum of state M)

John
ReturnA
State M 100.00 100.00 100.00 (sum stmt 4)
ReturnA 100.00 100.00 100.00 (sum of all states)


thanks!
-martha

nicocizik

Hi Martha,

Would it be possible for you to join the two Return tables with the detail
Master table, and order that result set on the PayorID? If so, you would be
all set when you link the Master Payor Table to that dataset or datasets.

--

rbuser

i had thought of something like that but in reality there are 23
individual return details (each have 1 to 20 different amount fields)
tied to the master detail (which is tied to 2 master tables). in our
previous report writer (crystal - boo hiss) this was our slowest report
so i'm trying hard not to have to do too much overhead database querying
/ restrictions on all the details.

i'm trying to step back and re-evaluate the whole approach - which is no
details just lots of sums. got to be a way though just haven't been
able to get there from here!

thanks!
-martha