Dave's Adventures in Business Intelligence

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. 😎