incompatable combination of objects

building reports desktopintelligence
1

Im using BO V4.1 and trying to get the report as detailed below. However I am having difficulty in getting the results.

I have a table that has the following data in:

E_DATE MAIN_MW CHECK_MW TRANS
=============== ======= ======== =====
10-JUN-98 10:00 623.00 622.00 GT1
10-JUN-98 10:00 620.00 621.00 GT2
10-JUN-98 10:30 624.30 623.60 GT1
10-JUN-98 10:30 621.00 621.50 GT2
10-JUN-98 11:00 623.50 621.60 GT1
10-JUN-98 11:00 621.00 621.00 GT2
.
.

What I what is to be able to produce a report that looks like:

Date Tran1 Main Tran2 Main Tran1 Check Tran2 Check

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

2

What I what is to be able to produce a report that looks like:

Date Tran1 Main Tran2 Main Tran1 Check Tran2 Check
10-JUN-98 10:30 624.30 621.00 623.60 621.50
10-JUN-98 11:00 623.50 621.00 621.60 621.00
.
.

I have done something similar but my solution may not be very elegant.

In the Universe for this Table, I would create dummy objects with the value zero, for example D1, D2 etc…

I would then created a union of 2 queries. The first would select Date, TM1, D1, TC1, D2 for ‘GT1’ the second would select Date, D1, TM1, D2, TC1 for ‘GT2’

This when Grouped by Date will give the result you are looking for.

My real life example was to do variance of Totals for 2 different dates where the same Table contained all the data for all dates. I had to show the Total for the first date, Total for the second Date and then the variance.

Mope this helps.

Mohan
(212)250-8105
p.s.mohan@bankerstrust.com

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

3

You wrote:

Im using BO V4.1 and trying to get the report as detailed below. However I am having difficulty in getting the results. I have a table that has the following data in: E_DATE MAIN_MW CHECK_MW TRANS
=============== ======= ======== =====
10-JUN-98 10:00 623.00 622.00 GT1
10-JUN-98 10:00 620.00 621.00 GT2
10-JUN-98 10:30 624.30 623.60 GT1
10-JUN-98 10:30 621.00 621.50 GT2
10-JUN-98 11:00 623.50 621.60 GT1
10-JUN-98 11:00 621.00 621.00 GT2
.
.

What I what is to be able to produce a report that looks like: Date Tran1 Main Tran2 Main Tran1 Check Tran2 Check

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

4

Thank you Glenn,

This one also helps me. I was always doing this with

Var1=Sum(if =“GT1” then <MAIN_MW>)

Your solution is much more readable. The Where you’ve put there, is it documented? If so, I seem to be missing a path to find the correct information. I can’t find it when I search the help for the Sum function.

Peter

PS

Glenn Fredericks Glenn_Fredericks@AAL.ORG 19/06/98 18:21:38 >>>

What you can try is creating variables based on MAIN_MW and CHECK_MW. Try defining the following varaibles

Var1 =Sum(<MAIN_MW>) Where (=“GT1”) Var2 =Sum(<MAIN_MW>) Where (=“GT2”) Var3 =Sum(<CHECK_MW>) Where (=“GT1”) Var4 =Sum(<CHECK_MW>) Where (=“GT2”)

Place these varables in your table along with the date and it should give you what you are looking for.

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

5

Peter Notenbaert wrote:

Thank you Glenn,

This one also helps me. I was always doing this with

Var1=Sum(if =“GT1” then <MAIN_MW>)

Your solution is much more readable. The Where you’ve put there, is it documented? If so, I seem to be missing a path to find the correct information. I can’t find it when I search the help for the Sum function.

All the information on additional formula parts, like IN/FORALL/FOREACH/WHERE … is available both in th on-line help and since V4.1.2 in the manuals, esp. the “advance calculations and troubleshooting guide”…

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)

6

I can find the information on IN/FORALL/FOREACH in the on-line help but not on the WHERE part. I’m using V4.1

Walter Muellner w.muellner@DELPHI.AT 22/06/98 12:19:15 >>>
Peter Notenbaert wrote:

Thank you Glenn,

This one also helps me. I was always doing this with

Var1=Sum(if =“GT1” then <MAIN_MW>)

Your solution is much more readable. The Where you’ve put there, is it documented? If so, I seem to be missing a path to find the correct information. I can’t find it when I search the help for the Sum function.

All the information on additional formula parts, like IN/FORALL/FOREACH/WHERE … is available both in th on-line help and since V4.1.2 in the manuals, esp. the “advance calculations and troubleshooting guide”…

Walter

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

7

you wrote:

Your solution is much more readable. The Where you’ve put there, is it documented? If so, I seem to be missing a path to find the correct information. I can’t find it when I search the help for the Sum function.

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

8

Well,

actually it doesn’t seems to help me.
I have created a report where

Var1=Sum(if = “x” then ) is giving the correct result and where
Var1=Sum() Where ( = “x”) is giving other (incorrect) results.

In fact I really can’t figur out how BO gets to the result it shows. If I put the variable in a break footer it even gives me different results when the ‘body’ is folded or not. Or I am missing something essentialy, or this must be a bug. Did someone else had simular problems? (…)
I tried a little more. Now I ame sure it is a bug. I have now two reports (created by duplicate report) in my document, exactly the same definition, but with the sum-where var giving different results in the two reports. Both wrong!

Peter

Peter Notenbaert peter.notenbaert@AQUAFIN.BE 22/06/98 9:36:58 >>>
Thank you Glenn,

This one also helps me. I was always doing this with

Var1=Sum(if =“GT1” then <MAIN_MW>)

Your solution is much more readable. The Where you’ve put there, is it documented? If so, I seem to be missing a path to find the correct information. I can’t find it when I search the help for the Sum function.

Peter

PS

Glenn Fredericks Glenn_Fredericks@AAL.ORG 19/06/98 18:21:38 >>>

What you can try is creating variables based on MAIN_MW and CHECK_MW. Try defining the following varaibles

Var1 =Sum(<MAIN_MW>) Where (=“GT1”) Var2 =Sum(<MAIN_MW>) Where (=“GT2”) Var3 =Sum(<CHECK_MW>) Where (=“GT1”) Var4 =Sum(<CHECK_MW>) Where (=“GT2”)

Place these varables in your table along with the date and it should give you what you are looking for.

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

9

Peter Notenbaert wrote:

Well,

actually it doesn’t seems to help me.
I have created a report where

Var1=Sum(if = “x” then ) is giving the correct result and where
Var1=Sum() Where ( = “x”) is giving other (incorrect) results.

In fact I really can’t figur out how BO gets to the result it shows. If I put the variable in a break footer it even gives me different results when the ‘body’ is folded or not. Or I am missing something essentialy, or this must be a bug. Did someone else had simular problems? (…)
I tried a little more. Now I ame sure it is a bug. I have now two reports (created by duplicate report) in my document, exactly the same definition, but with the sum-where var giving different results in the two reports. Both wrong!

Peter

I do not know what you did exactly, BUT there IS a difference between the two versions!!!

The IF…THEN…ELSE gives you the value on a “by row” basis. In your case, Var1 will give you the value of ‘var2’ only for those ‘records’ which have field1=‘x’ (what happens otherwise?), and, this formula is ‘aggregation enabled’ which means you can use this formula in the headin/footing area of a tabular block and it will be aggregated according to the computation driving dimensions. This also means, that grouping the data on ‘field1’, will give you the (summarized) values of ‘field2’ for the value of Var1 only on the group where field1=‘x’.

The WHEN formula uses relational database projection mechanism and also is aggregation enabled. This means, that you will get the (aggregated) value of ‘field2’ ALSO for the other groups of data, which do NOT have field1=‘x’!

Hope this helps…
Walter.

DI 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)

10

I hope you don’t mind I’m going further on this topic, but I don’t understand the difference between the two. Walter if you can find the time, can you say what the Sum-where formula should give me in the following example.

The table:
X Y Z
a m 1
a n 2
a m 3
b n 4
b n 5
b m 6
c m 7

The header detail report :
X=a
Y Z
m 1
n 2
m 3
Sum(if =“m” then ) = 4; Sum() where ( = “m”) = ?

X=b
Y Z
n 4
n 5
m 6
Sum(if =“m” then ) = 6; Sum() where ( = “m”) = ?

X=c
Y Z
m 7
Sum(if =“m” then ) = null; Sum() where ( = “m”) = ?

Altough the purpose of this mail for me is to have a better understanding of the projection mechanism you talked about, I want to repeat BO gives incorrect results. I think you will have a hard time to convince me that it is not a bug, when in a report the same function (in this case the sum-where) gives a different result after I folded and then again unfolded the report, because that is exactly what I did.

Peter

Walter Muellner w.muellner@DELPHI.AT 24/06/98 20:00:50 >>>
Peter Notenbaert wrote:

Well,

Var1=Sum(if = “x” then ) is giving the correct result and where
Var1=Sum() Where ( = “x”) is giving other (incorrect) results.

I do not know what you did exactly, BUT there IS a difference between the two versions!!!

The IF…THEN…ELSE gives you the value on a “by row” basis. In your case, Var1 will give you the value of ‘var2’ only for those ‘records’ which have field1=‘x’ (what happens otherwise?), and, this formula is ‘aggregation enabled’ which means you can use this formula in the headin/footing area of a tabular block and it will be aggregated according to the computation driving dimensions. This also means, that grouping the data on ‘field1’, will give you the (summarized) values of ‘field2’ for the value of Var1 only on the group where field1=‘x’.

The WHEN formula uses relational database projection mechanism and also is aggregation enabled. This means, that you will get the (aggregated) value of ‘field2’ ALSO for the other groups of data, which do NOT have field1=‘x’!

Hope this helps…
Walter.

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

11

Sorry, my mistake
I’ve seen the light,

Actualy it is the the difference between :

Sum() where ( = “m”)

and

Sum( where ( = “m”)) this is the one I need

Thanks to all who responded to my mails, I’ve already learned a lot from your reply’s. Peter

Peter Notenbaert peter.notenbaert@AQUAFIN.BE 25/06/98 10:54:01 >>>
I hope you don’t mind I’m going further on this topic, but I don’t understand the difference between the two. Walter if you can find the time, can you say what the Sum-where formula should give me in the following example.

The table:
X Y Z
a m 1
a n 2
a m 3
b n 4
b n 5
b m 6
c m 7

The header detail report :
X=a
Y Z
m 1
n 2
m 3
Sum(if =“m” then ) = 4; Sum() where ( = “m”) = ?

…

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

12

Peter Notenbaert wrote:

I hope you don’t mind I’m going further on this topic, but I don’t understand the difference between the two. Walter if you can find the time, can you say what the Sum-where formula should give me in the following example.

The table:
X Y Z
a m 1
a n 2
a m 3
b n 4
b n 5
b m 6
c m 7

The header detail report :
X=a
Y Z
m 1
n 2
m 3

The answer should be:Sum(if =“m” then ) = 4; Sum() where ( = “m”) = 4

X=b
Y Z
n 4
n 5
m 6

The answer should be:Sum(if =“m” then ) = 6; Sum() where ( = “m”) = 6

X=c
Y Z
m 7

The answer should be:Sum(if =“m” then ) = 7; Sum() where ( = “m”) =7

Altough the purpose of this mail for me is to have a better understanding of the projection mechanism you talked about, I want to repeat BO gives incorrect results. I think you will have a hard time to convince me that it is not a bug, when in a report the same function (in this case the sum-where) gives a different result after I folded and then again unfolded the report, because that is exactly what I did.

This case above is the less interesting, the MORE interesting is the following: Y=‘m’
X Z Sum() Where ( = “m”)
a 4 4
b 6 6
c 7 7
Y=‘m’, total 17 17

Y=‘n’
X Z Sum() Where ( = “m”)
a 2 4
b 9 6
Y=‘n’, total 11 17

Have fun…

Walter

DI 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)