Converting UserResponse()

system · July 20, 2011, 5:06am

Hi BOBians,

I have a multi select prompt.
And have LOVs like…

1 - Delhi
20 - Jaipur
302 - Bangalore
33 - Hyderabad
987 - Pune

So, when I am displaying Prompt selection using UserResponse() in report. It displays like…

1 - Delhi;302 - Jaipur;33 - Hyderabad…

Is there any way using which I can convert this into… 1;302;33…

I just want to display numbers available in that string.

I tried to make my requirement clear, still if you need any more information, please update the thread.

TIA.

Rakesh_K (BOB member since 2007-12-11)

system · July 20, 2011, 6:51am

Use Pos function to find the position of “-” and then Substring or left function to fetch the required data.

You can try & let me know if it is not working , I will try to replicate the same at my end …

aniketp (BOB member since 2007-10-05)

system · July 20, 2011, 2:21pm

Hi,

Suppose the object you have is Dim. Using the following code, you can show them in a table.

=Substr([Dim];1;Pos([Dim];"-")-1))

Lets work to show it in a cell.

M H Mohammed (BOB member since 2010-06-16)

system · July 25, 2011, 10:48am

Thanks Aniket,

It is a multi select prompt. So a user can select N number of values. How can i create a variable using POS() for such cases.

Rakesh_K (BOB member since 2007-12-11)

system · July 25, 2011, 4:55pm

Do you have separate object with just Id’s, if yes then you can bring it in the report and show it in horizontal table with out borders.

Thanks,
Zaif

zaif235 (BOB member since 2010-06-15)

system · July 25, 2011, 5:06pm

I think Cognos is wiser than BO on this. Try searching B B to find a topic where somebody has asked a Name field to be shown in a prompt where as internally it should use the ID field.

Jansi (BOB member since 2008-05-12)

system · July 26, 2011, 7:23am

Hi Rakesh, did you try the forula , what error or message you are getting?

aniketp (BOB member since 2007-10-05)

system · July 26, 2011, 11:15am

Hi,

@Aniket,

how i can create a formula. Because sometimes user can select 1 value and some times more than 1.

Value of the UserResponse() will vary.

for example:

1 - Hyderabad;2-Bangalore
or
3 - Jaipur;5 - Pune;6 - Delhi

Can you please try on your end.

@Jansi,
Please go through the post again, I am not looking for Index Awareness.

Let me explain this issue again.

I have two columns in database. location id and cityname.

Then I created a object called City = location id || ’ - ’ || cityname

Now I am using this object for my LOV.

So my LOV looks like…

1 - Hyderabad
2 - Delhi
3 - Jaipur
4 - Pune
and so on…

This prompt is a MULTI select prompt. So in UserResponse() i am getting string like “1 - Hyderabad;2 - Delhi;…”

I want to display this as “1;2…”

Just want to display locationids in a single cell.

Rakesh_K (BOB member since 2007-12-11)

system · July 26, 2011, 11:21am

Rakesh, if you simply take the Location Id to display won’t you get desired results.

Suppose you are selecting 4 values in your concatenated object and only those 4 location ids will appear in location id column, now if you display only location id object, you should able to achieve what you are looking for.

Now if you need that the output should be in order like 1;2;3;4 , then refer the below post-

aniketp (BOB member since 2007-10-05)

system · July 27, 2011, 1:31am

Hi,
Do a replace of Upper of userresponse()
Formula Would be something like below
=replace(replace(upper(userresponse());“A”;"");“B”;"")

Keep writing the replace for all the alphabets.

Hope it works for you.

prabhu_pramod (BOB member since 2010-06-17)

system · July 27, 2011, 6:03am

Thanks Aniket for sharing informative link, but this is not a clean solution in this case. As I am not displaying id column separately in report.

@Prabhu

What about special characters.

Rakesh_K (BOB member since 2007-12-11)

system · July 27, 2011, 3:44pm

U can do a replace for any character you want. Once you replace all the characters you dont need , your results will be what you want.

prabhu_pramod (BOB member since 2010-06-17)

system · July 28, 2011, 6:03am

@prabhu

If i ll includes all the characters from all the languages then the formula will become pages long.

Is this you are suggesting?

Rakesh_K (BOB member since 2007-12-11)

system · July 29, 2011, 1:25am

There is no easy solution. But you can build a lot of little variables and then join them together.

This will get the first value

var1 =Substr([Dim];1;Pos([Dim];"-")-1))

and then you can build more variables to get others

var2 =Substr([Dim];length(Var1);Pos([Dim];"-")-1))
var3 =Substr([Dim];length(Var2);Pos([Dim];"-")-1))
etc etc

The you can …

FinalVar = var1+var2+var3

Problem is that you will have to guess how many variables to build.

weaver (BOB member since 2008-10-17)

system · July 29, 2011, 5:07am

There are around 7000 values in the list.

Rakesh_K (BOB member since 2007-12-11)

system · August 1, 2011, 11:38pm

But no one will choose 7000 items via the prompt. You can guess a maximum of 20 and make 20 variables.

Furthermore a prompt of 7000 items is hardly user friendly to begin with.

weaver (BOB member since 2008-10-17)

system · August 3, 2011, 5:23am

By assuming 20 selection from list will be a limitation.
Prompt is user friendly.

Some business users are familiar with id and some are interested in names. Also they can make use of search option to filter out values from LOV.

I want to pass only id to drill report as passing complete cityname will increase the URL length.

Also there is a limitation of 2083 characters for URL.

Rakesh_K (BOB member since 2007-12-11)

system · August 4, 2011, 1:44am

If all you are doing is wanting to pass the values then perhaps a second data provider?

Have your normal query with its prompts.

In your second data provider you just have

Select ID
FROM MyTable
WHERE ID IN MyPrompt

You can then display the returned rows in a Horizontal table formatted to look like a single cell.

weaver (BOB member since 2008-10-17)

system · August 4, 2011, 4:36am

I tried that.

I am able to display all ids into single cell.

like 1;2;3…

But I am not able to pass this into my drilldown report.

Rakesh_K (BOB member since 2007-12-11)

system · August 8, 2011, 3:48am

Try creating an object at the Universe level?

I use that technique to make rows of comments into one single comment.

This is an example from a SQL site

SELECT CustomerID, (SELECT OrderID AS ‘data()’ FROM Order O WHERE O.CustomerID = C.Customer ID FOR XML PATH(’’)) AS OrderList FROM Customer C

weaver (BOB member since 2008-10-17)