topic Re: Loading data from sharepoint list work in local but couldn't make it work in the development server in QlikView

Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Wed, 10 Oct 2012 09:52:52 GMT

Hi All

Scenario:1 (In my local machine: Sharepoint link can be accessed without passing the userid and password)

1. Click on actions in sharepoint site and export it to excel. Save the excel in the required location.

2. Go to Data tab of excel and do the following

Connections --> Properties --> check: Enable background refresh and Refresh data when opening the file

3. Save the file

After this I have created one macro which opens up the above excel as we have enabled the above properties in Data tab it will automatically fetch the latest data from the sharepoint link.

Scenario:2 (In server machine: Sharepoint link can only be accessed by passing the userid and password)

Here I am not able to do step:2(Mentioned). May be it is asking for the userid and password to access the sharepoint link. I think the problem here might be because of the security.

So any idea what could be the possible reasons for been able to achieve the same in scenario:2. Also would like to know what need to be done for the scenario:2 so that it can achieved same like scenario:1.

Hope someone can helpme out on this!

Have already refered this in the following post but unable to find the solution yet 😞

http://community.qlik.com/message/255028#255028

http://community.qlik.com/message/254657#254657

http://community.qlik.com/message/254552#254552

http://community.qlik.com/message/253928#253928

http://community.qlik.com/message/253970#253970

http://community.qlik.com/message/253964#253964

http://community.qlik.com/message/253963#253963

http://community.qlik.com/message/253962#253962

http://community.qlik.com/message/253960#253960

http://community.qlik.com/message/253958#253958

http://community.qlik.com/message/253956#253956

http://community.qlik.com/message/253955#253955

http://community.qlik.com/message/253952#253952

http://community.qlik.com/message/253951#253951

http://community.qlik.com/message/252820#252820

http://community.qlik.com/message/253949#253949

http://community.qlik.com/message/253947#253947

http://community.qlik.com/message/253946#253946

http://community.qlik.com/message/253929#253929

http://community.qlik.com/message/253927#253927

http://community.qlik.com/message/253926#253926

http://community.qlik.com/message/252325#252325

http://community.qlik.com/message/252145#252145

http://community.qlik.com/message/252143#252143

http://community.qlik.com/message/252142#252142

http://community.qlik.com/message/251924#251924

http://community.qlik.com/message/251875#251875

http://community.qlik.com/message/251862#251862

http://community.qlik.com/message/251817#251817

http://community.qlik.com/message/249903#249903

http://community.qlik.com/message/249902#249902

http://community.qlik.com/message/249900#249900

http://community.qlik.com/message/249899#249899

http://community.qlik.com/message/249896#249896

Thanks

qvforum

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

hallquist_nate — Thu, 11 Oct 2012 14:27:26 GMT

Does the SErvice account running QVS has access and rights to the Sharepoint site? That would be where I would start looking...

have you looked at the log files to see if the account is getting denied access?

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Mon, 15 Oct 2012 11:19:14 GMT

Does the SErvice account running QVS has access and rights to the Sharepoint site? That would be where I would start looking...

Sorry I don't know how to find whether it is having access and rights to the sharepoint site. Could you please help me out on how to find the same!

have you looked at the log files to see if the account is getting denied access?

It is not showing any message as I am refreshing through excel and not through QV. I am just opening the excel using macro, once the excel gets opened as I have enabled the background refresh excel should fetches data from sharepoint site. But that is not happening here!

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

hallquist_nate — Mon, 15 Oct 2012 13:06:02 GMT

OK, I see now. You are trying to refresh Excel to Sharepoint. If you can, create an RDP connection to the Sharepoint server and go through the Event logs, and Application Logs. See if there are any messages where Sharepoint is denying access to an incoming request. IF you can't do this, call your help desk and describe what you are seeing and see if an admin can go through the log files for you and find out if there is an issue.

The other thing you may need to do is pass your credentials through Excel. If you go back to the window where you set your data refresh, there is the Definitions tab. In that tab there is a spot for a connection string, and checkbox to "Save Password". I'm guessing that you need to configure that to pass your credentials. Once you do that, it may solve your issues.

Try this and let me know.

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Tue, 16 Oct 2012 11:40:11 GMT

I think we don't have rights to connect to Sharepoint server to go through Event logs. Also we cannot ask the helpdesk(of our client) to go through the same.

I have tried replicate the same as per your instructions but the "Save Password" option is disabled. Any idea what could be done now?

Thanks your all your help. so nicely you have guided me... I am sure that you can help me out to resolve this issue. If not I need to go back to the client on what exactly it is stopping us from refreshing the excel.

I also want to let you know that when I am trying to replicate the samething which I am doing in the development machine. It is throwing following error could you please guide further!

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

hallquist_nate — Tue, 16 Oct 2012 13:18:57 GMT

Well, I think the above message hits the nail on the head. We'll need to enable the data connection. This is likely one of the reasons you can't get data. Looks like this connection method is turned off, as a security measure. I thihk that you can trust the Sharepoint server, so enabling this shouldn't be an issue. However, you need to make sure that all parties are aware of what you doing.

Enable the connection and let's see what happens...

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Wed, 17 Oct 2012 09:06:54 GMT

If I remember correctly I think I was able to enable the connections few months ago but at that time I was not able to refresh the excel. Anyway let me forget the past now.

Currently as I couldn't enable to connection. May I know how can I do the same please? Any help is appreciated!

Just a thought:

Instead of using excel to fetch the data from sharepoint. Can we try to load the data directly into QlikView? As I don't think client can enable the data connections due to security reasons. I thought it could be better if we can load the data directly into qlikview. Please let me whether is this possible! If so I kindly request you to guide me on the same in your convenient time.

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

hallquist_nate — Wed, 17 Oct 2012 13:36:46 GMT

Here is what I know so far. I have never pulled data from sharepoint, but it is a database. Start with this thread and see what you can accomplish. I think the main point is that the Sharepoint data is stored in a file, of some sort. we need to know what the file type is.

http://community.qlik.com/message/110134#110134

Let me know what the results are.

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Thu, 18 Oct 2012 09:36:56 GMT

Hi Nate

I have already read this post and followed the instructions but couldn't achieve the task. Any other thoughts please?

Thanks

qvforum

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

hallquist_nate — Thu, 18 Oct 2012 13:15:25 GMT

How are you not achieving, are you getting an error or an access denied message? The thing is that the SharePoint database is stored in a file, on the sharepoint server. You need to be able to get to that server, and then that DB file. If you are getting denied access, the only thing to do is to contact IT as ask for access. If they won't do it, then you have to contact a business leader and have them get it for you. There isn't a lot we can really do if you are being denied access.

Let me know...

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Fri, 19 Oct 2012 07:59:58 GMT

Hi Nate

I tried to achieve the same like below. I tried to find out for owssvr.dll but couldn't find the same. But I have changed the link to something like below but unable to achieve it.But it throws the message as "Object not found".

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=listGUID&View=viewGUID

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:

The thing is that the SharePoint database is stored in a file, on the sharepoint server. You need to be able to get to that server, and then that DB file. If you are getting denied access, the only thing to do is to contact IT as ask for access. If they won't do it, then you have to contact a business leader and have them get it for you. There isn't a lot we can really do if you are being denied access.
Let me know...

I am not sure where the Sharepoint server lies. So obviously never tried to connect to it. All these days I have trying to fetch the data from an aspx page assuming this is the sharepoint site. Please guide me on if I have get to know about the sharepoint server how should I able to connect to it.

I need to acheive this task but I feel I am no away near to it as I am not sure about the approach. Tried to google it as well but it seems nobody has achieved it before :-(.

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

qlikviewforum — Mon, 22 Oct 2012 07:22:36 GMT

Hi Nate

I am not sure whether you have read my above post. If not I request you to read and reply me back in your convenient time.

Thanks,

qvforum

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

hallquist_nate — Mon, 22 Oct 2012 12:54:46 GMT

I see what you are trying to do, and I have seen that work for sites where there is data displayed on that page, but not from a Sharepoint site. Let me see what I can find out. As far as I can tell though, you will need to get authorizations into the Sharpoint server, either through the QV service account you will use to run QVS or through your own login.

Re: Loading data from sharepoint list work in local but couldn't make it work in the development server

Fri, 08 Mar 2013 06:27:06 GMT

Hi Nate,

I want to fetch lists created in Sharepoint page into Qlikview.

Is it possible to directly fetch this sharepoint data into qlikview?

Or do i need some Plugin for it?

Can you please guide me regarding this?

Regards,

Yojas