In this article, by David Lai and Xavier Hacking, authors of SAP BusinessObjects Dasboards 4.1 Cookbook, we will provide developers with recipes on interactivity and look and feel of the dashboards, which will improve the dashboard user experience. We will cover the following recipes:

• Using the Hierarchical Table
• Inputting data values
• Displaying alerts on a map
• Changing the look of a chart

(For more resources related to this topic, see here.)

An important strength that SAP BusinessObjects Dashboards has is the amount of control it allows a developer to provide the user with. This leads to totally customized dashboards, which give users the interactivity that guides them to make the right business decisions. It is important that developers know what type of interactive tools are available so that they can utilize the power of these tools.

With the right interactivity, users can retrieve information more quickly and efficiently.

Using the Hierarchical Table

The Hierarchical Table is a powerful component that was introduced in SAP BusinessObjects Dashboards 4.0 FP3. It allows users to connect to either a BEx query connection or an OLAP universe and take advantage of its hierarchical display and multi-selection capability.

Before the Hierarchical Table was introduced, there was no way to accomplish native hierarchical display and selection without significant workarounds.

Although the Hierarchical Table component is extremely powerful, please note that it can only be used with either a BEx query or an OLAP universe. It will not work on a universe based on a relational database.

Getting ready

Before you can take advantage of the Hierarchical Table component, you must have an OLAP universe or a BEx query connection available. In our example, we create a simple cube from the Adventureworks data warehouse, which is easily accessible from MSDN.

You can download the Adventureworks data warehouse available at http://msftdbprodsamples.codeplex.com/releases/view/105902.

To set up a simple cube, please follow the instructions available at http://www.accelebrate.com/library/tutorials/ssas-2008.

To set up an OLAP connection to the cube, please follow the instructions available at http://wiki.scn.sap.com/wiki/display/BOBJ/Setting+up+OLAP+Microsoft+Analysis+Service+through+an+XMLA+connection+with+SSO.

Finally, you will have to set up an OLAP universe that connects to the OLAP connection. Instructions for this can be found at http://scn.sap.com/docs/DOC-22026.

How to do it…

1. Create an OLAP universe query / BEx query from the Query Browser.

   

2. From the Components window, go to the Selectors category and drag a Hierarchical Table component onto the dashboard canvas.

   

3. Click on the Bind to Query Data button and choose the query that you created in step 1. Next, choose the dimensions and measures that you want displayed on the Hierarchical Table. By default, you must select at least one hierarchy dimension.

   

4. Click on the Configure Columns button below the data binding to adjust the column widths on the Hierarchical Table. We do this because by default, SAP BusinessObjects Dashboards does not set the column widths very well when we first bind the data.
5. On the Appearance tab, edit the number formats for each measure appropriately. For example, you can set dollar amounts as the currency with two decimal places.
6. Next, we want to capture rows that are selected during runtime. To do this, click on the Insertion tab. For the Insertion Type, you have the option of Value or Row.

   

7. For the Value insertion option, you must choose an option for Source Data, which is one of the columns in the Hierarchical Table.
8. In our example, we will choose the Insertion Type as Row, which grabs values from all the columns. We'll need to bind the output destination. We will assume that a user can select a maximum of 30 rows. So we'll bind the output to a 30 x 3 destination range.

   

9. Bind a spreadsheet table object to the destination output from step 8 to prove that our selection works.

   

10. Finally, test the Hierarchical Table by entering preview mode. In the following screenshot, you can see that we can expand/collapse our Hierarchical Table, as well as make multiple selections!

    

How it works...

As you can see, the Hierarchical Table selector is a very useful component because before this component was available, we were unable to perform any form of hierarchical analysis as well as simple multi-selection. The component achieves hierarchical capabilities by taking advantage of the OLAP cube engine.

There's more…

Unfortunately, the Hierarchical Table selector is only available from cube sources and not a traditional data warehouse table, because it uses the OLAP cube engine to do the processing.

The hierarchical capability, in our opinion, is doable with data warehouse tables as other tools allow this. So hopefully, SAP will one day upgrade the Hierarchical Table selector so that it works with your traditional data warehouse universe based on tables.

Inputting data values

The ability to input values into the dashboard is a very useful feature. In the following example, we have a sales forecast that changes according to an inputted number value. If we were to use a slider component for the input value, it would be more difficult for the user to select their desired input value. Another good example could be a search box to find a value on a selector which has over 100 items. This way you don't need to hunt for your value. Instead, you can just type it in.

In this recipe, we will create an input textbox to control a what-if scenario.

Getting ready

Create a chart with its values bound to cells that will be controlled by the input textbox value. The following is an example of a sales forecast chart and its cells that are controlled by the what-if scenario:

You may refer to the source file Inputting data values.xlf from the code bundle to retrieve the pre-populated data from the preceding image if you don't want to manually type everything in yourself.

Unlock access to the largest independent learning library in Tech for FREE!

Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.

Renews at $15.99/month. Cancel anytime

START FREE TRIAL

How to do it...

1. Drag an Input Text object from the Text section of the Components window onto the canvas.

   

2. In the properties window of the Input Text component, bind the Link to Cell as well as Destination to cell D3 from the Getting ready section.

   

3. Go to the Behavior icon of the input text properties and make sure Treat All Input As Text is unchecked.

   

4. The blue cell D6 from the Getting ready section that's labeled as valid value will check to make sure the input text entered by the user is valid. To do this, we use the following formula:

   =IF(ISNUMBER(D3),IF(AND(D3>=-20,D3<=20),D3,"INVALID"),"INVALID")

   The formula checks to make sure that the cell contains a number that is between -20 and 20.

   Now every cell in the chart binding destination will depend on D6. The binding destination cells will not add the D6 value if D6 is "INVALID". In addition, a pop up will appear saying "Input is invalid" if D6 is "INVALID".

   

5. Create the pop up by dragging a Label text component onto the canvas with Input is invalid as its text. Next, go to the behavior tab and for dynamic visibility, bind it to D6 and set the Key as INVALID.

   

How it works...

In this example, we use an input value textbox to control the forecast bars on the chart. If we type 20, it will add 20 to each value in the forecast. If we type -20, it will subtract 20 from each value in the forecast.

We also add a check in step 4 that determines whether the value entered is valid or not; hence the use of Excel formulas. If a value is invalid, we want to output an error to the user so that they are aware that they entered an invalid value.

Displaying alerts on a map

A map on a dashboard allows us to visually identify how different regions are doing using a picture instead of a table or chart. With alerts on the map, we can provide even more value. For example, look at the following screenshot. We can see that different regions of the map can be colored differently depending on their value. This allows users to identify at a glance whether a region is doing well or poorly.

Getting ready

Insert a Canadian map object into the canvas and bind data to the map.

You may also refer to the data prepared in the source file, Displaying alerts on a map.xlf.

How to do it...

1. In a separate area of the spreadsheet (highlighted in yellow), set up the threshold values. Assume that all provinces have the same threshold.

   

2. Go to the Alerts section of the map properties and check Enable Alerts.
3. Select the radio button By Value.
4. In the Alert Thresholds section, check Use a Range. Then, bind the range to the Threshold dataset in step 1.
5. In the Color Order section, select the radio button High values are good.

   

How it works...

In this recipe, we show how to set up alerting for a map component. The way we set it up is pretty standard from steps 2 through 5. Once the alerting mechanism is set up, each province in the map will have its value associated with the alert threshold that we set up in step 1. The province will be colored red if the sales value is less than the yellow threshold. The province will be colored yellow if the sales value is greater than or equal to the yellow threshold but less than the green threshold. The province will be colored green if the sales value is greater than or equal to the green threshold.

Changing the look of a chart

This recipe will explain how to change the look of a chart. Particularly, it will go through each tab in the appearance icon of the chart properties. We will then make modifications and see the resulting changes.

Getting ready

Insert a chart object into the canvas. Prepare some data and bind it to the chart.

How to do it...

1. Click on the chart object on the canvas/object properties window to go to chart properties.
2. In the Layout tab, uncheck Show Chart Background.

   

3. In the Series tab, click on the colored box under Fill to change the color of the bar to your desired color.
4. Then change the width of each bar; click on the Marker Size area and change it to 35.

   

5. Click on the colored boxes circled in red in the Axes tab and choose dark blue as the Line Color for the horizontal and vertical axes separately.
6. Uncheck Show Minor Gridlines to remove all the horizontal lines in between each of the major gridlines.

   

7. Next, go to the Text and Color tabs, where you can make changes to all the different text areas of the chart, as shown in the following screenshot:

   

How it works...

As you can see, the default chart looks plain and the bars are skinny so it's harder to visualize things. It is a good idea to remove the chart background if there is one so that the chart blends in better. In addition, the changes to the chart colors and text provide additional aesthetics that help improve the look of the chart.

Summary

In this article, we learned various recipes on how to make interactive dashboards including using Hierarchical Table and alerts. Using such techniques greatly improve the look and feel of the dashboards and help create great presentations.

Resources for Article:

Further resources on this subject:

• Creating Our First Universe [article]
• Report Data Filtering [article]
• End User Transactions [article]