Totals multiplicated

building reports desktopintelligence
1

Hello!
I must be missing something (my mind e.g. …). I have a report with structure like:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 14

group1 g1id2 subid121 4 3
group1 g1id2 subid122 5 3
group1 TOTAL for id2 9 6

TOTAL for group1 14 20

group2 g2id1 subid211 2 4
group2 g2id1 subid212 6 4
group2 TOTAL for id1 8 8

group2 g2id2 subid221 4 6
group2 g2id2 subid222 3 6
group2 TOTAL for id2 7 12

TOTAL for group2 15 20

GRAND TOTAL 29 40

…which is quite a natural behavior.
The problem is, that AMT2 depends only on ID, not on SUBID and is simply repeated for each SUBID. So, what I really need is:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 7 <-- difference

group1 g1id2 subid121 4 3
group1 g1id2 subid122 5 3
group1 TOTAL for id2 9 3 <-- difference

TOTAL for group1 14 10 <-- difference

group2 g2id1 subid211 2 4
group2 g2id1 subid212 6 4
group2 TOTAL for id1 8 4 <-- difference

group2 g2id2 subid221 4 6
group2 g2id2 subid222 3 6
group2 TOTAL for id2 7 6 <-- difference

TOTAL for group2 15 10 <-- difference

GRAND TOTAL 29 20 <-- difference

Well, it must a well-known problem,. but I really need a hint. Of course, it’s not a problem to have it not summed up on ID level, the problem is, that afterwards it should be summed up on every higher level (i.e. GROUP level an GRAND TOTAL)… Whichever way I have tried, it has summed up every entry on SUBID level, so that it looked like:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 7

group1 g1id2 subid121 4 3
group1 g1id2 subid122 5 3
group1 TOTAL for id2 9 3

TOTAL for group1 14 20 <-- difference

group2 g2id1 subid211 2 4
group2 g2id1 subid212 6 4
group2 TOTAL for id1 8 4

group2 g2id2 subid221 4 6
group2 g2id2 subid222 3 6
group2 TOTAL for id2 7 6

TOTAL for group2 15 20 <-- difference

GRAND TOTAL 29 40 <-- difference

Help!!!

Ryszard Mikke

PS. BO 4.1.2a on NT if that matters…

–==> Hiroshima’45 Tschernobyl’86 Windows’95 <==-- R.Mikke@pl.vwfsag.de

Listserv Archives (BOB member since 2002-06-25)

2

One way to do is, create two queries and link them.

Create first query with:
GROUP, ID, AMT2

Create second query with:
GROUP, ID, SUBID, AMT1

Automatically these two queries will be linked on GROUP and ID. (If not do it manually. Go to data view. Double click on GROUP in DP1, select GROUP
in DP2 and so on)

Now create a report, with GROUP, ID, SUBID, AMT1, AMT2.

–Vasan

From: Mikke, Ryszard

Hello!
I must be missing something (my mind e.g. …). I have a report with structure like:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 14

group1 g1id2 subid121 4 3
group1 g1id2 subid122 5 3
group1 TOTAL for id2 9 6

TOTAL for group1 14 20

group2 g2id1 subid211 2 4
group2 g2id1 subid212 6 4
group2 TOTAL for id1 8 8

group2 g2id2 subid221 4 6
group2 g2id2 subid222 3 6
group2 TOTAL for id2 7 12

TOTAL for group2 15 20

GRAND TOTAL 29 40

…which is quite a natural behavior.
The problem is, that AMT2 depends only on ID, not on SUBID and is simply repeated for each SUBID. So, what I really need is:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 7 ← difference

group1 g1id2 subid121 4 3
group1 g1id2 subid122 5 3
group1 TOTAL for id2 9 3 ← difference

TOTAL for group1 14 10 ← difference

group2 g2id1 subid211 2 4
group2 g2id1 subid212 6 4
group2 TOTAL for id1 8 4 ← difference

group2 g2id2 subid221 4 6
group2 g2id2 subid222 3 6
group2 TOTAL for id2 7 6 ← difference

TOTAL for group2 15 10 ← difference

GRAND TOTAL 29 20 ← difference

Well, it must a well-known problem,. but I really need a hint. Of course, it’s not a problem to have it not summed up on ID level, the problem is, that afterwards it should be summed up on every higher level (i.e. GROUP level an GRAND TOTAL)… Whichever way I have tried, it has summed up every entry on SUBID level, so that it looked like:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 7

group1 g1id2 subid121 4 3
group1 g1id2 subid122 5 3
group1 TOTAL for id2 9 3

TOTAL for group1 14 20 ← difference

group2 g2id1 subid211 2 4
group2 g2id1 subid212 6 4
group2 TOTAL for id1 8 4

group2 g2id2 subid221 4 6
group2 g2id2 subid222 3 6
group2 TOTAL for id2 7 6

TOTAL for group2 15 20 ← difference

GRAND TOTAL 29 40 ← difference

Help!!!

Ryszard Mikke

PS. BO 4.1.2a on NT if that matters…

–==> Hiroshima’45 Tschernobyl’86 Windows’95 <==-- R.Mikke@pl.vwfsag.de

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (9am-5pm ET only): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

3

“Mikke, Ryszard” wrote:

Hello!
I must be missing something (my mind e.g. …). I have a report with structure like:

GROUP ID SUBID AMT1 AMT2
group1 g1id1 subid111 2 7
group1 g1id1 subid112 3 7
group1 TOTAL for id1 5 14
…

If you HAD defined your universe correctly (i.e. right contexts, etc.) then BusinessObjects would do this for you automatically! This means, it would create two “synchronized” queries, because of the “knowledge” that the two measures have different level of detail (driving dimensions) and, use this information in the calculations within the reports automatically…

Walter

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna, Austria Tel. +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)