Find out how to use Xcelsius 2008 connectivity, which allows you to connect directly to your SAP NetWeaver BW system without the need for any middle layers. In addition, the connectivity provides you with functionality and metadata support from your SAP NetWeaver BW queries.

I show you how to use the SAP NetWeaver BW connectivity with Xcelsius 2008 to define a dashboard that displays net sales revenue broken down by product and by calendar month. In addition, the dashboard offers drill-down capabilities that enable the user to see the details for a single product (for 12 months) or for a single month (showing revenue by product).

The SAP NetWeaver BW query you use is based on the MultiProvider 0D_NW_M01 from the SAP NetWeaver demo model. The query contains two characteristics: Product and Cal. year / month as well as a set of key figures (Figure 1). As part of the query make sure you configure the characteristic Product to show the key and description and the characteristic Cal. year / month to show the key only.

Figure 1

SAP NetWeaver BW query

Set Up the Data in the Data Manager

Start the Xcelsius Designer by following menu path Start > Programs > Xcelsius > Xcelsius 2008. Then select Data > Connections and click the Add button. Select the entry SAP NetWeaver BI Connection (Figure 2).

Figure 2

Xcelsius Data Manager

Next, click the Browse button and log on to your SAP system. Select the SAP NetWeaver BW query that you created from the role dialog. Then navigate to the Data Preview tab and move the characteristic Cal. year / month to the Free Characteristics area and make sure the characteristic Product is in the Rows area. Click the Refresh Data Preview button to see a preview of the result set (Figure 3).

Figure 3

Xcelsius data preview with one connection

Navigate back to the Definition tab and enter Data by Product as the name for the connection. Click the Add button in the Data Manager and select SAP NetWeaver BI Connection to create a second connection with your SAP NetWeaver BW system.

Click the Browse button and select the query you used in the previous connection. Navigate to the Data Preview tab and, this time, move the characteristic Product to the Free Characteristic area and move the Cal. year / month characteristic to the Rows area. Click the Refresh Data Preview button to see a preview of the result set (Figure 4).

Figure 4

Xcelsius data preview with two connections

Navigate back to the Definition tab and enter Data by Calendar Month as the name for the connection. Click the Add button and select SAP NetWeaver BI Connection to create a third connection with your SAP NetWeaver BW system. Click the Browse button and select the query you used in the previous connection. Navigate to the Data Preview tab and move the characteristic Product to the Free Characteristics area and the characteristic Cal. Year / month to the Rows area.

Navigate back to the Definition tab and enter Data by Calendar Month (DRILL) as the name for the connection. Click the Add button and select SAP NetWeaver BI Connection to create a fourth connection with your SAP NetWeaver BW system. Click the Browse button and select the query you used in the previous connection. Navigate to the Data Preview tab and move the characteristic Cal. Year / Month to the Free Characteristics area and the characteristic Product to the Rows area. Navigate back to the tab Definition and enter Data by Product (DRILL) as the name for the connection. Close the Data Manager.

Prepare the Spreadsheet and Assign Xcelsius Components

In the following steps you organize your embedded spreadsheet to make it easier to assign the Xcelsius components to specific areas in the embedded spreadsheet.

Navigate to the embedded spreadsheet. Right-click the SHEET 1 tab and insert another three sheets so that you have four sheets available overall. Configure the names for the sheets as follows:

• Sheet 1: Settings
• Sheet 2: Sales by Product
• Sheet 3: Sales by Month

• Sheet 4: Sales by Month (Drill)

Sheet 1: Settings

Enter the following text in the Settings sheet:

• Cell A1: Summarized by:
• Cell A2: Product
• Cell A3: Calendar month
• Cell B2: 1
• Cell B3: 2

Step 1. Drag and drop a radio button component from the Selectors category to the top-left corner of your canvas. Right-click the new components and select the menu item Properties. Click the reference icon  next to Title and set it to cell A1. Then click the reference icon next to Labels and select cells A2 and A3. Set the Insertion Type to Value. Click the reference icon next to Source Data and select cells B2 and B3. Finally, click the reference icon next to Destination and select cells B6.

Step 2. Enter the text Selected value in cell A6.

Step 3. Mark the value B6 with a yellow color. At this point, the dashboard contains two radio buttons that allow you to store the selected option into your embedded spreadsheet. Drag and drop a Column chart from the category charts to your canvas and resize it so that it uses about half of the height of your canvas. Select Data > Connections and select the connection Data by Product.

Step 4. Navigate to the Data Preview tab and click the Refresh Data Preview button. You should see two columns for the product and three columns for the key figures.

Sheet 2: Sales by Product

Step 1. Navigate to the Definition tab. In the Output Values, select the top entry, Cross-Tab Data. Click the reference icon and select a range of five columns and 50 rows (5x50) in the Sales by Product sheet. Click the OK button and navigate to the Usage tab. Select the option Refresh On Load (Figure 5).

Figure 5

Data connection usage

Step 2. Close the Data Manager. Right-click the column chart in the canvas and select the Properties menu. Enter Net sales by product as the title.

Step 3. Select the option By Series for the data and click the plus (+) icon to add a series.

Step 4. Enter Net Sales for the Name of the series.

Step 5. Click the reference icon next to Values (Y) and select the range from C3 to C50 in the Sales by Product sheet.

Step 6. Click the reference icon next to Category Labels (X) and select the range B3 to B50 for the Product descriptions (Figure 6).

Figure 6

Chart properties

Step 7. Right-click the chart and open the Properties menu. Navigate to the area Behavior > Common. Activate the In Series and In Values options for Ignore Blank Cells.

Step 8. Click the reference icon next to Status for the Dynamic Visibility and select cell B6 from the Settings sheet (Figure 7). B6 is the cell where you store the user selection from the radio buttons. Set the Key to the value 1, which means that when the user selects the summary by product, the chart is shown; otherwise it is hidden.

Figure 7

Chart properties: Dynamic Visibility

Currently your dashboard allows you to select between a view by product or by calendar month. In the next steps, you add another chart, so you should hide the current chart to avoid too many elements on your canvas. Go to the menu View > Object Browser and select the option to hide the chart from your canvas (Figure 8).

Figure 8

Object Browser

Sheet 3: Sales by Month

Step 1. Drag and drop a column chart from the category chart to your canvas and resize it so that it uses about half of the height of your canvas and overlaps with the chart you created previously. Select Data > Connections and select the connection Data by Calendar Month.

Step 2. Navigate to the Data Preview tab. Click the Refresh Data Preview button. You should see one column for the calendar month and three columns for the key figures.

Step 3. Navigate to the Definition tab. In the Output Values, select the entry Cross-Tab Data. Click the reference icon next to Insert In and select a range of four columns and fifty rows (4x50) in your sheet. Click the OK button.

Step 4. Navigate to the Usage tab. Select the option Refresh on Load and close the Data Manager.

Step 5. Right-click the column chart in the canvas and select Properties. Enter Net Sales by Month as the title for the chart. Select the option by Series for the Data and click the plus icon to add a series. Enter Net Sales for the name of the series. Click the reference icon next to Values (Y) and select the range B3 to B50. Click the reference icon next to Category Labels (X) and select the range A3 to A50.

Step 6. Right-click the chart and open the Properties menu. Navigate to the area Behavior > Common. Activate both options — In Series and In Values — for the option Ignore Blank Cells. Click the reference icon next to Status for the Dynamic Visibility and select the cell B6 from the Settings sheet. Set the Key to 2 so that when the user selects the summary by calendar month, the chart is shown; otherwise it is hidden.

Step 7. Go to SAP > Publish to save the Xcelsius dashboard to your SAP NetWeaver BW system and enter a description and technical name for your Xcelsius dashboard.

Step 8. Go to SAP > Launch to launch the BEx Web runtime and to show your dashboard. The system asks you to authenticate the Web runtime. Your dashboard should be presented and you should be able to switch between a view by product (Figure 9) and calendar month (Figure 10).

Figure 9

Xcelsius visualization by product

Figure 10

Xcelsius visualization by month

Sheet 4: Sales by Month (Drill)

Up to now, the Xcelsius dashboard shows the net sales revenue either by product or by month. Now I show you how to add two charts and enable the drill down from the first set of charts to the second set of charts.

Step 1. Drag and drop a new column chart to the canvas and place it below the existing two charts. This chart is displayed when the upper chart shows the net sales by product, so you need to configure the dynamic visibility.

Step 2. Right-click the new chart and go to Properties > Behavior > Common. Click the reference icon next to Status for the Dynamic Visibility and select cell B6 from the Settings sheet. Set the Key to the value 1.

Step 3. Go to Data > Connections and select the connection Data by Calendar Month (Drill).

Step 4. Navigate to the Data Preview tab and click the Refresh Data Preview button. You should see one column for the calendar month and three columns for the key figures.

Step 5. Navigate to the Definition tab and in the Output Values, select the entry Cross-Tab Data. Click the reference icon next to Insert In and select a range of four columns and 50 rows (4x50). Click the OK button.

Step 6. Navigate to the Usage tab, select the option Refresh on Load, and close the Data Manager.

Step 7. Right-click the newly added chart and open the Properties menu. Enter Net sales by Month and Product as the title. Select the option By Series for the Data and click the plus icon to add a series. Enter Net Sales for the name of the series. Click the reference icon next to Values (Y) and select the range B3 to B50. Click the reference icon next to Category Labels (X) and select the range A3 to A50 from sheet Sales by Month (Drill) for the calendar month.

Step 8. Right-click the chart and open the Properties menu. Navigate to the area Behavior > Common.  Activate the In Series and In Values options for the option Ignore Blank Cells.

Set the Connection Filter

In addition, you now need to set the filter for the connection that this chart uses to pass the selected value from the upper chart.

Step 1. Right-click the upper level chart showing the Net Sales by Product and select Properties. Navigate to the Drill Down area and activate the option Enable Drill Down. Set the Insertion Type to Row.

Step 2. Select Net Sales in the Series list. Click the reference icon next to Source Data and select the cells A3 to B50 from the Sales by Product sheet.

Step 3. Click the reference icon next to Destination and set it to the cells B10 to C10 in the Settings sheet.

You configured the upper level chart to store key and description in the Settings sheet. It is important to leverage the key so that you are sending a unique value, as the description could be language-dependent master data. Now you need to configure the connection for the lower level chart to use this value as the filter and refresh each time the value changes.

Step 4. Go to Data > Connections and select the connection Data by Calendar Month (Drill).

Step 5. Navigate to the Definition tab, open the entry Filter in the Input Values area, and select the entry Product.

Step 6. Click the reference icon next to Read From and select cell B10 in the Settings sheet. Click the OK button.

Step 7. Navigate to the Usage tab. Click the reference icon and select cell B10 as the trigger cell. Click the OK button.

Step 8. Activate the option When Value Changes (Figure 11). Close the Data Manager.

Figure 11

Connection properties

Step 9. Right-click the lower level chart showing net sales by month and select the menu item Properties. Click the reference icon next to the property Subtitle and select cell C10 from the Settings sheet. This enables the chart to show the product description for each drill down.

Create the Calendar Month Drill-Down Chart

Now you need to repeat the steps from the previous section and create the drill-down chart for the drill down from the calendar month showing each product.

Step 1. Drag and drop a new column chart to the canvas and resize the chart so that it has a similar size to the first lower level chart and overlaps with it.

Step 2. Select Data > Connections > Data by Product (Drill).

Step 3. Navigate to the Data Preview tab and click the Refresh Data Preview button to verify the structure of the data set. You should see two column for the product and three columns for the key figures.

Step 4. Navigate to the Definition tab. In the Output Values area, select the top entry Cross-Tab data. Click the reference icon next to Insert In and select a range of five columns and 50 rows (5x50) in the Sales by Product (Drill) sheet. Click the OK button.

Step 5. Navigate to the Usage tab, select the option Refresh on Load, and close the Data Manager.

Step 6. Right-click the newly created chart and select the menu item Properties. Enter a title and remove the subtitle. Select the option Series for the Data and click the plus icon to add a series. Enter Net Sales for the name of the series.

Step 7. Click the reference icon next to Values (Y) and select the range C3 to C50 in the Sales by Product (Drill) sheet.

Step 8. Click the reference icon next to Category Labels (X) and select the range B3 to B50 from the Sales by Product (Drill) sheet for the product.

Step 9. Right-click the chart and open the Properties menu. Navigate to the area Behavior > Common. Activate both the In Series and In Values options for the option Ignore Blank Cells.

Step 10. Click the reference icon next to Status for the Dynamic Visibility and select the cell B6 from the Settings sheet. Set the key to 2.

Step 11. Right-click the upper level chart showing the Net Sales by month and select Properties > Drill Down. Activate the option Enable drill down and set the insertion type to Row.

Step 12. Click the reference icon next to Source Data and select the cells A3 to A50 from the Sales by Month sheet.

Step 13. Click the reference icon next to Destination and set it to the cells B11 in the Settings sheet.

Step 14. Go to Data > Connections and select the Data by Product (Drill) connection.

Step 15. Navigate to the Definition tab and open the entry Filter in the Input Values area. Select the entry Cal. year / month. Click the reference icon and select cell B11 in the Settings sheet.

Step 16. Navigate to the Usage tab. Click the reference icon and select cell B11 as the trigger cell. Activate the option When Value Changes and close the Data Manager

Step 17. Right-click the lower level chart showing net sales by product and select the menu item Properties. Enter Net sales by product as the chart title. Click the reference icon next to Subtitle and select cell B11 from the Settings sheet. This enables the chart to show the calendar month for each drill down.

Step 18. Go to SAP > Publish to save all your changes to the SAP NetWeaver BW repository.

Step 19. Go to SAP > Launch to launch the Xcelsius dashboard (Figure 12).

Figure 12

Xcelsius dashboard

You created an Xcelsius dashboard that allows you not only to select a different chart on the highest level but also allows you to drill down into more detail depending on the chart type you selected in the beginning.

Ingo Hilgefort

Ingo Hilgefort started his career in 1999 with Seagate Software/Crystal Decisions as a trainer and consultant. He moved to Walldorf for Crystal Decisions at the end of 2000, and worked with the SAP NetWeaver BW development team integrating Crystal Reports with SAP NetWeaver BW. He then relocated to Vancouver in 2004, and worked as a product manager/program manager (in engineering) on the integration of BusinessObjects products with SAP products. Ingo's focus is now on the integration of the SAP BusinessObjects BI suite with  SAP landscapes, such as SAP BW and SAP BW on SAP HANA, focusing on end-to-end integration scenarios. In addition to his experience as a product manager and in his engineering roles, Ingo has been involved in architecting and delivering deployments of SAP BusinessObjects software in combination with SAP software for a number of global customers, and has been recognized by the SAP Community as an SAP Mentor for SAP BusinessObjects- and SAP integration-related topics. Currently, Ingo is the Vice President of Product Management and Product Strategy at Visual BI Solutions, working on extensions to SAP’s product offering such as SAP BusinessObjects Design Studio and SAP Lumira. You may follow him on Twitter at @ihilgefort.

You may contact the author at Ingo@visualbi.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.