Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

trailblazer070
Contributor

Best method to read from SharePoint List on desktop & publisher - urgent

I require some guidance.

1) What is the best method to read from SharePoint lists using Qlikview desktop . What are the steps for this?

2) Which method is best preferred for reading from SharePoint lists when published on publisher. What settings are required to enable QlikView to read from SharePoint lists on Qlikview publisher/Management console.

15 Replies
ramasaisaksoft
Valued Contributor III

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Reading Data from Sharepoint lists into QlikView

Step 1: We need to create a Sample List in SharePoint

Step 2: Open QlikView

Step 3: Choose File Menu -> Open URL

Step 4: Type URL you want to open E.g. (####.sharepoint.com)

Step 5: Enter Username and Password for your SharePoint account in QlikView itself

Step 6: Choose your List from SharePoint.

Step 7: Now Right Click -> choose properties -> copy Address (URL)

Step 8: Open Script Editor, click Web Files

Step 9: Paste URL in Internet File option

Step 10: Click Next

Step 11: Now we need to change the character set option in File Wizard window. Select the option Unicode (UTF-8)

Step 12: Click Finish

Step 13: The Query builder will automatically generated the script

Step 14: Press F5 or Click Reload option

trailblazer070
Contributor

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Hi Rama Sai, will the above work - have you tried and tested it.

Please share the steps for reading the SharePoint list after qlikview is published on the publisher. What contents of the SharePoint list setting should it hold and what settings are required at the QMC please write to me step wise as you have written here.

Did it work for you both at desktop and QmC. Give me your review. Don't forget step wise for the QmC console.

Regards

trailblazer070
Contributor

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Thank you for your post. Please share settings and steps (like mentioned above) to read sharepoint lists from the Publisher/QMC step by step in setps.

trailblazer070
Contributor

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Please reply back with Publisher step by step to read SharePoint lists from qlikview. What settings are required at QmC/publisher level?

ramasaisaksoft
Valued Contributor III

Re: Best method to read from SharePoint List on desktop & publisher - urgent

At present i don't have QMC & Publisher access. I mean i don't have privileges  to access the server/publisher.

i used the SharePoint link and developed the dashboard to show

  • the # of Tickets  raised to Help desk
  • What are the reasons/Reply they are given while closing the ticket...etc.

(The above data came from a SharePoint link and i followed the steps mentioned in the link Reading Data from Sharepoint lists into QlikView)

i didn't  done any settings .automatically code will fetch the data .even though settings are there i didn't remember those now.so sorry.

Highlighted
trailblazer070
Contributor

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Rama Sai, so when you publish your dashboard with the SharePoint list connection, let me know what settings you applied at the QMC and Publisher. Just check up at your end and let know. That will be of great help.

Another thing I wanted to know is the steps you have mentioned above is HTML method of connecting to a SharePoint list - am I correct. So what needs to be done is take the url where the list is at the SharePoint and place it in Internet file option in Web Files

click Web Files.Click Next. Lastly the character set option in File Wizard window has to be changed to option Unicode (UTF-8) and it will work. So this is HTML method am I correct. Is that what this is called.



1) Please let me know which type this is .i.E is it the hTmL method of reading a SPList and 2) share the settings that you have at QlikView publisher and QMC that enables you to make this work there.

ramasaisaksoft
Valued Contributor III

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Hi,


The steps i followed  while i fetch the data from SharePoint list.


Note:- there are no more settings required in QMC for refresh the data.why because QV treats this as a web link so if your web link works it will fetch the data.


Go to SharePoint Site

Ex:-https://xxxxxxx.com/sites/00115/Lists/Delay%20Log/AllItems.aspx]

Right click on the page View Source Code

CTRL+F for List=

Ex:-

List={77d0fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}

Again CTRL+F for View=

Ex:-

View=%7bE151BDA-FA52-49C8-B338-516018F78B0F%7d

Keep it these 2 values some where

As per the below Qlikview Community link

https://community.qlik.com/docs/DOC-1308#

Using RPC-Calls

2. Using RPC-Calls

An easier way is to use RPC-calls using owssvr.dll, like:

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

listGUID and viewGUID must be in the format {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}.


The listGUID can easily be found by navigating to the list in Sharepoint and go to List Settings. The listGUID will then show up in the address-field in your browser.

If viewGUID is omitted, the default view will be retrieved. To find the viewGUID locate the list in Sharepoint and activate the view needed. Select View Soure in IE to see the source code. Search for view={ to locate the viewGUID.

The response will be in XML, so when reading into QlikView make sure you select XML Files and Internet File. Add the correct crafted URL as described above. The data retrieved can be found in the node xml/data/row.

Example URLs to be used in QlikView:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}&View={58678A30-DC12-4C66-8568-28E4D9A3EED1}

For more info about owssvr.dll use Google to find various ways of filtering the information

Note: Only fields defined in a view can be retrieved. You need to construct the view with the fields needed, if not using the default view.

We need to change this link

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

Ex:-

https://xxxxxx.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={77d7fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE91BDA-FA52-49C8-B338-516018F78B0F%7d

Note:-

Here List={………}&View=%........

we already took from source file

Now you need to go to Qlikview

  1. Ctrl+Eàscript
  2. Click on Web Files àInternet file address

you need to give the address as you already created

https://xxxxxx.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={77d7fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE91BDA-FA52-49C8-B338-516018F78B0F%7d

Select source format as XML

Then select XML/DATA/Row in your Tables tab on right hand side.

Here you can see the entire data as it is in SharePoint.

Click on Finish Button.

Then your rows will visible like

LOAD ows_ID,
ows_Dep,
ows_Date_x0020_Issue_x0020_happd,
ows_Issue_x0020_Tipe,
ows_Issue_x0020_Caty,
ows_ProblemDesc,
ows_Notification_x0020_Numb,
ows_TotalHrs,
ows_Issue_x0020_Status,
ows_ContainmentAction,
ows_IssueOwner,
%Key_xml_98C49F546A18E856    // Key to parent table: xml
FROM

[https://xxxxxx.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={77d7fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE91BDA-FA52-49C8-B338-516018F78B0F%7d]

https://suppbio.sp.medimmune.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&Li...{77d720fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}

https://suppbio.sp.medimmune.com/sites/00115/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&Li...{77d720fb%2D5b61%2D4f38%2D97d1%2Dc5ccc8369b93}&View=%7bE9151BDA-FA52-49C8-B338-516018F78B0F%7d



trailblazer070
Contributor

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Hi Rama Sai, Please confirm the following to me as soon as possible : (if it is ok to use below mentioned easy method to pull SharePoint list in qlikview and if it will surely work in both qlikview desktop and publisher (QMC).)

1) Basically what I am saying is Instead of using the link that you have mentioned above that has the ListGUID and the ViewGUID in it-that you have created using the RPC Call method, is it fine to just use the direct link that is there on the web browser when we go to the list in SharePoint . (Basically the direct internet link on the web browser for the SharePoint list and also referring to your very first Reply steps.) I think this method of directly using the internet link at the web browser in sharepoint is called the HTML method. So if I take the direct URL of the SharePoint where the list is as seen on the web browser and open it in qlikview as web files, internet file address, all i need to do is change the character set option in File Wizard window to option Unicode (UTF-8) and it will work. (Please confirm to me if it is ok to use the above mentioned easy method instead of the RPC Call method.)

2. Are there any settings required at the QMC Publisher end as this would be a direct web link(talking about the easy method).

please test if possible at your end both at desktop and publisher/QMC - Ask around if required and confirm to me if it is ok to use the above mentioned easy method instead of the RPC Call list view method.

ramasaisaksoft
Valued Contributor III

Re: Best method to read from SharePoint List on desktop & publisher - urgent

Hi Blazer,

1) i gave all the code which i followed in my previous project ,i don't have environment so i can't test and judge your thought.please check by your self and confirm me also if you find the any solution.

2) we don't have publisher license for my client so i don't have idea.

In QMC i already mentioned there is no need any settings change.

i don't have any share point environment to cross check now.i worked this 8 months before.i shifted  another organisation so i am helpless to verify.

Community Browser