Qlik Community

Qlik Healthcare User Group

A private group is for healthcare organizations, partners, and Qlik healthcare staff to collaborate and share insights.

Highlighted
wdchristensen
Valued Contributor

How to Connect QlikSense to a 2013 SharePoint List

** Prerequisite – Install Qlik Sense REST Connector **


1) Get guid from SharePoint list (Maybe your SP Administrator can provide this?)

  •    Navigate to the aspx page that contains the list:

     Example page: http://sharepoint/company/home/folder/project/new/newer/_formats/001/default.aspx

  • Click on the Site Contents link

SharePointQlik00.png

  • Click on Settings

SharePointQlik03.png

  • Doesn’t matter if you actually have permission to the object the Access Denied page has the information you need! Look at the URL and search the string from RIGHT to LEFT and find the first instance of the word “List” and copy everything from that point to the end of the URL. 

AccessRequired.png

Example url: http://sharepoint/company/home/folder/project/new/newer/_formats/001/AccessDenied.aspx?Source=http%3...List%3DZY98XW76%252DVU54%252DTS32%252DRQ19%252DPO87NM65LK43


  • The %3D in the url encoding just means the equal sign (=) so it can be ignored. Change all “%2D” to “-“ (I used notepad)

ReplaceHyphen.PNG

  • Wrap the result in curly brackets and you have the List ID

Example GUID List ID = {ZY98XW76-VU54-TS32-RQ19-PO87NM65LK43}

2) How to view xml code of a list view

  • Build URL: http://{0}/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={1}

          {0} – The URL to your site.  This could be the root web or a child site.

          {1} – The GUID for your list.

Example URL for viewing XML in browser:

http://sharepoint/company/home/folder/project/new/newer/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&Ro...{ZY98XW76%252DVU54%252DTS32%252DRQ19%252DPO87NM65LK43}

  • Copy and paste the new url into your favorite browser and you should see a page of XLM. If you can't view the XML in a browser you won't be able to view it in QLIK so make sure you can see the information before continuing.

HospSampleXML.PNG

3) Open your QlikSense application and create a new connection using the "Qlik Rest Connector".

CreateNewConnection_01.png

4) Create new rest connection.

  • Enter the url you created in step 2.
  • Set the authentication and create a meaningful name (cnSharePointExampe)
  • Fill out the remaining options as follows and test the connection

CreateNewConnection_02.png

5) You can now load data (similar to any other data load process)

SharePointLoadData.png

ExampleDataFromXMLconnection.png

Tags (1)
15 Replies
marounhkhoury
New Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

Hi

I am trying to accomplish the above but it seems that your version uses an old version of Qlik Desktop since mine has the REST Connector included and the authentication section has more options

Can you please advise which authentication method I should use (NTLM , Basic , Digest , ...) . I am using a AD to connect to sharePoint.

BTW I tried all of them and they call return 403 Forbidden although I plugged in the SP administrator account

wdchristensen
Valued Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

I wrote the documentation above based on Qlik Sense 3.2. After I created the documentation we "upgraded" our development to Feb 2018 but unfortunately I can no longer create apps or make data connections so it is hard to update the documentation. We have a support case open for that issue but in the meantime I can make a guess as to the issue.

I suspect your primary issue is building out the URL. Are you able to view the XML in your brower (step 2)? If not, get that part working before you try to make a connection to the sharepoint data.

Do you get the 403 error from Qlik or from a browser when navigating to the derived XML url?

wdchristensen
Valued Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

I was able to create connection with the "Window NTLS" option but I had to supply a login which is not what you. Since I was not able to get Windows Authentication to work on Feb 2018, I edited the connection string in the QMC. This produced an error during the load that the "connection format is obsolete".

marounhkhoury
New Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

Did u get it to work using NTLS ? if yes please how did you supply the username and password (format I mean)

wdchristensen
Valued Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

RestWorkAroundForFeb2018.png

wdchristensen
Valued Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

Can you at view the XML file in your browser?

marounhkhoury
New Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

I am still getting the 403 Error. I am plugging the admin username and password !. and Using the browser when I push the link I am getting download .DLL file I click yes and I open the DLL as XML it opens Up

Here is my URL : https://sharepoint.butec.com.lb/sites/HR/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={...

And a sample when I open the XML file

Untitled.png

wdchristensen
Valued Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

Your XML looks good but when I enter my URL, my XML renders immediately. I don’t have to click “yes”. Can send a screenshot of that dialog box? Were you on your personal computer when you took the XML screenshot or were you remoted into the Qlik application server? Might also check and make sure your SharePoint site doesn’t have any IP deny rules blocking the Qlik Application Server? Are you using Qlik Sense Desktop or Enterprise? 

marounhkhoury
New Contributor

Re: How to Connect QlikSense to a 2013 SharePoint List

I am using Qlik Sense Desktop but I have Qlik Sense Server. I was on my personal computer. I am able to see the sharepoint from my browser and administer it normally .. So I suppose there is no permission access thing