How to Manipulate Result Set in BEx/BO

[Former Member]

Hi,

I want to know if there was anyway to manupulate the result set returned in a BEx query. For my input I am gettting the following   4 records(I have just shown 1 installation as example. There could be  more than one 'Installation' in the result set)

Installation	Supplier	Rate Cat	Supp Valid From	SuppValid To	Stat Valid From	Stat Valid to	Rate Cat Valid From	Rate Cat Valid To	De Energised Count	Energised Count
30000969	AAA	T031	01.01.1950	12.05.2012	01.02.2012	31.12.9999	01.01.1950	31.12.9999	0	1
30000969	BBB	T031	13.05.2012	31.12.9999	01.02.2012	31.12.9999	01.01.1950	05.08.2012	0	1
30000969	BBB	T035	13.05.2012	31.12.9999	01.02.2012	15.08.2012	06.08.2012	31.12.9999	0	1
30000969	BBB	T035	13.05.2012	31.12.9999	16.08.2012	31.12.9999	06.08.2012	31.12.9999	1	0

If the user runs the report from from 01.08.2012 - 31.08.2012 compare all three FROM dates(Supp Valid From,Stat Valid From,Rate Cat Valid From) and get the latest FROM date for that month, for that particular installaion and then display that record. In my case this would be the record with the 'STAT Valid From' =16.08.2012( the Max of the 3 FROM dates for the month of August,2012)

30000969

BBB

T035

13.05.2012

31.12.9999

16.08.2012

31.12.9999

06.08.2012

31.12.9999

1

0

This check has to be done for all 'Installations' returned. Is there any temporary storage location where BEx stores the resulting dataset wherin I could go and manipulate this data? Or Is there any way I could do this in BO. Im using BOXI Webi v3.1 and use BEx query as universe. Is there anyway I can write a sub query to filter the results in BEx or BO? Any help is much appreciated. It is an usrgent requirement.

Thanks in advance.

Divahar

[Former Member · Accepted solution]

Hi Divahar,

This is possible using formula variables and conditions. There can be other ways as well.

1. Use formula variables to convert your dates into key figures

2. Use these key figures to find out the highest or latest date. For this you need to create a calculated key figure and use < or > logic to find the latest date. This depends on you how you want to calculate. You can take the difference between dates to calculate which one is greater.

3. Now create a condition on this column. Use "Top N" as 1.

4. In this condition "Characteristics Assignment" tab select "Supplier" and "Rate Cat"

You should get your desired output.

[Former Member · Accepted solution]

Hi Sujit,

Thanks for your reply, iIthink it helped me head in the direction towards the solution. But having converted the 3 'FROM' dates into Key Figures using formula, The issues I am facing are lsited below

1.I tried to create a 'Calculated Key Figure' to compute the greatestof the 3 FROM dates, but I couldnt find the converted FROM dates under 'Key Figures' in the 'Available Operands' area. I could only find the formula variables I created.

2. Since BEx doesnt support  'IF' condition, it is porving extremely difficult to compare 3 key figures and get the greates ouyt of the three.

Any help/suggestion would be helpful as this is proving to be a very tricky problem to solve.

Thanks for your help so far.

[anshu_lilhori · Accepted solution]

Hi,

Bex support if else conditions.

The only thing is we cannot read string values in it.

Please search about it,lot of threads and documents are available on the same.

Regards,
AL

[Former Member · Accepted solution]

Hi Divahar,

1. You can only find the Formula Variables you have created. But this formula variable will contain the value of the char that you want to add for each row of the report. So this should be enough.

2. Bex supports if else condition.

SAP Help link

http://help.sap.com/saphelp_nw04/helpdata/en/23/17f13a2f160f28e10000000a114084/content.htm

Refer to page 12 of the below document as well.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d08b56a8-daf5-2e10-2397-904d6aeb5...

Regards,

Sujit.