With SAP BusinessObjects Live Office it is possible to insert data from Crystal Reports documents and Web Intelligence reports into Microsoft Office products (Word, Excel, Outlook, PowerPoint). Since data can also be refreshed with this add-on, the Live Office Connection can be a very useful way to provide our dashboards with fresh data.
For this recipe you will need SAP BusinessObjects BI Platform to be installed on your machine and the Live Office Connection software installed on your client computer. You will also need a Crystal Reports document, a Web Intelligence report, or a Universe to connect to.
- Open a new SAP BusinessObjects Dashboards file and go to Preferences... in the File menu. In the Excel Options section, check if Live Office Compatibility is enabled.
- Go to the Live Office tab of the spreadsheet. Click the Crystal Reports or Web Intelligence button to insert a report.
- Log in to your SAP BusinessObjects BI Platform and choose a report.
- If your report contains any parameters, a window will appear in which you can enter the parameter values to filter the dataset that should be retrieved. In the There's more... section of this recipe we will discuss how to connect these prompts to the dashboard.
- In the next window, the actual Crystal Reports document or Web Intelligence report is shown. With some mouse dragging and selecting you can select the data you want to import.
- Click the Switch to Fields button to see an overview of all available fields. Here you can also select which fields you want to use.
- If you want to set some more filters on fields, you can use the next window by clicking on Next.
- In the final window, you can enter a name for the Live Office objects you just set up. Click Finish.
- As you will see, the spreadsheet is now populated with data from the report.
- Go to the Data Manager and add a Live Office Connections connection.
- Change the Sessions URL to point to the SAP BusinessObjects BI Platform server. Ranges are already bound to the cells in which the data has been imported. As you can see, Headers and Data Grid are separated. Do not forget to set up the Usage tab (see the Creating a news ticker with Excel XML Maps recipe).
With the Live Office Connection we retrieve data from Crystal Reports documents, Web Intelligence reports, or connect directly to a Universe. In this way we can reuse the definitions that are created in these reports as a base for our dashboard data.
Just like we have seen in the Using Query as a Web Service (QaaWS) recipe, it is possible to use prompts to select the data that we want to retrieve and use it in our dashboard with a Live Office Connection. Use the following steps to set this up:
- In the Live Office tab of the spreadsheet, select the Modify Object button and choose Prompt Setting....
- In the window that appears you will see the available parameters. After selecting the parameter you want to use, select Choose Excel data range.
- Bind the field to a spreadsheet cell.
- Follow step 6 until 9 of the There's more... section on Using prompts of the Using Query as a Web Service (QaaWS) recipe to use this prompt from within your dashboard.
