QlikView – Access Data on SharePoint

QlikView is a nice BI Tool that is lightweighted and fast.

On top of that the Client version is free of charge and gives you access to the full features. There other limitations and that is you can’t share your work using the free goodie !

But here are some examples on how to access SharePoint Data.

Prerequisites :

1. WebClient Service

You need to make sure that you have the WebClient service installed and running on the server and the user has access to the SharePoint library.

To install the service on the server follow these steps:

Start the Windows Server Manager.

In the tree view, highlight the Features node.

In the details pane, click Add Features.

In the Add Features Wizard, check the Desktop Experience box, and then click Next.

Click Install.

When the Add Features Wizard has finished

Click Close.

Click Yes when promoted to restart the computer.

2. Authentication

Adding the SharePoint site to local intranet sites in the security tab in internet explorer on the machine that QlikView Server runs on.

More info…

As I understand, QlikView uses the IE authentication model – So if you can access your SharePoint site without a sign-on then you can also get list data from a script using owssvr.dll method.

Assuming you are using AD authentication to the SharePoint server.

If your PC is on the same domain as that used for SharePoint authentication, just add your SharePoint site to the “Local Intranet” zone in IE.

If your PC is not in the same domain, you will also need to authenticate and make sure you check the ‘Remember Password’. This will store your credentials in the windows Credentials Manager.

Examples :

1. Accessing SharePoint Document Libraries

Open a new QVW Project and open the Script editor.

Press the EDIT Script ICON and press the WEB FILE button.

image

This will bring up the File Wizard

QlikView accepts 2 different URL formats.

A. WebDav URL

Fill in your WebDav URL to the Doc. Libr.

\\YourSPSite\DavWWWRoot\sites\…\Doc.Libr.Name\FilenName.XLSX

Press next and see the data is available for further processing

image

B. HTTP URL

Use the URL to retrieve the file.

image

Use the Browser Copy shortcut of the file to get the URL.

2. Accessing SharePoint Lists

Same procedure as above to bring up the WEB FILE Wizard.

But using a different URL structure. SharePoint supports 2 interfaces :

A. URL Protocol (Remote RPC)

Syntax : http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID

This one is the better one because you can filter or use a specific SP View

The following parameters can also be used in the protocol:
FileDialogFilterValue to set filters for a view and to return the list of all files of a specified type from a document library according to file name extension (for example, *.doc, *.ppt, or *.xls).
FilterField n to specify the name of a field in the database, where n is an integer that is limited only by the number of fields allowed in the database table or by the length allowed for the URL field.
FilterValue n to specify the string value on which to filter a field, where n is an integer that is limited only by the length allowed for the URL field.
SortField to specify the name of the field on which to sort.
SortDir to indicate an ascending (asc) or descending (desc) sort order.
Using to specify the relative URL of a virtual file used in exporting an item or list, which can be one of the following values: vcard.vcf, event.ics, query.iqy, or query.bqy.

Example :

image

B. Web Services API

Syntax : http://your-sharepoint-server/site/ICT/_vti_bin/listdata.svc/ListName

Keep in mind the oDdata or REST protocol has a few disadvantages (no VIEWS can be selected, and it returns only 1000 records max)

You can uses Filters and Sort syntax though.

 

image

image

On top this QlikView is COM visible, so you can pick this up in your scripting adventures. Surprised smile

Here is an Example on how to access the QV Client using the COM interface

See also here : SharePoint Interface

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: