How to get a Sharepoint List in Xcelsius and publish outside Sharepoint

If eg. Treasury is maintaining a Tax Rate by country list in the Corporate Sharepoint directory, you can import that list into Xcelsius for use in your dashboard without setting up a portal provider as you do not want to publish the dashboard in Sharepoint itself.

The easiest way is to use the url built up:

http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&XMLDATA=TRUE

{0} is the server path.
{1} is GUID from the list (go to settings of your list, in the url it is the long code after List= )

Import into Excel, add an XML Map, import that into Xcelsius and set up the data connection with Excel maps.

Link your xml url to a cell containing the url + random number to be sure the cache is cleared in windows explorer. [="http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&XMLDATA=TRUE&r="&RAND()]

Set the refresh on open. Now each time the dashboard loads, the xml list from sharepoint is loaded.

Recent Related Posts
Femke Kooij is a dashboard and report designer, developer and educator specializing in SAP | BusinessObjects Dashboards (better known as Xcelsius) and Crystal Reports. She has also a lot of experience with other SAP | BusinessObjects toolings like, Web Intelligence (WebI), Information Design Tool (Universe Designer), BusinessObjects Data Services (BODS) and the overall server platform BusinessObjects Enterprise (Launchpad, CMC). She spends a lot of her time thinking about and sharing her knowledge of Xcelsius to others through her own blog. Lately she is exploring other visualization tools like Qlikview and Tableau. On the later she is also posting some experiences. Currently she is in the employment of Cognizant Technology Solutions. Her blog only contains her personal views, thoughts and opinions. It is not endorsed by Cognizant Technology Solutions nor does it constitute any official communication of Cognizant Technology Solutions.

This entry was posted in Connection and tagged , , , . Bookmark the permalink.

4 Comments to How to get a Sharepoint List in Xcelsius and publish outside Sharepoint

  1. Hamed Mehrzadegan says:

    Hi femek
    i have the same problem as Charles describe!

  2. Hamed Mehrzadegan says:

    Hi Femke
    that’s useful post , but i have a problem
    when i add data to excel, the work sheet does not update automatically, according to the refresh time.
    thanks for your assistant

  3. Jon Fortner says:

    Femke,
    I get the URL part, but it’s not clear what you do in Excel to pull the data in using the URL. Can you elaborate please.
    Jon

  4. Hi Femke

    Nice blog post, however there are a few points to be aware of when using this method:

    1. If any of your columns in the sharepoint list are null, i.e. your list is populated on a monthly basis so that future months are blank, they will not be returned as part of the xml_map. A workaround is to add a dummy row with 0′s for each column.

    2. This may be a bug in the version of Xcelsius I’m using at this client (Xc 2008 SP3), but if there is a null value within your dataset, then any values that are directly below this null ‘cell’ in the column are ‘moved up’ to fill the gap. This only happens when refreshing the URL in Xcelsius. Not found a fix for this as yet :(

    3. You need to adjust the Item Limit of the list (Edit View Settings) to make sure it returns all of your rows if the list has a static row structure, otherwise you need to include &Query=* to the URL which brings all of the rows, but also returns a whole load of unwanted columns.

    Hope this helps!

    Thanks
    Charles