Substr() help!!

building reports webintelligence
1

Hello All,
I Need some help with Substr() function. I am on BoXi 3.1 SP5 and the below scenario is in a webi doc.

I have to capture user responses for a prompt and display them on the report header. This prompt can take multi values and I have to show the substrings of the chosen prompts. All prompts will have same string at the beginning and I have to get rid of that part

For example:
If the chosen prompts are Report_abcd, Report_xy, then I have to display abcd;xy in the report header.

I used substr() function, but it is being applied just for the first prompt., and giving output as abcd;Report_xy
How to make substr() apply for the second prompt as well?

Thanks,
Bob

bob96 (BOB member since 2012-02-02)

2

Hi,

How about using the Replace() function then? Something like:

=Replace([your object];"Report_";"")

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

if you have two prompts you can get the value for each one at a time by using the userresponse() function. However, you’d have to substring or use replace as suggested above around each userresponse. if you got the first one OK then just do the same thing on the second one.

erik.stenson :us: (BOB member since 2012-07-30)

4

Marek,

Thanks for the reply. I created a variable with your suggestion and it’s giving multivalue as there is more than one prompt. How to tackle this?

Thanks,
Bob

bob96 (BOB member since 2012-02-02)

5

could you provide the formula that you’re using in your variable?

erik.stenson :us: (BOB member since 2012-07-30)

6

Hi Erik,

Below is my original variable:

=Substr(UserResponse(“Enter prompt:”);7;Length(UserResponse(“Enter prompt:”)))

This prompt can take multi values and if the prompts chosen are Report_abcd;Report_xyz

The result I got is “abcd;Report_xyz”

Thanks,
Bob

bob96 (BOB member since 2012-02-02)

7

are you saying that you have the same prompt text but have two different values depending on the data provider? I know that you could also use as an input the dataprovider (query) name before the prompt text to be more specific. Usually what I do is something like this:

userresponse(dataprovider([selectobjectfromquery]);“promptTextHere”)

Hope that helps

erik.stenson :us: (BOB member since 2012-07-30)

8

No.

My report has only 1 dataprovider and 1 prompt. this prompt has a LOV and user can choose more than 1 value from LOV.

bob96 (BOB member since 2012-02-02)

9

Yes, but the result of the UserResponse() function is a single string that contains all values selected in the prompt. So the Replace() function applied on the result of the UserResponse() function should work.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

10

Got it!! Thanks Marek!!

bob96 (BOB member since 2012-02-02)

11

You’re welcome 8)

And, maybe you can post your final formula so it could help someone in future.

Thanks.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

12

Yes Marek, of course…

=Replace(UserResponse(“Enter prompt:”);”Report_”;” “)

Thanks,
Bob[/img]

bob96 (BOB member since 2012-02-02)