One of the most challenging requirements of an SAP Dashboards project is the need to export data points presented in dashboard charts to Excel so that the information can be further analyzed, processed, and distributed across the organization. There is no standard export-to-Excel component available in SAP Dashboards for delivery of the data.

For example, a regional sales manager runs a dashboard with filters applied on the sales organization and sales area combo boxes. After that, users must engage in workarounds if they want to download (export) the data to their local workstations before applying a formula on the sales data and disseminating the report to others in the organization.

I have created a solution that does not require an add-on component or programming knowledge. The solution, which is based completely on standard SAP BusinessObjects components, allows the export of run-time data in SAP Dashboards to Excel in an intuitive manner, similar to downloading a document from Web sites.

One common workaround used by organizations when exporting data is to have a URL button that, when clicked, opens a new window from the dashboard. In the new window, the pre-configured URL (which could be an OpenDocument URL), will launch other BI reporting tools, like SAP Web Intelligence or Business Explorer, that access the same source for the sales data and have the standard export-to-Excel functionality.

There are two potential issues with this option. One issue is that the filters used in the dashboard cannot be set up to be passed as parameters to restrict the data in the BI reporting tools mentioned above. The users then need to apply the same filters and generate the same data in the reporting tools again.

The other related issue is that after users generate the data with the reporting tools, they need to manually click on the Export to Excel button to export the data. Therefore, from a business user’s perspective, this workaround is not intuitive, as manual and redundant steps are required.

I found that though export functionality and high customization can be achieved with a third-party SAP Dashboards add-on component known as a Software Development Kit (SDK), the prerequisite of leveraging SDK is the knowledge of Adobe Flex, which is a skill set uncommon among many SAP BI professionals. In addition, this option requires further considerations from a budget and maintenance perspective, which are not favored by the project stakeholders and IT staff most of the time. The organization will need to spend extra money to purchase the Adobe Flash builder software and send the IT staff to training so that they can support the solution.

The solution offered in this article involves use of a URL button in the dashboard that, when clicked, launches the pre-defined report that has been created with Crystal Reports for Enterprise. The URL is generated by OpenDocument based on the filters that are applied on the selection control components in the dashboard.

Based on experience gained from implementing SAP BusinessObjects BI projects, I started to take an approach with the main consideration that the solution must allow the users to intuitively export the data points in the dashboard to Excel. The solution must also be able to retain the state of the data last navigated in the dashboard when sending the data points to Excel.

Step-by-Step Configuration of the Solution

In this section, I show you how to configure the major components of the solution to export to Excel the data points in SAP Dashboards at run time. The solution uses a combination of SAP BusinessObjects technologies, including OpenDocument, SAP Crystal Reports for Enterprise, and SAP Dashboards itself.

Step 1. Launch SAP Dashboards. Using a sample country/year sales report, Figure 1 shows the sample dashboard with hardcoded data. In real-world projects, your dashboard will be connected to and receive data from SAP NetWeaver 7.3 BW or another data source. In my example, I assume the data source is SAP BW and that the data come from a Business Explorer query. Note that the dashboard will be uploaded to the BI platform server and accessed through the BI launch pad eventually.

Figure 1

Sample Dashboard

Create a URL button by choosing URL Button from menu path Components > Category > Web Connectivity and name it Download.

Create a combo box (by which users to pick a value to filter) by choosing Combo Box from menu path Components > Category > Selectors. Name it Select a Country:. Create another combo box and name it Select a Year:. The combo boxes are usually bound to BEx or Universe query prompts so that the selected values are passed as parameters to SAP NetWeaver BW or other data source to retrieve results.

Create a column chart by choosing Column Chart from menu path Components > Category > Charts and name it Sales Performance. Bind the data from your data source, which can be a Universe query or a BEx query. This step is performed only to illustrate how the values in the chart change based on the selected values in the combo boxes.

Figure 2 shows the setup of the Excel data in the dashboard. The sample data points are hardcoded in cell area A2:N7. The labels for the Country combo box are bound to cell area A10:A11 and the label of the selected value will be inserted into cell B12. The labels for the Year combo box are bound to cell area D10:D11 and the value of the selected label will be inserted into cell E12. The data in cell area A15:B18 is bound to the URL button in the dashboard. The base URL in cell B15 is a standard OpenDocument URL, which you can retrieve from the property of the report created with Crystal Reports for Enterprise and hosted in BI Platform server. The values in the bracket placeholders are to be replaced with the BI Platform server and document ID of the Crystal Reports for Enterprise report in your system environment accordingly. [BI Platform Server] refers to the server host string of your BI Platform server while [Document ID of Crystal Reports for Enterprise] refers to the document ID of the Crystal Reports for Enterprise report hosted in the BI Platform server.

Figure 2

Excel Data in Sample Dashboard

Figure 3 shows the setting in cell B16. The cell concatenates the string “&lsSpsCountry=” and the values selected from the Country combo box. Keep in mind the syntax used by OpenDocument. lsS is an OpenDocument input parameter syntax specifying a single prompt bound to cell B12 whose value is inserted as the selected one from the Country combo box. psCountry is the corresponding prompt name defined in the report created with Crystal Reports for Enterprise. An example is if the users select Germany from the Country combo box, the cell will output &lsSpsCountry=Germany. When the OpenDocument URL is launched, the Crystal Reports for Enterprise report recognizes lsSpsCountry as a parameter and assigns the value Germany to the corresponding parameter psCountry in the report.

Figure 3

Excel Data (B16) in Sample Dashboard

Figure 4 shows the setting in cell B17. Similar to setup of cell B16, the cell concatenates the string “&lsSpsYear=” and the values selected from the Year combo box. lsS specifies a single prompt bound to cell E12,  whose value is inserted as the selected one from the Year combo box. psYear is the corresponding prompt name defined in the report created with Crystal Reports for Enterprise. An example is if the users select 2013 from the Year combo box, the cell will output &lsSpsYear=2013. When the OpenDocument URL is launched, the Crystal Reports for Enterprise report recognizes lsSpsYear as a parameter and assigns the value 2013 to the corresponding parameter psYear in the report.

Figure 4

Excel Data (B17) in Sample Dashboard

Figure 5 shows the setting in cell B18. &sOutputFormat is an OpenDocument output parameter syntax that specifies the format in which to open the target document. In my example, a value of E specifies the Crystal report to be launched in Excel format.

Figure 5

Excel Data (B18) in Sample Dashboard

Below is the complete URL in cell B19 as a result of concatenation of cells B15, B16, B17 and B18.

https://[BI Platform Server]/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=[Document ID of Crystals Reports for Enterprise]&lsSpsCountry=Germany&lsSpsYear=2013&sOutputFormat=E

Figure 6 shows the configuration of the URL button that points to cell B19.

Figure 6

URL Button Properties

Step 2. Launch Crystal Reports for Enterprise. Connect to the same data source to which your dashboard is connected and from which it gets its data. Figure 7 shows an example of a report created with Crystal Reports for Enterprise designer. The data source is SAP BW and the report definition is based on the same BEx query that we used for the dashboard in step 1. It is important to take note of the parameters shown under the Parameters section, as they filter the data in the report as in the dashboard. You can see where the parameters psCountry and psYear, as mentioned in Step 1, are configured in Crystal Reports for Enterprise.

Figure 7

Crystal Reports for Enterprise Sample

These parameters can be configured through the Query Filter panel in Crystal Reports for Enterprise designer. From the Data Explorer in the left panel in the designer, click on Edit Data Sources… and you will get to the Query Filter panel, as shown in Figure 8. In our example, you will find two filters, which are Country and Year.

Figure 8

Crystal Reports for Enterprise - Query Filter Panel

Figure 9 shows the options available for the filter type when the down arrow alongside the Country filter is clicked. Choose Prompt so that the filter values can be passed from the OpenDocument URL at report run time.

Figure 9

Crystal Reports for Enterprise - Query Filter Type

Figure 10 shows the options available in the Edit Prompt screen after Prompt was selected in Figure 9. In my example, I enter Country in the Prompt Text field. Similarly, return to Figure 9, select Prompt for the Year filter, and enter Year in the Prompt Text field of the subsequent screen.

Figure 10

Edit the Prompt for the Query Filter in Crystal Reports for Enterprise

After you are done with editing the query filters, go back to the main screen, as shown in Figure 7 above. At this point, the designer has generated the parameters based on the query filters automatically. Right click on psCountry in the Parameters section and select Edit Parameter… from the context menu, as shown in Figure 11.

Figure 11

Edit Parameters Based on the Query Filters

Figure 12 shows the Edit Parameter screen, where you can find the Prompt Text Country that was defined in the query filter earlier, as well as the parameter Name psCountry. Note that the designer creates the actual parameter name by concatenating the “ps” with the value in the Prompt Text.

Figure 12

Parameters Based on the Country Query Filter in Crystal Reports for Enterprise

Step 3. Log in to BI launch pad. Launch the dashboard that you created and uploaded to BI Platform server in Step 1. Select a value, e.g. Germany, from the Country combo box and a value, e.g. 2012, from the Year combo box. Click on the Download button. A new window opens in your browser and you get a prompt to save or open the Excel file containing the data points based on the filter values applied on the combo boxes. Select different values, e.g., UK and 2013, from the Country and Year combo boxes, respectively, and click on the Download button again. You will be asked to open or save an Excel file containing a different set of data points based on the different filter values.