SAP BusinessObjects 3.1 SP3, Xcelsius 2008.
This white paper is meant for SAP BusinessObjects developers who are working on Xcelsius Dashboard integration with MS Excel as source file. Also it describes the connection and data refresh steps required to perform.
Author(s): Rahul Desai
Company: Tata Consultancy Services Ltd
Created on: 04 September 2012
Rahul Desai works as a SAP BW/BI/BOBJ consultant in Tata Consultancy Services Ltd and having 6 years of experience in SAP BW/BI Implementations and Production support environment.
This white paper is meant for SAP BusinessObjects developers who are working on Xcelsius Dashboard and using MS Excel as source of data. Also this paper will give you the details of establish a connection with source file and details of MS Excel connection.
Creation of sample Dashboard
Below is the MS Excel (Version: 2010) source file containing the data which need to be present in Xcelsius dashboard
In the below screen you can see the controls used in the Dashboard, Now we need to Link the CELL so that it will show the value available in the source. But before that we need to establish the connection with MS Excel source file.
Establish new connection
In below screen you can see the embedded workbook is blank.
Now here we will establish new connection with MS Excel source file, for those first choose menu “DATA”.
Select button Get External data and then select Existing connection, see the below screen for the same.
Now below popup will appear on your screen, select option “BROWSE MORE”.
And select you source MS Excel file, once you select the file it will ask you to choose the Sheet you want to extract the data.
Here we need to select the “Source1” because it contains the data that we want to present in dashboard.
Once you click on “OK” your connection is ready to use, see the below output for the same.
Now whenever your data changed we just need to refresh the embedded workbook in XLF file.
We will give the cell reference to all dashboard controls, so you can see those data on dashboard.
Once you will link all your data to dashboard then it looks like below:
Xcelsius connection properties with MS Excel
Again go to the menu “DATA” and click on the button “CONNECTION”, the below information will appear:
If you want to see location where your current connection id pointed than select the link “click here to see the selected connection are used” so that it will show you the exact place where it pick the data, in our example it is from “DATA” sheet.
Now click on the properties to see the connection properties.
Here you can see the different setting like refresh query with time parameter and also you can keep the setting when you want to refresh your data whenever you open the XLF file.
Now click on definition tab:
Connection Type is your MS Excel source.
Connection file is your source file contains data.
Connection string contain information regarding you data provider.
NOTE: here data provider is Microsoft.ACE.OLEDB.12.0 because we are using MS Excel version 2010, if you are using 2007 or 2003 than data provider would be Microsoft.Jet.OLEDB.4.0
Command type is Table because we are extracting information in tabular format.
Command text is nothing but your work sheet here our is “Source1”
Data refresh Exercise
Now to see whether our refresh command is working properly or not we will change some figures in Excel source file. We will change the Quantity of product PP01 to 50, the current Quantity is 24.
Now first see the below current XLF preview:
Now we will go to menu DATA and select the refresh option.
After refresh you can see the updated data in dashboard.