selected values in report

[Former Member]

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

[mhmohammed]

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

[Former Member]

Hi Amith,

values are not static.... We have big list and user can select any combination.

Hi Mahboob,

it is displaying XYBISCUI

[mhmohammed]

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

[amitrathi239]

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

[mhmohammed]

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