Qlik Community

QlikView Documents

Documents for QlikView related information.

Integrating Sharepoint with Qlikview

Comments
Qvmaster2019
Contributor

Using MS Access, it works for me when reloading it using Qlikview Desktop but always fails when I used Qlikview Publisher.

Error: SQL##f - SqlState: S0002, ErrorCode: 4294965991, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine could not find the object 'Contacts'. Make sure the object exists and that you spell its name and the path name correctly. If 'Contacts' is not a local object, check your network connection or contact the server administrator.

0 Likes
Not applicable

How to Fetch a Sharepoint 2013 List using REST interface

n earlier versions of SharePoint most of the Qlikview developers used  Windows SharePoint Services Remote Procedure Call (RPC) methods to fetch data from a share point list.


http://Server_Name/[sites/][Site_Name/]_vti_bin/owssvr.dll?Cmd=Display&List=GUID&XMLDATA=TRUE


This method of extraction always required a GUID to fetch a list or a specific view. This posed difficulties in automating some of the extraction tasks.


With the launch of Sharepoint 2013 Microsoft provided REST(Representational State Transfer) interface which is a data service that allows you to use construct HTTP requests to query SharePoint list data. The main advantage here is that WCF Data Services uses the OData protocol for fetching the data. The data is returned using a well known XML format called ATOM. As a Qlikview developer you don't have to learn entire technical aspects of this new feature. All you have to do is to understand some of the very useful methods.



Some of the main advantages are given below.


1.  GUID is no longer required to fetch a list. Now we can fetch a list by the Title

Use the following format

http://server/site/_api/web/lists/getbytitle(‘Title’)/Items

2. We can fetch meta data about the list.

http://server/site/_api/web/lists/getbytitle(‘Title’will provide a XML with Metadata about list


For example

[content/properties/ItemCount]  will provide you the number of records in the list


3. OData protocol have much more powerful methods and Going forward ,Microsoft is positioning it as a preferred method of interaction.


Following are some of the available options


OptionDescription

$expand

Directs that related records should be retrieved in the record or collection being retrieved.

$filter

Specifies an expression or function that must evaluate to ‘true’ for a record to be returned in the collection.

$orderby

Determines what values are used to order a collection of records.

$select

Specifies a sub set of properties to return.

$skip

Sets the number of records to skip before it retrieves records in a collection.

$top

Determines the maximum number of records to return.



Sample Script for Fetching data from Sharepoint 2013 List using ODate protocol


//Get number of Records from the list


ListCount:

LOAD

        Num([content/properties/ItemCount]) as ItemCount

FROM  [http://server/site/_api/web/lists/getbytitle(‘Title’) ] (XmlSimple, Table is [entry]);

//fetch the Row count

Let vListCount=  Peek('ItemCount',0,'ListCount')  ;

//Get List

//Please remember to use top method. If the top method with number of total rows is omitted the fetch might not return all the

//records in the share point list


List:

LOAD

      [content/properties/ID] as ID,

    [content/properties/Title] as Title,

    [content/properties/Modified] as Modified,

    [content/properties/Created] as Created

FROM [http://server/site/_api/web/lists/getbytitle(‘Title’)/Items?$Top=$(vListCount)] (XmlSimple, Table is [feed/entry]);

Hope this will help you in your quest for the next Qlikview solution. This method enabled us to automate fetching of lists with the same name from different share-point sites.


Wishing you all the best of up coming holiday season from your friends in Michigan


Pauljin Kochupyloth     | Sr. BI Consultant

WIT Inc :"HARNESS THE POWER OF BI"  http://www.witinc.com/

Qvmaster2019
Contributor

Thanks a lot of this!

Will this work for Sharepoint 2013 hosted in cloud?

My list is :

https://rubber2013.sharepoint.com/sites/financial/Qlikview/Lists/Qlikview%20Notes/AllItems.aspx

I tried couple of combinations but doesn't seems to work. Either object not found or download failed.

0 Likes
Not applicable
0 Likes
Qvmaster2019
Contributor

I cannot try this at the moment. I am getting an error prior to this.

The download failed

0 Likes
johncaqc
Valued Contributor

Same here. Can the authors please test their links

0 Likes
Not applicable

Sorry Darvin,

The sharepoints are hosted in intranet and most of the sites are not available out side the company network. So I won't be able to provide you with a live example of how it works. IF you have access to your own sharepoint sites make sure that REST and OData services are turned on at those sites. Then read a little bit about REST and OData. It will be required before you can use it in Qlikview. When it comes to Qlikview you are  basically extracting a XML over http.

Qvmaster2019
Contributor

Thanks! I got it working but not when running the task in QMC.

I am getting this.

1/2/2014 2:36:56 PM:       Error: The download failed

0 Likes
Not applicable

Check whether the QMC admin user have proper security access

0 Likes
Not applicable

Hi,

I also facing the same issue. Did you manage to find a solution? I'm extracting using "_vti_bin/ListData.svc" method. The account have access to the sharepoint site. (Can open the URL in IE)

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2012-10-27 10:41 PM
Updated by: