How to Fetch a Sharepoint 2013 List using REST interface

[#1]

In 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

Option	Description
$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]);

"I tried to create this as a blog ,but apparently couldn't publish it to a place where every one can access. Sorry for posting it in multiple places. "

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]

I

[#3]

I have tried your method above with no luck.  Using sharepoint 2013 and QV 11 SR5

server is uf.hide.com

site = prod

list  name = ProductionTanks

Is this the right code:

FROM  [http://uf.hide.com/prod/_api/web/lists/getbytitle(‘ProductionTanks’) ] (XmlSimple, Table is [entry]);

I can get unknown MSXML errro

[#4]

I encountered the same error when I was deploying my application to server. So the following might fix the issue.

This error message is usually found where there is security access issues.

1.. Check whether the ID by which you are executing the qlikview publisher has proper sharepoint access.

Log into the server with the user id and password . Launch the sharepoint server . If you are asked to enter user name and password chances are that Qlikview is not getting the user authentication info.

2. If your id has access , you need to do one more configuration in Internet explorer in the server. Apparently microsoft is storing this setting as part of internet explorer security settings.

Go to internet explorer > internet options> Security .

Click on trusted sites and add the sharepoint site into list of custom sites .

Then click on the 'Custom level' button under security level  area.

This will open up a list of options . Scroll all the way do and find option

'User authentication'

     'Logon'

By default it will be selected to 'Automatic logon only in intranet zone' change it to

'Automatic logon with current usename and password'

This is what I have done to get things working. Ironically this falls in between admin and developer and sometimes none of us know what is going on.

We have to do it every server using the ID. For example if you move the app to prod , you have to do it there too. I guess you get the idea,

Try it out and let me know whether it helped you..

[Qvmaster2019]

Up

[#6]

Our sharepoint is not hosted on the server. You might be right about the cloud hosting. There might be something which is prohibiting qlikview user from reaching the sharepoint.

[#7]

Hi pauljin

Thank you for the information.  I found something similar to this and it also worked.  Instead of trusted sites, we go to Local Intranet, then Advanced Settings, then add the SP site to trusted site and had to check the box for secure HTML..  and it worked! 

[Qvmaster2019]

Up

[#9]

QMC only.  works fine from desktop.  but all is working fine now

[#10]

Hi pauljin,

thank you for sharing this information. It works fine for me as long as I only use the fields ID, title, modified and created. As soon as I try to read details from fields like description with multiple lines I get no values. How can I read from fields with blank in the field description like 'Assignd to' and how can I extract date from the field 'Created by' or 'Modified by'? Thank you in advance.

Kind regards,

Harald Martini