Nov 25 2009
Displaying UserResponse() Values On Separate Rows
I am often asked why I still participate so much on BOB after all of these years. The main reason is I still get inspired by questions that make me think a little bit. The puzzle for today’s post was presented as a question something like this:
How can I display each individual value selected in the UserResponse() function on its own row in a table?
I believe the reason for wanting this behavior is fairly clear. If you have a bunch of complex data it would be much easier for the report consumer to read this:
XYZ123ABC
DEF456HIJ
BOB111BOB
Instead of this:
XYZ123ABC;DEF456HIJ;BOB111BOB
As is often the case, the strict answer to the question is “you can’t do it that way” with Web Intelligence. Despite the “you can’t…” answer I am going to show how it can be done.
Printer History Lesson aka Carriage Return + Line Feed To The Rescue
Before I show my solution, I have a history lesson to share. 
In the old days (and I mean really old, like before laser printers) most business printing was done with impact printers. An impact printer was essentially a typewriter connected to a computer. It would print one character at a time. Some really expensive printers could write an entire line at a time. Some even more expensive ones would be able to write forwards and backwards to save time. Fancy stuff. 
But the point is, everything was characters and lines. If a program needed to print something bold, the printer would back up and print the same characters more than once on the same line. That’s where special printer control characters came in.
The print head or “carriage” was where the next character would be printed. Normally it would flow across the page printing one character at a time. At the end of the line, a carriage return signal was sent. This would tell the printer to send the carriage all the way back to the beginning of the line. If the printer started printing again at that point, it would overwrite the existing line of output. (As mentioned above, that’s one way that bold type was done.) To avoid this, another special printer instruction character was sent just before the printing started: the line feed. This character told the paper to roll the paper forward one line so that the new output would be on the next line.
To recap: carriage return sends the print head back to the left side of the page, and line feed moved the paper forward one line. Carriage return was (and still is) character 13 in the ASCII sequence, and line feed is character 10. (These are also often abbreviated CR and LF.) There are plenty of others. If my memory is correct a tab is character 9 and the “bell” if you wanted the printer to make a noise was character 7. But I digress.
Fast forward to today. The characters still retain some of their meanings, even if we don’t use impact printers anymore. So if I want to push some content down and start on a new line, I can include these characters as part of my output and they will behave the same way. Web Intelligence offers the function Char() which will allow me to introduce a specific character using the proper number. Web Intelligence also offers the Replace() function which will allow me to replace one character (or string) with another character (or string). You might see where all of this is heading…
Prompt Processing
I created a simple query using the Prestige Motors universe. The query includes a prompt that asks the user to select one or more automobile manufacturers when the report is refreshed. The text of the prompt is “Car Makers.” I can easily display the results of the prompt (the values the user selected) on my report using the UserResponse() function. What I want to do is display each selected value on its own line.
How am I going to accomplish this? I’m not going to put each value on an individual row in a table. As I already said that can’t be done. Instead, I’m going to make them show up on different rows in a single cell. The readability should be the same, even if the applied technique is different.
The final piece of the puzzle is to observe just how Web Intelligence captures and displays prompt responses. Here is a screen shot showing the prompt after I have selected four different car makers. Notice that the four values are separated by the semicolon ; character.
With a simple formula I can echo the prompt selections in my report title cell like this:
Notice that the exact same format is used in both the prompt window and the output in the title cell. Each selected value is separated by a semicolon. I can work with this.
The Solution
I created a variable named “Picked Cars” with this formula:
=Replace(UserResponse("Car Makers");";";Char(13)+Char(10))
The inside portion of the formula looks like this:
UserResponse("Car Makers")
That bit captures the selections and returns them in the format shown above, where each car name is separated by a semicolon. The outer portion of the formula uses the Replace() function to find those semicolon characters and replace them with the combination of character 13 (CR) and 10 (LF). The net result?
Everywhere there is a ; it gets replaced by the CR+LF and therefore the output moves down to a new line and back to the left side of the page. Strictly speaking there is no page nor is there any paper to “line feed” but the functionality is the same. Simple enough. Here’s what the results looked like after I created this variable and used it in my header.
What about the formatting? It turns out I didn’t need to do too much. I made sure that my cell was set to automatically fit the content (since it could grow or shrink based on how many cars were selected) using the “Autofit Height” setting as shown here.
The next item I planned to update turned out to be set for me by default. (This example was documented using the Web Intelligence Rich Client so other tools may require this step.) I clicked on the edge of the block and made sure that if the title cell above it grew too big that the block would move down the page. This is done by setting the relative positioning of the block, as shown here.
As I mentioned, this dialog box did not require any changes for this scenario. It was already set the way I needed it to be. Since the block is positioned relative to the cell that contains my [Picked Cars] variable it will move up or down the page based on the size (height in this case) of the cell.
Conclusion
Can I split out each individual user response and display it on a separate row in a table? No. Can I achieve the same visual result using other techniques? Sure. That’s what it’s all about, finding a different way to answer a question. This solution does have one problem… it will not work cleanly if there are semicolon characters in the data returned from the prompt. I did not test this with Desktop Intelligence, but since it includes all of the functions used in this example I am confident that it would work the same way.
I was challenged when I posted this solution on BOB as to why I used both CR and LF characters in my formula. To be honest, when I changed the formula so that it only included one or the other, either seemed to work just fine individually. Despite this, I opted to leave both in my formula.
What can I say… old habits die hard. 
Dave, i’ve noticed that if your data contains semi-colons and you picked them from an LOV you won’t get the item that contained the semi-colons in your result set because it sees the semi-colon as a delimeter. We’ve had to ask our users to refrain from using semi-colons in data that they might need to report on ;/
Hi,
this code is good for Deski reports when you want that all of your prompts will be presented in seperate rows,on under each other:
“First prompt:” & Fill(Char(32) ,10) & Replace(UserResponse (DataProvider() , ” First prompt:”;” ,Char(13) & Fill(Char(32) ,10) ) & Char(13) & Char(13) &” Secound prompt ” & Fill(Char(32) ,10) & Replace(UserResponse (DataProvider() , ” Secound prompt:”) ,”;” ,Char(13) & Fill(Char(32) ,10) ) & Char(13) & Char(13)
I have re-posted the formula since some of it’s syntax (the tagging) didn’t pass well:
“First prompt:” & Fill(Char(32) ,10) & Replace(UserResponse (DataProvider((object )) , ” First prompt:”;” ,Char(13) & Fill(Char(32) ,10) ) & Char(13) & Char(13) &” Secound prompt ” & Fill(Char(32) ,10) & Replace(UserResponse (DataProvider((object)) , ” Secound prompt:”) ,”;” ,Char(13) & Fill(Char(32) ,10) ) & Char(13) & Char(13)
the ((object)) should be with the < signs.
Thanks
yoav
Excellent post Dave! Just what I needed! I have to take it a step further however….do you know of a way to apply a filter in WEBI on the ‘User Response’ values without having to pull back the dimension in the result set?
Thanks!
Hi, DMC, welcome. I’m not sure I understand the question…
Thanks Dave!
Using your example, let’s say I wanted to view the report display by Car Maker = “Lotus”.
Let us also say that I have a set of standard reports that cannot be modified at the query level. Therefore, I cannot add any dimensions to the query. I can only modify the displayed data in the report.
Can a report level filter be applied on the user response selections in the table so that only “Lotus” Invoice Amounts are shown?
Hint – We have not found a way to apply a simple filter on user response data.
So let me see if I understand what you have…
1. There is a report with a user response, for example on Car Maker
2. The report has a field that shows the user response to the prompt
3. The user can also filter on the report
Now what you want is for the user response value to be affected by the filter, is that correct?
Yes, that sounds correct….. Filtering on a user response variable.
I would probably try creating a new table (block) with just that one dimension in it, and then make sure that the filters are “report” filters rather than “block” filters.
I don’t think I’m explaining the issue well.
I guess my question is: Can a report level filter(as opposed to a query filter) be applied only on dimension objects in a report? AND Do you know where are the “=UserResponse()” values stored?
Thanks
Let me redo my explaination of what I’m trying to do and see if that helps, because your last question doesn’t make sense to me. A report filter can be applied to any object on the report, dimension or otherwise. But if you filter on a dimension, then any other values (details, measures, or other dimensions) will be impacted as well. You cannot filter just one column out of a block.
Usually when people put a UserResponse() function on their report it’s to provide documentation. In the example used in this post, it was to echo on the report header the car makers selected via the prompt. That way someone looking at the report knows what car makers to expect. However, a user can also filter the report. So if I had run the report for “Porshe” and “Ferarri” but then filtered the report (as opposed to refreshing the query) to show only Porshe, then the user response calculation would be (or would appear to be) wrong. It would show two car makers when only one was visible on the report.
That’s what I thought you were asking for, a way to filter the user response results based on report filters. I don’t think we can do that. As a result I made my previous suggestion of adding a specially formatted table block to show a unique list of dimensions in the header of the report instead of using the UserResponse() function at all. That way if the user were to use a report filter (rather than a block filter) then the block data would be in sync.
If that’s not what you’re trying to do then we need to start over.
Nice solution to a common problem.
Great tip!!!!!
Resolved my issue in a minute
Excellent tip…
It solved my issue ..
Hi Dave,
Thank for the tip.
I am making a report as a letter.
In this letter I put some text.
I need to emphasize some of the text with underline and bold characters.
How can I do it?
(Where can I find all char (?) codes available for infoview?)
Hi dave,
Can you tell us if it is possible to create somthing like this :
=if([Catégorie(info)] inList(UserResponse(”Sector(s) choise:”));”yes”;”No”)
Kindest regards
Hi, Nidhal, thanks for your question. The UserResponse() function does not return a list, it returns a delimited list of string values. So you cannot use InList to find an item. Instead you can use the Match() function. Suppose you have a list of responses A;B;C. What I do next is combine the ; in front of and at the end of the string, resulting in ;A;B;C; as the next step. Finally, I combine a *; in front of and a ;* behind the value I am looking for. Suppose I am looking for the value D in my list, it would be *;D;* as the string for the Match() function. If that value is found (the * is the wildcard) then I return “yes” else “no” as you have in your formula.
If I look for *;A;* as a match, it will be found.
Does this make sense?
Gabriel, welcome. For your scenario you would not need character codes to change formatting. If you are viewing your report via the web you can try standard HTML formatting codes.
Thank you Dave.
I need to create a PDF document and print it. ( Distribute many letters )
I tried without success.
For example I used ” =+”text”++ Char(10)+”text” ”
(I only a user of the BO infoview not a programmer. I tried the URL
http://www.fund-online.com/alabook/htmldemo/format.htm#other)
I see that I cannot sent the html code I used. but it shows in the bold letters.
Thanks,
Great tip!! was actually looking to build a multi-line address variable and couldn’t recall the syntax for inserting CR/LF between each element
If I want all of the values on one line as shown below…
XYZ123ABC;DEF456HIJ;BOB111BOB
…is there a way to replace the “;” delimiter with a “,”
Hi, Lauren, thanks for your question. All you have to do is use the
Replace()function and substitute the , for the ; instead of the CR/LF combination I showed in the post. This should do it:=Replace(UserResponse("Car Makers");";";",")I have a report with 2 columns based on the data in the first column i need to perform either sum or average for the next column
For example for c1 the aggregation for the next column should be average
c2 the aggregation for the next column should be sum …..so on
I have created a variable v1 for user response value on column1
is there a way where i can mention if v1= c1,c3,c5,c7 then do sum
else average on the column2
Hi, this really doesn’t have anything to do with this post.
Despite that, here’s a thought. Make two variables, one that does a sum() and one that does an average(). Then use an “If” statement to swap out the appropriate variable based on the value of column one.
Sorry dave , i posted in the wrong discussion but i need your advise on this
my requirement is
For example take efashion unv
my requirement is if user selects california or newyork the aggregation on sales revenue should be average and sum on the other two states.but it is taking only the first option for all the states .Is there anything wrong with my formula.
=If(Match(UserResponse(”M:”);”California”) Or Match(UserResponse(”M:”);”New York”)) Then Average(Sales revenue) ElseIf(Match(UserResponse(”M:”);”Massachusetts”) Or Match(UserResponse(”M:”);”Texas”))Then Sum(Sales revenue)
State Sales Revenue
California 7479999
New York 12345
Massachusetts 758456
Texas 10100001
Since this is not related to the topic of this post I would suggest that you please consider posting this on BOB where you will have more eyes and brains working on your question. Please include how you are defining the prompt. Also I am not sure why you are using the Match() function since there is no need for wildcards.
For example take efashion unv
The prompt is on state:
If the user selects
California
New York
Massachusetts
Texas
The user reponse value will be stored as california;new york;massachusetts;texas
My requirement is if for california or newyork the aggregation on sales revenue should be average and sum on the other two states.
Code:
=If(UserResponse(”State:”)=”California” Or UserResponse(”State:”)=”New york” ) Then Average([Sales Revenue]) Else Sum([Sales Revenue])
this formula is not working , iam getting aggregation for sum under sales revenue column as it is taking the else part
Is there something to do user response function because it returns values as california;new york;massachusetts;texas
could you please post the correct formula
State Sales Revenue
California
New York
Massachusetts
Texas
it is really urgent so iam posting here for your advise
Why not use:
=If ( [State] = ”California” Or [State] = “New York” ) Then Average([Sales Revenue]) Else Sum([Sales Revenue])
Discussion continues on BOB here:
http://www.forumtopics.com/busobj/viewtopic.php?t=156198