SharePoint + Reporting Services = nuances
My colleagues and I are engaged in the development and implementation of applied intra-corporate solutions based on the Microsoft SharePoint platform, as well as Russian EDS, the implementation of which is justified by years of experience and appropriate in certain cases.
On my projects, I love using Microsoft Reporting Services (hereinafter - SSRS) for the rapid implementation of reports and print forms that do not require complex behavioral dynamics.
With the availability of data in the structure of the DBMS tables, and, consequently, the DataSet of the report, 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 mart by means of ETL. And what if the conditions, the budget and the timing of the project do not allow it? It is necessary to use the available data sources (DataSource).
')
As you might guess, it will be about creating SSRS reports using data from SharePoint lists.
The arsenal of available SSRS data sources is great, but in practice it is possible to “plunge” into nuances that are not always obvious.
So, we proceed to the formulation of the problem.
Initial data:
Formulation of the problem:
It would seem, what could be easier? In practice, it may be sadness.
By the way, for SharePoint, direct access to the data in the database is technically possible, yes, but difficult, and not allowed by licensing policy. And, to be honest, it is better not to even try to directly access the objects of the SharePoint database, 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. Wonderful? Yes, as if not always!
The problem is this:
Concerning association of various lists within the limits of one DataSet. Yes it is impossible. A DataSet that retrieves data from a SharePoint list can contain only data from a single list. As a way out - for each list, create a separate DataSet, tricky to filter dependent DataSets via the SSRS report system parameters in order not to take the entire data array from all the merged lists, and Join data from different lists using the SSRS functions (Lookup, LookupSet or MultiLookup) in the report view itself, for example, in a table.
On the second point. There is such a thing in the SharePoint lists as Throttling, when a limit on the number of items in a folder is set, by default it is 5K items. What is usually done for large lists? In a simple way, the structure of the storage of items changes in such a way that the list contains folders (hidden), and the data is archived so that in each specific folder there are no more than 5K items or the value specified in the list settings (by experience it is better let 5K and remains).
We are dealing with such a list, where there are many elements, all by folders, the data that has passed its life cycle are archived, but remain in the list and are needed, including for reports.
It would seem that the CAML is built into the RSSharePointList web service, from which data is obtained and there is enough
But the data is needed and something needs to be done, without a showcase, no time. We return to the sources and look in the direction of receiving data through the SharePoint web services, but not those mentioned above. Create a Datasource, use the XML connection type, write the “path to the SharePoint site collection” + “/ _ vti_bin / Lists.asmx” to the connection string, specify the credentials of the service user for the reports. Further more interesting - we create a DataSet with the source described above and a query for similarity:
You can form a query programmatically, for example, you only need to filter the data that is obtained by Inner Join with another DataSet. And this can be done by forming a request dynamically and substituting the necessary fragment of the CAML request into it.
Having mastered a couple of tricks to retrieve data and being in conditions similar to the conditions of the task, you can quickly implement a fairly complex report and secure it for a functional customer, even if you have limited rights when access to SQL Server is missing and you don’t get to the SharePoint farm administrator .
Perhaps, in one article all. Let's see if this topic is interesting to readers of habrahabr. If yes, it is possible to paint the implementation of the task of this post on the example of a specific report with disclosure of technical nuances of implementing logically combined DataSets based on report parameters, dynamic query generation to web services lists, examples of using Lookup'ov in report tables.
On my projects, I love using Microsoft Reporting Services (hereinafter - SSRS) for the rapid implementation of reports and print forms that do not require complex behavioral dynamics.
With the availability of data in the structure of the DBMS tables, and, consequently, the DataSet of the report, 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 mart by means of ETL. And what if the conditions, the budget and the timing of the project do not allow it? It is necessary to use the available data sources (DataSource).
')
As you might guess, it will be about creating SSRS reports using data from SharePoint lists.
The arsenal of available SSRS data sources is great, but in practice it is possible to “plunge” into nuances that are not always obvious.
So, we proceed to the formulation of the problem.
Initial data:
- Information system based on Microsoft SharePoint 2010 Foundation;
- Microsoft SQL Server 2008 R2 (and, R2 is the principal condition);
- Microsoft SQL Server Reporting Services in SharePoint integration mode.
Formulation of the problem:
- Implement a custom report using data distributed across several different lists (Lists) of SharePoint.
- Do not use data marts or customized web services (they can of course be used, but additional development does not fit into the scope of the task and complexity).
It would seem, what could be easier? In practice, it may be sadness.
By the way, for SharePoint, direct access to the data in the database is technically possible, yes, but difficult, and not allowed by licensing policy. And, to be honest, it is better not to even try to directly access the objects of the SharePoint database, 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. Wonderful? Yes, as if not always!
The problem is this:
- Want to Join in a Single DataSet? Forget it.
- Want to get data from the folder structure in the list? Forget it.
Concerning association of various lists within the limits of one DataSet. Yes it is impossible. A DataSet that retrieves data from a SharePoint list can contain only data from a single list. As a way out - for each list, create a separate DataSet, tricky to filter dependent DataSets via the SSRS report system parameters in order not to take the entire data array from all the merged lists, and Join data from different lists using the SSRS functions (Lookup, LookupSet or MultiLookup) in the report view itself, for example, in a table.
On the second point. There is such a thing in the SharePoint lists as Throttling, when a limit on the number of items in a folder is set, by default it is 5K items. What is usually done for large lists? In a simple way, the structure of the storage of items changes in such a way that the list contains folders (hidden), and the data is archived so that in each specific folder there are no more than 5K items or the value specified in the list settings (by experience it is better let 5K and remains).
We are dealing with such a list, where there are many elements, all by folders, the data that has passed its life cycle are archived, but remain in the list and are needed, including for reports.
It would seem that the CAML is built into the RSSharePointList web service, from which data is obtained and there is enough
/> to bypass the nuance, but, unfortunately, the scop in the SharePoint List Datasource is not embedded. Simply such data using the Datasource "List of SharePoint" does not get in any way.But the data is needed and something needs to be done, without a showcase, no time. We return to the sources and look in the direction of receiving data through the SharePoint web services, but not those mentioned above. Create a Datasource, use the XML connection type, write the “path to the SharePoint site collection” + “/ _ vti_bin / Lists.asmx” to the connection string, specify the credentials of the service user for the reports. Further more interesting - we create a DataSet with the source described above and a query for similarity:
<Query> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction> <Method Namespace='http://schemas.microsoft.com/sharepoint/soap/' Name='GetListItems'> <Parameters> <Parameter Name='listName'> <DefaultValue>{A4AA15E5-D722-4583-AA7D-C51C86A384F4}</DefaultValue> </Parameter> <Parameter Name='viewName'> <DefaultValue>{231A273A-DDA2-4CA1-B8FC-54B3DC4B0816}</DefaultValue> </Parameter> <Parameter Name='query' Type='xml'> <DefaultValue> <Query> <Where> ... </Where> </Query> </DefaultValue> </Parameter> </Parameters> </Method> <ElementPath IgnoreNamespaces='True'>*</ElementPath> </Query> You can form a query programmatically, for example, you only need to filter the data that is obtained by Inner Join with another DataSet. And this can be done by forming a request dynamically and substituting the necessary fragment of the CAML request into it.
Having mastered a couple of tricks to retrieve data and being in conditions similar to the conditions of the task, you can quickly implement a fairly complex report and secure it for a functional customer, even if you have limited rights when access to SQL Server is missing and you don’t get to the SharePoint farm administrator .
Perhaps, in one article all. Let's see if this topic is interesting to readers of habrahabr. If yes, it is possible to paint the implementation of the task of this post on the example of a specific report with disclosure of technical nuances of implementing logically combined DataSets based on report parameters, dynamic query generation to web services lists, examples of using Lookup'ov in report tables.
Source: https://habr.com/ru/post/177639/
All Articles