I'm struggling with connecting QlikView to SharePoint lists. I can do it on an individual site using the GUID of the list and the view in an RPC call (e.g. http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID) but the problem comes when I create a new SharePoint site and have to configure this RPC call again.
This is my scenario:
I'm using QlikView to provide portfolio/programme-level reporting on risk and issue data stored on multiple project sites in sharepoint 2010 enterprise server. Each project has a separate site, and therefore every risks list and issues list has its own GUID and view GUID on every single project site. Projects start and finish, new ones need to be set-up, and this leaves me with a lot of admin-overhead in QlikView to ensure all new projects are having their respective risk/issue data captured in the dashboard.
Is there another way to retrieve data from Sharepoint lists other than GUIDs? The data is consistent across every project in terms of field names and formats (I am using a template) and the URLs are consistent (the only variable is the project name, e.g. http://<my server>/sites/projects/<project name>/lists/risks/).
My first problem is retrieving SharePoint data using something other than GUIDs, and my second problem is setting QlikView up to reference another list on my Project Portfolio site that contains entries with URLs for every project site, and then looping through these project sites in my query. This list on the portfolio site is where users request new project sites to be set up, so it contains an entry for every single project and will always have entries for new ones, therefore it makes sense to reference it with QlikView to ensure all risks and issues data is retrieved from every project at any given point in time.