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.

[Former Member]

Thaanks Anshu,

This means all work I have done on BEx to get the latest recoed will not work on BO. I will need to find a solution for this on BO then.

[Former Member]

Hi Sujit & Anshu,

First of all thanks a lot for your help. I have solved the issue, but in a slightly different way. I have listed the steps below.

1. I added a new field 'Max Date'  and populated it with the maximum of the 3 from dates in the routine.(comparing three variables in BEx proved to be very complicated as opposed to 2)

2. Created a 'Max From Date'  Formula Variable and converted the 'Max Date' char to a Key Figure.

3. Added a Condition on the 'Max From Date' key Figure   to selext the TOP N as 1. And then the 'Characteristic Assignemt' I found the correct combination with a lot of trial and error.  Initially I only assigned  'Installation' , but then this was still retuning 2 records, so I added the 'Max Date' characteristic which returned a single record.

Once again thanks a lot for your help Sujit, I learnt quite a lot about BEx through this.

I have only one more question, if you have used BO, do you think I still have to have the 'Installation' and 'Max Date' available in the universe for this to work correctly or can I use 'Query Stripping' to remove these fields as the report should be summarised on 'Supplier' and not be broken down into 'Installation'

Thanks.

[Former Member]

Hi Divahar,

I have only worked on BI 4...where universe is not mandatory...so have consumed BW queries using BICS only. Hence can't help you much on this. Post your query in BO forums and people will be able to help you quickly.

Regards,
Sujit.

[anshu_lilhori]

Hi,

Refer this documnet:

http://scn.sap.com/docs/DOC-3544

Read the 4th point.It talks about your issue.

Regards,

AL

[anshu_lilhori]

Hi,

I can think of some solution but still apprehensive about the approach i am going to give.

You can make use of Replacement path variable and you will replace it with variable.

Make a user input variable on any of the dates.Now create variables on other dates with the help of replacement path with variable.Give the variable name of the first variable so the same value will be passed in all the other date variables.

You need to give range like greater than Equal to the value in from part and less than equal to in To part.

Separate variables will be there for from and to values.

Give a try to this solution.Lot of hit and try and R&D needs to be done to achieve it.

But in the end its worth trying something like that and this is how you learn new things and tricks.

Hope this helps.

Regards,

AL