Qlik Community

QlikView Documents

Documents for QlikView related information.

How Qlikview read data from Sharepoint?

Not applicable

How Qlikview read data from Sharepoint?

Dear All,

 

I suggest to watch the below video tutorial to understand how qlikview read data from Sharepoint.

 

http://www.makdeniz.com/how-to-use-list-data-by-qlikview/ 

 

 

Regards,

 

Asim Akin 

Tags (2)
Labels (1)
Comments
qlikviewforum
Contributor II

Thanks for the video! In your example everytime you are entering the password.Whereas in my case it should be automated. It should not ask for the username and password everytime. Data in the qvw should be automated whenever it get refreshed if there is any change in the data. Is it possible?

Not applicable

This is one of few ways to get data from SharePoint. You can also get data as XML from the owssvr.dll from SharePoint using

http://<<ServerURL>>/_vti_bin/owssvr.dll?Cmd=Display&List=<<GUID>>javascript:;;&XMLDATA=TRUE

GUID can be retrieved from the settings page of the list.

Regards,

Naren

amien
Valued Contributor

Dear Asim,

Thanks for posting this.

Do you know if this would also work reading excel files from a sharepoint location. Without installing any WebDAV services.

I have a problem with installing the WebSAV services in the QlikView services, because there is no 'clean' WebDAV installer. It comes with alot of additional tooling with j don't need and not allowed to install.

Not applicable
amien
Valued Contributor

Hi Pauljin, Thanks for your comment, but this blog is behind a private group..

Not applicable

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


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/

Not applicable

I posted the entire blog as a response and it is currently under review from moderator. Sorry for the inconvenience 

Employee
Employee

Has anyone managed to find out how to get versions when you are using version control in Sharepoint through the REST interface? 

Not applicable

Hello Pauljin,

This method works, but only locally - after I open the URL and provide my credentials.

Question is: How REST can work if I put the .qvw in the server to reload automatically? How should we pass through login validation on SP O365?

everest226
Contributor III
Version history
Revision #:
1 of 1
Last update:
‎12-27-2011 02:57 PM
Updated by: