Skip to Content

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 didn’t 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:

  1. Running TOP-5 Vendor Report already exist in the system:

    image

    image
    TOP-5 Vendors of the Category ‘FACILITIES’ for the year 2006 on the basis of Amount.

  2. 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.

    image

  3. Created Condition of TOP-5 for each selection ( Months).

    image

  4. 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.

    image

  5. Here is the approach which I tried by Creating Variable on Vendor Characteristic with Replacement Path on TOP-5 Vendor Query.

    image

  6. Included Calendar Year/Month Characteristic in Columns.

    image

  7. 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.

    image

  8. Finally I got the Output as per my Requirement. (You can verify the amount total from the TOP-5 Vendor Report Diagram of step1)

    image

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Anonymous
    In your first query, you could create a column for the entire year and apply the condition to that column. If you dont want to see the year column, you can hide it.

    Another option, would be to store the Top 5 query results as a value set. Replacement path using query results is nice to use when performance is not paramount.

    There are many ways to achieve the same results, thanks for sharing yours!

    (0) 
    1. Kamaljeet Kharbanda Post author
      The first query which i created is the column on year only, but how to dispaly it month wise? Could you elobrate, as I already tried many possible combinations.

      About value sets, i agree. I didn’t mentioned in my blog, as the link which i have given as URL of my blog highlightling it already.

      ->There are many ways to achieve the same results
      I would love to hear other alternatives. Could you provide some more here?

      Thanks,
      @KJ…

      (0) 
      1. Anonymous
        Make a structure with 13 columns. One for each month, and one for the year. Then apply the condition to the year column.
        (0) 
    2. Ivi Torres
      I have a problem, creates a project in the CMOD, and now the queries do not want to me to load, but this happens for the queries created in the version 2004s support package 0, in version 3x they are executed normally. The error that appears is the following one “TIME_OUT” in “Form pers_write”, especificamente in the instruction modify /BI0/APERS_BOD00 from table l_t_data of the reporte “GP392Z0Y41C6O4K6ZQQ17W775PS”

      thanks…..

      (0) 
      1. Mark Finnern
        Please Ivi,

        For most of us here helping people out on SDN and BPX is a side job.

        Consequence is, that there is no Urgent and explanation marks especially 4 in a row are frowned upon.

        What you ask belongs into our forums, where you can give good answers points to your helpful fellow community members.

        Please do. You may have done that already. I hope you got your answer and are beyond the panic mode by now.

        Good luck, Mark.

        (0) 
  2. zubin lalani
    Hi there,
    I havent tried it but is very impressive. My query is:
    If the Top 5 Vendors is used to feed the other report(which runs in the background) and when you run the report for vendors evaluation by month, wouldnt the selection screen for Top 5 vendors will ask you to fill the selection screen.
    (0) 
  3. Beena Glenu
    Hi Kamaljeet,

    I see that the prequery requires manual entry , So if we are running only the final query do we enter it manually and the variable value passes to the pre-query?

    Another question ..I need to create a prequery on a multicube so that it returns all the customers from the cube(char).But when I run the
    query it does not bring any results , but if I add a key figure , I can see the data.So my question is whether it is possible to have a query with only one char value for output.

    Thanks,
    Beena

    (0) 
  4. poonam bhutani
    can u let me know how to calculate the total no. of employees in a particular personnel area in the cube 0py_c02 where in the rows,the wage type occurs?
    This is really urgent,plz help!!!
    (0) 
  5. Igor Pylypenko
    Hi Kamaljeet,
    Thank you for the smooth and clear explanation how you did resolve the problem. It is really nice.
    My question is – is this real requirements? As I understand, you filter data twice – by TOP 5 (for each month separately!) and by Repalcament Path. So, I suspect that Vendor 10000289  , for instance, had some results for 01/2006; but your approach cut it off.
    Is this correct? I am not sure…
    The approach of John Kurgan is very usual for me…
    (0) 

Leave a Reply