SharePoint + Reporting Services = nuances

My colleagues and I are engaged in the development and implementation of applied internal corporate solutions based on the Microsoft SharePoint platform, as well as Russian EDMS, the implementation of which is justified by experience gained over the years and is appropriate in certain cases.

On my projects I love to use Microsoft Reporting Services (hereinafter - SSRS) for the quick implementation of reports and print forms that do not require complex dynamics.

With the availability of data in the DBMS table structure, and, consequently, of the Report DataSet, with SSRS everything is easy and simple, if not trivial (of course not for all cases). But what if there is no access to the database or is it prohibited by the licensing policy or even attempts are made to make it technically difficult to implement? It is necessary to prepare a data showcase using ETL tools. But what if the conditions, budget and terms of the project do not allow this? You must use available data sources (DataSource).

As you might have guessed, it will be about creating SSRS reports using data from SharePoint lists.

Although the arsenal of available SSRS data sources is large, in practice you can “plunge” into the nuances that are not always obvious.

So, we pass to the statement of the problem.
Initial data:

  • Information system based on Microsoft SharePoint 2010 Foundation;
  • Microsoft SQL Server 2008 R2 (and, R2, a fundamental condition);
  • Microsoft SQL Server Reporting Services in SharePoint Integrated Mode.

Formulation of the problem:

  • Implement a custom report using data distributed across several different SharePoint Lists.
  • Do not use data marts or customized web services (they can of course be used, but additional development no longer fits into the scope of the task and laboriousness).

It would seem that could be easier? In practice, it may be sad.

By the way, for SharePoint, direct access to data in the database is technically possible, yes, but difficult, and not allowed by the licensing policy. And, honestly, it’s better not to even try to directly access the SharePoint database objects, believe me.

Okay, let's continue. SQL Server 2008, starting with version R2, presented us with a new data source in the form of SharePoint lists. Is it wonderful? Yes, as if not always!

The problem is this:

  • Want to Join in the Same DataSet? Forget it.
  • Want to get data from the folder structure in the list? Forget it.

Regarding combining various lists within one DataSet. Yes, this is not possible. A DataSet that retrieves data from a SharePoint list can contain only data from a single list. As a way out, create a separate DataSet for each list, slyly filter dependent DataSets through the SSRS report system parameters so as not to collect the entire data array from all merged lists, and do Join data from different lists using the SSRS (Lookup, LookupSet or MultiLookup) in the report view itself, for example, in a table.

On the second point. In SharePoint lists, there is such a thing as Throttling, when you set a limit on the number of items in a folder, by default it is 5K items. What is usually done for large lists? In a simple way, the storage structure of items changes so that the list contains folders (hidden), and the data is archived so that each specific folder does not have more than 5K items or the same value that is set in the list settings (from experience it’s better let 5K remain).

We are dealing with such a list, where there are many elements, everything is in folders, data that has passed its life cycle is archived, but remains in the list and is needed, including for reports.

It would seem that in the RSSharePointList web service from which the data is received, CAML is built-in and enough/>to bypass the nuance, but, unfortunately, scope in the SharePoint List Datasource is not implemented. It’s just that you can’t get such data using the Datasource SharePoint List.

But the data is needed and something needs to be done, while not a showcase, no time. We return to the origins and look in the direction of receiving data through the SharePoint web services, but not the ones mentioned above. We create a Datasource, use the XML connection type, in the connection string write "path to the collection of SharePoint sites" + "/ _ vti_bin / Lists.asmx", specify the credentials of the service user for reports. Further more interesting - we create a DataSet with the source described above and a query similarity:

http://schemas.microsoft.com/sharepoint/soap/GetListItems{A4AA15E5-D722-4583-AA7D-C51C86A384F4}{231A273A-DDA2-4CA1-B8FC-54B3DC4B0816}
						...
					*

The request can be generated programmatically, for example, you need to filter only the data that is obtained by Inner Join with another DataSet. And this is possible by forming a dynamic request and substituting the necessary fragment of the CAML request into it.

Having mastered a couple of tricks of getting data and finding yourself in conditions similar to the conditions of the task, you can quickly implement a rather complex report and deploy it for a functional customer, even if you are in a situation of limited rights when there is no access to SQL Server and not reach the SharePoint farm administrator .

Perhaps, in the framework of one article, everything. Let's see if this topic is interesting to habrahabr readers. If so, it is possible to describe the implementation of the task of this post using a specific report as an example, revealing the technical nuances of the implementation of logically integrated DataSets based on report parameters, dynamically generating a request to list web services, examples of using Lookups in report tables.

Also popular now: