I'm wondering about a design requirement here, any suggestions and all suggestions are helpful. I apologize for the length of the question, as I couldn't find a better way to make it any shorter.
I was handed over an excel sheet with a lot of formulae and asked to create a BW report. I've successfully got all the required data into BW infocube and now to implement record based excel formula I'm struggling.
For example, one such key figure which I need to achieve is ORDER COUNT :
Maintenance Item Call Number Order Count
090 10 0
100 20 1
100 30 1
100 30 0
200 40 1
Here Order count is being calculated using the below logic in excel:
If maintenance item of the present row = maintenance item of the previous row.
If call number of the present row = call number of the previous row.
Order Count = 0.
Order Count = 1.
order count = 1
Based on the above results I need to create another report where I've to summarize the order count for every workcenter across every month, which looks something like below
01.2013 02.2013 03.2013 04.2013 ........
Work Center Order count Order count Order Count Order Count
WC1 25 36 12 10
WC2 33 42 55 16.....
So if I set up a document count on Maintenance item, I get only the unique maintenance item count in the report output but will miss on the counts of Call number being different within the maintenance item.
I've created a workbook to calculate the order count, using excel formula, but a workbook cannot be a data provider to the report where I need the display as total number of order counts.
I've thought of implementing a self load on the target infocube and implementing the logic of order count and other keyfigures in transformation and loading the target everyday, but the data is huge and everyday full load to run through will have a big performance impact. And full load is a must to compare each maintenance item to the previously loaded one.
I'm not sure if I can make use of APDs here, if yes then kindly guide me on the best approach.
In short: I need formula to be applied on the result set and then this result set be an input to the next query.
Can this be achieved in the BEx level only, and then use that report result in turn as an input to the next report.
I understand the way SCN works so will be generous in rewarding points for the helpful answers 😊.