Skip to Content

Applies to:

SAP BusinessObjects 3.1 SP3, Xcelsius 2008.

Summary

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

Author Bio

/wp-content/uploads/2012/09/author_134663.jpg

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.

Introduction

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

/wp-content/uploads/2012/09/1_134665.jpg

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.

/wp-content/uploads/2012/09/2_134666.jpg

Establish new connection

In below screen you can see the embedded workbook is blank.

/wp-content/uploads/2012/09/3_134667.jpg

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.

/wp-content/uploads/2012/09/4_134668.jpg

Now below popup will appear on your screen, select option “BROWSE MORE”.

/wp-content/uploads/2012/09/5_134669.jpg

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.

/wp-content/uploads/2012/09/6_134670.jpg

Once you click on “OK” your connection is ready to use, see the below output for the same.

/wp-content/uploads/2012/09/7_134671.jpg

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.

/wp-content/uploads/2012/09/8_134672.jpg

Once you will link all your data to dashboard then it looks like below:

/wp-content/uploads/2012/09/9_134673.jpg

Xcelsius connection properties with MS Excel

Again go to the menu “DATA” and click on the button “CONNECTION”, the below information will appear:

/wp-content/uploads/2012/09/10_134674.jpg

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.

/wp-content/uploads/2012/09/11_134676.jpg

Now click on the properties to see the connection properties.

/wp-content/uploads/2012/09/12_134678.jpg

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:

/wp-content/uploads/2012/09/13_134679.jpg

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.

/wp-content/uploads/2012/09/14_134688.jpg

Now first see the below current XLF preview:

/wp-content/uploads/2012/09/15_134689.jpg

Now we will go to menu DATA and select the refresh option.

/wp-content/uploads/2012/09/16_134690.jpg

After refresh you can see the updated data in dashboard.

/wp-content/uploads/2012/09/17_134691.jpg

Related Content

https://help.sap.com/analytics

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_exp_user_en.pdf

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Abhishek Sharma

    Nice one Rahul,

    This will definitely help quiet a number of people who were looking for some alternative to refresh the dashboard from another excel, where they could change the dataset.

    Your method seems quiet straight forward and simple.

    I would like to know if we can refresh the connection without coming out of the preview or directly from the dashboard itself ?

    [Say we use a button to link up the refresh activity ?]

    Regards,

    Abhishek

    (0) 
    1. rahul desai Post author

      Hi Abhishek,

              Answer to your question: In preview mode we do not have such option to refresh the data. But yes you can set the parameter like 1. refresh every 30 mins or you can keep your intervals according to your requirement and 2. Refresh data when opening the file.

             Wtih this 2 options you can set the refresh property. This options are resides in “connection properties” in usage TAB.

      Thanks

      Rahul Desai

      (0) 

Leave a Reply