Business Objects Insights from Dataspace

December 2008 e-newsletter

tip: USING THE USER RESPONSE AND REPLACE FUNCTIONS TO FORMAT YOUR REPORT
When you include prompts in your Web Intelligence report, you are making your report dynamic so that each time it is run, you can retrieve the data you need to see at that time without modifying the query. Did you know that you can use the User Response function to capture the value(s) you select and then include that in a report title so that you can easily know what data the report includes?

In this example, we will use the User Response function to capture the values for a date prompt and also to capture the values for status. We will then use the Replace function and format the responses in a more user friendly format.

Our report has the following prompts:

The syntax for the User Response function is:
=UserResponse("name of data provider";"prompt message")

NOTE:  The "name of the data provider" parameter is optional.

We will create the following variables:
Name:  UserResponse Date
=UserResponse("Enter Date:")

Name:  UserResponse States
=UserResponse("Select State(s):")

NOTE:  The prompt message must be typed exactly the way it is displayed to the user, including any punctuation and capitalization.

Okay, we can now include these variables in our report title as shown below:

Each time the report is refreshed, the values selected will be displayed in the cells.
However, notice how the state values are separated by semi-colons and the date also has a timestamp.  Let's use the Replace function to format these variables to be more user friendly.

The syntax of the Replace function is:
=Replace([variable to replace something in];"text to replace"; "replacement text")

In our new variable for the state response we will replace the semi-colons with commas:
Name:  Replace State Semi-colons
Syntax:  =Replace([UserResponse States]; ";"; ",")

NOTE:  the parameters are separated by a semi-colon (;) and we are also replacing the semi-colon in the user response

We will also create a variable that will replace the timestamp in our date with nothing
Name:  Replace TimeStamp
Syntax:  =Replace([UserResponse Date];" 12:00:00 AM"; "")

NOTE:  Our timestamps are always "12:00:00 AM"

Now, let’s replace the variables in our report title with the new ones:

Founded in 1994, Dataspace is a consulting and solutions firm focused on the flawless execution of Business Intelligence and Data Warehousing initiatives. Visit www.dataspace.com.