How to consume SharePoint lists in Web Intelligence 4.1
This step by step document describes how to consume Sharepoint content - like lists, in Web Intelligence.
Currently there is no universe or Web Intelligence connector that enables users to expose Sharepoint content as a data source. It is however possible to leverage MS Access support of Sharepoint through a universe to solve this restriction.
A. MS Access database
In this example, we are going to expose and consume in WebIntelligence a couple of Sharepoint lists.
1. In Microsoft Access, create a new Access Database or open an existing database to reuse with Sharepoint
2. Insert a SharePoint object using the menu External Data:
External Data->Sharepoint List
3. Enter the Sharepoint site with the port number (2845 in this example)
4. Make sure you select option “Link to the data source by creating a linked table” is selected. This insures that the universe and the dependent WebI reports will be dynamic and will be able to retrieve new data from Sharepoint on Refresh.
5. All available Sharepoint lists and items on the selected site are displayed; select the lists and items you need to expose in Web Intelligence:
6. The selected lists are inserted in Access database as tables with appropriate data
7. Save the database
1. On the client machine where Information Design Tool will be used, open ODBC Data Source Administrator and create a new DSN pointing to the Access database
2. If you plan to use Web Intelligence in server mode from BI Launchpad, you need to create the same DSN described in previous step, on that server.
3. Open Information Design Tool and create a new relational connection:
4. Publish the connection to the CMS, and create a connection shortcut (.cns).
5. Create a Data Foundation on that connection shortcut
6. Insert the tables representing the Sharepoint lists, lists that you want to expose in the universe and Web Intelligence:
In this example we expose two Lists in the same universe without joins. It is however possible to join multiple tables representing various Sharepoint lists if that is necessary.
7. Create a Business layer on that Data Foundation then publish the universe to the CMS.
C. Web Intelligence document
1. Start Web Intelligence in BI Launchpad or Web Intelligence Rich Client, and then create a new document based on universe. Select the previously published universe.
2. Add the objects representing the fields you want to expose in Web Intelligence document to the Query and Run the query
3. The result report exposes the data retrieved from SharePoint:
4. Changing the date in SharePoint, then refreshing Web Intelligence document reflects the changes instantly
Adding a new line in SharePoint:
Refreshing Web Intelligence document, the new line is added to the report: