Skip to Content
avatar image
Former Member

selected values in report

Hi,

We have  Material hierarchy variable (PRO_VAR) in BEx query I want to display selected product key values in report.

Below is the product data

Product

Key

Biscuits

XYBISCUITS

Diary Milk

XYDIARYMIL

Choc

XYCHOC

I am using below formula to display its key values (in single cell)

=Replace("XY"+upper(UserResponse("PRO_VAR"));";";";XY")


It is displaying XYBISCUITS;XYDIARYMILK;XYCHOC


But i am expecting all key values like below (nospaces and restrict to 8 chars)


XYBISCUITS;XYDIARYMIL;XYCHOC


Could you please help me on this logic please..!!

Thank you,

suri

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 19, 2015 at 03:40 PM

    Hi Suri Babu,

    Question: What is the maximum number of values that you/users may select in the prompt?

    I don't know if someone else posted this logic. I'm trying to get the values selected in different variables. I just started that part, and eventually when I have all the values, I would extract the first 8 characters only and concat with XY in the beginning and finally concat all values together.

    This can help you get started, variables I created:

    v_UR = UserReponse("Enter Values:")

    (this gives you the whole User response string as you already know, UR in naming convention means UserReponse)

    v_URvalue1 =Substr([v_UR];1;Pos([v_UR];";")-1)

    (this gives you the first selected value)

    v_URforvalue2 =Substr([v_UR];Pos([v_UR];[v_URvalue1])+Length([v_URvalue1])+1;Length([v_UR]))

    (this will give the remaining Userreponse string after taking out value 1)

    And from that v_URforvalue2, we can calculate v_URvalue2, second selected value as below

    v_URvalue2 =Substr([v_URforvalue2];1;Pos([v_URforvalue2];";")-1)

    And so on, hope you got the idea. Obviously this is not the cleanest solution. That's why SAP needs to update the Pos() function so it helps us to find the position of nth occurrence of a character/string in a string. I'll check if there is any SAP Idea about that.


    If you are selecting more than 3/4 values in the prompt, this may not be the solution you want to use.

    Let's see what the Experts have to say. 😊


    @Experts: Please chime in.

    Hope that helps.

    Thanks,

    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2015 at 04:10 PM

    Hi,

    If Product list is static means not very long and you know the product values then you can use match function to replace values with if else condition.

    =if(match(Replace("XY"+upper(UserResponse("PRO_VAR"));";";";XY");"*XYDIARYMILK*")) then"XYDIARYMIL" else Replace("XY"+upper(UserResponse("PRO_VAR"));";";";XY")


    Amit

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2015 at 04:10 PM

    Hi Suri Babu,

    Looks like I completely missed something, I don't know how UserReponse works with BEx queries, if I just want to use the formula you created to get the first 8 characters of the Product names, then it would be

    =Replace("XY"+left(Replace(upper(UserResponse("PRO_VAR"));" ";"");8);";";";XY")


    I just added the text you see in bold. Try that and let me know what happens.


    That inner Replace() is going to get rid of spaces in the names and the Left() is going to get you the first 8 characters.


    Thanks,

    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Suri Babu,

      This is going to be difficult, you'll have to tell us what is the MAXIMUM number of values that you/users select when the report is refreshed? The more the number of values, the more the variables including the complexity.

      Your formula I updated previous won't work.

      In the below formula I'm approaching this a different way, but again, we hit the same question of how many values will be selected because of the limitations of Pos() function to not include number of occurrence in the syntax and calculate accordingly.

      ="XY"+Replace(Replace(Upper("PRO VAR");";";";XY");" ";"")

      This does everything except getting the first 8 characters of a selected values. Let's see if someone can leverage this and come up with something to help you.

      If the values selected are few, then go with my first response of creating many variables.

      Thanks,

      Mahboob Mohammed