Re: SAP BW Query to cognos

[fml55555]

Thanks a ton for all the help! I promoted the package and am able to access all the details. however, I have a new problem and it is related to report studio. I do not know whether I should be posting it here, but here it goes.

There are 4 query objects (in different regular dimensions). Each of them could have different value.

e.g.


QO1      QO2    QO3   QO4

1           AA        AKL   1JK7
2           BB        MMM  22OO
3           CC       ALF     72U1
4           DD        NJU    WWDD


Based on the data, some of these combinations, belong to certain group

for e.g. (1  AA AKL 1JK7) and (4,DD,NJU,WWDD) might belong to one group

I am trying to extract the value of a perticular measure based on these values. I am using the implicit if filtering techniques e.g

FILTER1
(QO1 = 4 AND QO2 = DD AND QO3 IN (NJU,BJU,MML) AND QO4 = WWDD)



OR

(QO1 = 1 AND QO2 = AA AND QO3 IN (AKL,BKL,CKL) AND QO4 = 1JK7)



However, Cognos throws up errors during validation

such as

"RSV-VAL-0010. Failed to load report specification"
"XQE-PLN-0026 Filter with OR conditions"

Once again thank you for all your help MFGF

[MFGF]

Hi,

I'd strongly recommend you steer clear of filters against any dimensional source - including SAP/BW.

You could perhaps do this with tuple() expressions? Tuples give you the values at intersections of different members.

eg tuple([1 member],[DD member],[WWD member],[your measure])

then perhaps use this in an aggregate() summary to get the aggregated measure value for the three members in QO3

eg aggregate(tuple([1 member],[DD member],[WWD member],[your measure]) within set set([NJU member],[BJU member],[MML member]))

Cheers!

MF.

[fml55555]

Thank you for the reply. I may not have been entirely clear about my objectives in the initial post. What I want to achieve is a bar chart where the bar represents the sum of the various groups that I mentioned. I tried using your technique as follows

I created a tuple for each group in query explorer

Tuple 1
Tuple2
Tuple3

I dragged one of the tuple1 to the filter window (if there is there any other method to apply the "aggregate" function, I would try that too) and the when the filter window popped up I clicked on the "aggregate" function and dragged the other two tuples into it. The validation of the same gave a syntax error.

What am I doing wrong and please let me know if I have not made myself clear about the requirement I working on.

Once again thank you very much for all the help!

One more thing...back when I was using the OR expression the filter...I kept getting the error message as

"The query cannot be supported as unprojected hierarchies ...."

[MFGF]

I dragged one of the tuple1 to the filter window...

Nooooo!!! Don't use detail filters with a dimensional source!

You could either create a query calculation that sums the tuples, or you could use a member() function to convert each tuple to a member then use a dimensional aggregate() summary for this set of members - eg aggregate(<measure value? within set set(<member1>,<member2>,<member3>)

I have split this from your original topic in the FM forum and moved it here as this now relates to Report Studio not FM.

Cheers!

MF.

[fml55555]

Thank you very much for the reply
I tried your approaches but am facing the following problems

1. You could either create a query calculation that sums the tuples

It seems some of the combinations that I mentioned early on do not result in numeric data (I guess they populate Null). This is giving me error messages while I use the above method. Also there is a lot of tuples that I need to create for the any one combination as each tuple allows one and only one value for each column. I have a requirement where mutiple values for one column can be considered as one group (e.g. column tttno. can have values from 1 to 10 for a group).

2. The member function gives me an error message when I try to enter the tuple. It works only with hierarchies or levels.

[MFGF]

Thank you very much for the reply
I tried your approaches but am facing the following problems

1. You could either create a query calculation that sums the tuples

It seems some of the combinations that I mentioned early on do not result in numeric data (I guess they populate Null). This is giving me error messages while I use the above method. Also there is a lot of tuples that I need to create for the any one combination as each tuple allows one and only one value for each column. I have a requirement where mutiple values for one column can be considered as one group (e.g. column tttno. can have values from 1 to 10 for a group).

2. The member function gives me an error message when I try to enter the tuple. It works only with hierarchies or levels.

It feels a lot to me like you're having to fight against the design of the cube to get what you need here? Is there an option to get the structure changed in BW to group the members in the way you require?

MF.

[fml55555]

Hi MFGF,

I finally understood your suggestion (and tweaked it a little) to create partial sum.

To refresh, I am giving you all details

The formula I want to create is in three parts

Part 1

Member1 (Belongs to Dimension 1)
Has a single value 'A' (e.g.)

Member2 (Belongs to Dimension 2)
Can have one of the four values (1,2,3,4)

Member 3 (Belongs to Dimension 3)
Has a single value 111

Member 4 (Belongs to Dimension 4)
Can have one of the many date (year) values (>= 1993)

I used your idea of

total(tuple([Amount],[111],[A])  within set set ([].[].[1993],[][].[1994] etc...)

but to get the other values of the dimension 2 onboard, I encased the above  formula in another total

Total(total(tuple([Amount],[111],[A])  within set set ([].[].[1993],[][].[1994] etc...) within set set ([][].[1],[].[].[2]...))                                       ---------------------------------(*)
It seems to be working as I get the right sum
I created a similar data item for the same members (however with different combination of values)
(lets denote the formula as **)
I then tried encasing both in member()formula so that the data objects look as following
Dataobject1
Member((*),premiumone,premiumonecaption,<dimensionone>)
Dataobject2
Member((**),premiumtwo,premiumtwocaption,<dimensionone>)
(dimensionone dimension does not exist)
Then I tried using aggregate function to sumup these two data objects in a third data object but it gives me a syntax error. I am still working on it. Thank you for all your suggestions and I hope you don't feel too frustrated, this is my first time on OLAP so I have much to learn

[fml55555]

This is how I am currently using the member formula

Member (Total (..............), premone, captionone, hierarchy name)

I have the following questions
1.   I am assuming that the premone is the member name and captionname, am I right?
2.   Does the hierarchy have to be already created in the framework manager ?(i.e. does it have to be an existing hierarchy or
        will cognos create one if it does not exist).

As I said I have used the above formula in two different data items to create two sums. Now I want to sum up the two data items. Am I on the right track?