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.