We often create Text/Formula Variables with Replacement Path on Characteristic value in BEx Query Designer. Not sure how many of you have tried Characteristic Variable with Replacement Path on Query?
While working for TOP-N Vendor Report I encountered the need of Replacement with Query, and just thought to share the solution with all the SDNers.
We already have Report to Calculate TOP-5 Vendors of the Year, and recently I got new requirement to update this report for analyzing the TOP-5 Vendors of the year over all the months of the given year. I tried simple approach by creating multiple selections for all the months individually in Columns, and created condition of TOP-5 for all the months. But it didnt work in the way I was expecting. It included TOP-5 Vendors of each month individually, which added some new vendors to analyze in my report. Then I tried the same solution with the Replacement Path on TOP-5 Vendor Query (which I was already having in my system), and finally it worked fine for me.
Below is the detailed explanation of the scenario that I discussed and the problem I faced with the alternative approach, and how it solved with the approach of Replacement Path on Query:
- Running TOP-5 Vendor Report already exist in the system:
- Now I need to Analyzing these TOP-5 Vendors over last 12 months (01/2006 to 12/2006). For this I created new report with 12 selections restricted by each month. For testing I have restricted it directly with (01/2006, 02/2006 .12/2006), but we can also replace it with Dynamic Variable based selection.
- Created Condition of TOP-5 for each selection ( Months).
- The output of the newly created report is having additional Vendors that were not in the TOP-5 Vendor Report. As Condition is based on each month, month-N may be having some Vendors that are in the TOP list for that month, but not for overall year. But my requirement is to analyze only those months that are TOP Vendors of overall year.
- Here is the approach which I tried by Creating Variable on Vendor Characteristic with Replacement Path on TOP-5 Vendor Query.
- Included Calendar Year/Month Characteristic in Columns.
- After running the newly created query we can see Category, and Calendar Year Variables on the selection screen even though they are not the part of Query Definition. As we are using TOP-5 Query as Replacement Path, its taking all the variables of that query in the selection screen of the newly created query.
- Finally I got the Output as per my Requirement. (You can verify the amount total from the TOP-5 Vendor Report Diagram of step1)