F...riendly aggregation...

Date: Wed, 17 Jun 1998 18:26:09 +0200

Hello!
I have got a big problem. I have data in Oracle,

which looks like: and in report I have:

ID ACC OP AMT ID ACC C D
1 123 c 10 1 123 10
2 124 d 10 2 124 10
3 123 c 30 3 122 30

…and so on. OP is c for credit and d for debit, in report I have something like =if(OP=“c”,AMT,0) for C column and similarly for D column. I have defined those columns as variables, so as to be able to sum it up later on (you can’t have =sum(if…)). OK, now problems.

First of all I don’t need ID in the report, but if I remove it, then lines with the same ACC, C, and D will become one line (ID uniqely identifies each operation). There is some automatic aggregation which I believe to be behind it. Ah, and there is one important thing - the data provider is freehand SQL, not built from Universe, so please don’t advise me to go to data provider window and mark “Duplicate rows” in options…

Also, all the columns I have made dimensions (with free-hand SQL numbers are automatically measures and are summed up i.e. instead of number got from Oracle, in every row is a sum of numbers in all rows…), so it’s not that problem either…

Problem 2 is even more interesting. I wanted another table, containing sums of C and D for every ACC. So, I have inserted another table, using data existing in the report with columns ACC, =sum©, =sum(D) (C and D are variables, I have defined as described at the beginning of this mail, I have found sums in “Formulas” when I was choosing columns for the new table). And guess, what happened?

If in the first table I had:

ID ACC C D
12 234 10
13 234 10
14 234 10
15 234 10
16 234 10
17 234 10

…then in the “totals” table I had:

ACC Sum© Sum(D)
234 10
^^^^ !!!

Again, I suspect this automatic aggregation… HOW TO GET RID OF IT??? Or at least, how to work around it in my case…

Ryszard Mikke

Sorry if you can’t see the subject. Something breaks my headers. – ==> Hiroshima '45 Tschernobyl '86 Windows '95 <== – R.Mikke@pl.vwfsag.de


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