Method for Creating an Object Prompt in BusinessObjects

On a recent project, there was a requirement to create a BusinessObjects Xcelsius dashboard that had a number of bar charts for which the one of the chart axes would be selected by the user. For example, the user had the option to display in the y-axis either the time period, product category, or location. Because of technical issues, we were constrained to using Query as a Web Service (QaaWS) for our data connection. Typically, we would tackle such a requirement by creating a different QaaWS connection for each different attributes that the chart would need to display. However, this can quickly become unmanageable as the different attribute combinations increased. An alternative approach could be to create the BusinessObjects Universe based on stored procedures and have the stored procedures handle the logic of applying the correct attribute based on the user selection.  This was not pursued since we wanted the Universe to be usable outside of the dashboard. Finally, we decided to create an object that encapsulates a prompt and then using this prompt object in another object that would select the appropriate attribute based on the prompt result. In the world of a BusinessObjects competitor, Microstrategy, such an object is referred to as an Object Prompt and we will use this terminology for the rest of this discussion.

The following instructions outline the steps needed to create an Object Prompt that can be used by Xcelsius through a QaaWS connection. We’ll be using the Island Resort Marketing sample Universe for these instructions.

1. In Universe Designer, create a Prompt object. The syntax for the object would be similar to the following. Note that the object may not parse correctly even though it is still usable. In order for the object to parse correctly, a table association will need to be made. However, the table associated to the object prompt should only return one record in order to prevent duplicate results.

@Prompt('Attribute?','A',{'month','category','resort'},mono,constrained)

Note that we have hard coded the list of values for the attributes that we want the user to be able to choose.

2. Create an Attribute utilizing the Prompt object created in the previous step. In our example, the object would have the following SELECT syntax:

CASE WHEN @Variable('Attribute Prompt') = 'month' THEN @Select(Sales\Month)   
     WHEN @Variable('Attribute Prompt') = 'region' THEN @Select(Customer\Region)
     WHEN @Variable('Attribute Prompt') = 'resort' THEN @Select(Resort\Resort)
END

This CASE statement basically selects the attribute that will be utilized depending on the prompt value.

3. Define a QaaWS connection utilizing the objects created. Your QaaWS connection would have the attribute you created in Step 2 included as well as any measures that you want on your dashboard.

4. In Xcelsius, add the QaaWS connection that was defined in Step 3. You’ll also need to add one Selector component and one visualization component. The Selector component will pass a parameter to the Prompt Object indicating which Attribute the user wants to display. In our example the possible values that the Selector component would pass would be ‘month’, ‘category’, or ‘resort’.

Several caveats are applicable to this technique:

• The solution described is based on a data source that is ANSI SQL compliant. The syntax described would not work if your data source is, for example, Microsoft Access.
• Since the SQL generated when using the Object Prompt is dynamic because of the CASE statement, there may be a performance impact mainly from the fact that the query plan likely cannot be cached.
• The solution described will not work with measures that utilize of the Aggregate Awareness (@Aggregate_Aware).