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. 😎
Hi all.
I am interested in the reverse of this, though not from a user’s response. I have some text areas in my database where users input data and separate each line with a hard return. So using your example, this is what they enter:
Porsche
Lotus
Ferrari
Aston Martin
This is all in one field, and I’d like to remove the carriage return and list all of the data on a single line with a comma between. For example: Porsche, Lotus, Ferrari, Aston Martin. I can handle the concatenation, but what I can’t seem to do is remove the carriage return.
WEBI doesn’t see a carriage return, and there’s no trailing space after each line. The entire field is seen as one long line. Counting characters doesn’t seem to work because the length of each line varies.
Any ideas?
Thanks,
Lane
Hi, Lane, there is perhaps a newline (character 10) rather than a carriage return (character 13) in your data? What I would try to do to research this is find out if your database offers any sort of “raw” or “dump” function that will let you see the hexadecimal equivalent of your data string. That should show you what unprintable characters are present in the data that are causing it to appear on a new line. For example, in Oracle the RAWTOHEX() function will take raw data and output in hexadecimal format. You would then be able to see if there are 10 (0x0A) or 13 (0x0D) values mixed in your text.
Hi Dave,
I have a requirement something similar to the one above. I have Universe object like “Notes” that has few new line characters in its data. Reason due to the data is taken from a user entered tool text box. While extracting the report in CSV format, due to that field each record is coming in different lines. Can you suggest probable solution through webi for getting data as one line per each record?
Thanks
You could replace the newline characters with a variable, or do it in a universe object if that works better. It would basically be the opposite of what is done here where I am adding newline characters into the string.
Hi,
I’ve a requirement to pass the user reponse values as filter to the other dimensions.
Ex:
User response (“Account Group”) will result A;B;C
Dim1
A
B
C
D
E
Now I want to filter the Dim1 values by the Userresponse result values.
Could you please provide assistance on this.
Thanks,
Srinivas.
I have certainly done this in the past. You need to use a combination of string functions. First, note that the prompt responses are A;B;C where the final response “C” does not have a ; after it, and the initial response “A” does not have a ; in front of it. That means the first step is to concatenate a ; in front of and behind the string so you end up with ;A;B;C;. Why is this important?
Because the next step is to check to see if your dimension value with a ; in front of and behind it will match the prompt string.
Basically you take your dimension [Dim] and concatenate a ; on both ends so you end up with “;” + [Dim] + “;” and then you see if the resulting string is contained in the prompt string, and if so you return a 1 else you return a 0. Finally you can filter on the rows that are 1.
I did this a long time ago in one of my “Variables” presentations, and I never have managed to get around to posting all of them here. While I have not provided the exact syntax of the answer, hopefully you can work with what I have provided. 😎
Hello sir i am new to webi and i am having a problem in a webi report. The sample report layout is something like that
Customer: Island Trading Total:
City: Contact:
OrderID Salespersion Order Date Req Date Ship Date Ship Via
ProductID Product Name Quantity Unit Price Discount Price
order and product informmation is in seperate lines , I acheived that design but i cant manage to avoid duplicate customer name for multiple order block. So i created a seperate table and put it over the order block bbut multi value is showing instead of the value. I know it is happening because other table means other block . Iam using section. Please tell me how do i avoid multiple customer block woth out applying other table.
the thing is we have multiple orders under single customer name . thts why i put another table to avoid duplicate customer name but when i am printing the sum in that table it is giving me error of multi value . I know it is happening because other table means other block but thaat was the only thing i knew to avoid multiple custmer name in multiple orders block . I used section and break both to get my required solution.
thanks. worked like a charm. landed on this site through google search. came up in first batch. I’ve bookmarked you now.