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.
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.
- Create an OLAP universe query / BEx query from the Query Browser.
Note
For instructions on how to create a query from the Query Browser, please refer to the Using the Query Browser recipe in Chapter 8, Dashboard Data Connectivity.
- From the Components window, go to the Selectors category and drag a Hierarchical Table component onto the dashboard canvas.
- 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.
- 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.
- 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.
- 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.
- For the Value insertion option, you must choose an option for Source Data, which is one of the columns in the Hierarchical Table.
- 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.
- Bind a spreadsheet table object to the destination output from step 8 to prove that our selection works.
- 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!
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.
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.