Introduction

These are a few posts that came up as a result of my work in one consultancy project helping a team that worked on implementation of a SharePoint 2010 based system. My involvement into the project was not too long, only a month, and my tasks were mostly bringing break-through solutions for the most peculiar requirements or the most intricate integration points. Some of those solutions will be described in these posts.

SharePoint 2010 Consultancy Project. Part 1 - Business Workflows
SharePoint 2010 Consultancy Project. Part 3 - How to kill SharePoint farm with the help of SharePoint Designer

Part 2 – Reports

Requirements and Technical Solutions

The system required reports built for contracts in Proposals (some details about information organization were provided earlier in ‘Closing CR Tasks automatically on Resubmit’ section in Part 1. So additional complexity for the reports was brought by the fact that the library was not plain; it had more complicated structure with document sets pushing down some attributes from top level to document level, custom content types for contracts, InfoPath form promoting attributes and synchronizing some attributes up to document set level, and some other nice features.
There were two types of reports required in the system

Summary reports with aggregation calculations

Grid reports with groups and totals

After analyzing requirements and keeping in mind that the solutions should be as simple as possible it looked like the best option for summary reports would be MS SQL Server Reporting Services (using Reports Builder 3.0) and for grid reports – SharePoint Designer Data Views. Both approaches were more or less out-of-the-box, which complied with supportability requirements.

After prototypes for summary reports based on SSRS had been finished, customer realized that it will be a delay in getting proper licenses for SSRS so the technical solution for summary reports should be reconsidered. At this point I had two solution candidates one based on custom web parts created in Visual Studio and SPD Data Views as for grid reports. SPD Data View won because of supportability requirements.

Summary reports with SPD Data Views

Data Sources and Parameters

The most intricate part in building Data View web parts for summary reports appeared in configuring data source.

There are two options to add Data View to the page – first one is to insert web part initially based on one of the lists, the second – add an empty web part and after that select needed list as a data source for the web part. It looks like both options are to achieve the same but surprisingly with the first option less functionality is available for manipulating data source. So the tip here is: insert an empty Data View web part and after that bind data source to a list or document library.

From now you can configure the options for a data source. Note that all those configurations will guide how CAML querying SharePoint objects is built, which is important for performance.

Selecting needed fields. Here you can “unselect” columns that will not be needed for data view, which is good because extra columns will not annoy you when building views and secondly the CAML query will be optimized for better performance as mentioned above

Filters. Again for better performance it is important to configure all needed filters here on the level of data source (alternatively XSLT filters can be added).
In those filters it is also possible to use Data View parameters. As you can see on picture above DateFrom and DateTo values are taken from relevant parameters. There are several types of parameters – they can be introduced as Control, Cookie, Form, Query String, or Server Variable.
Another nice feature for filter conditions is that I can group sub-conditions to manage precedence of logical operations

Sort

Folder scope. For my tasks it was critical to query all contracts inside document sets, so fortunately I had this option for data source (it was really frustrating when I was not able to configure folder scope for SharePoint data source in Reporting Services)

There were some strange things about building filters as well. E.g. I could not find ContentType in the list of available columns even though many other system columns were there. Workaround for content types would be to modify query directly in code. Surprisingly, it worked fine for content type.

Building Reports

Data View web parts suit perfectly for building data grid reports. For summary reports, when only groups and totals are displayed, it is trickier. Basically this can be achieved with “Sort & Group” functionality.

Problem 1 – Overall Total
Even for simple case with only one grouping level, adding overall total is not really straightforward.

In example on picture below, all contracts are grouped by some property and totals are calculated for those groups. This can be done with simple group settings: for my group set ‘Show group footer’ and in ‘Advanced Grouping’ set ‘Always hide group details’. Then calculations for group totals can be done with formulas, e.g. format-number(sum($nodeset/@BC_TCDValueExclVat.),'#,###.00')

But I cannot easily add formulas for overall totals because there is not even a placeholder for them. Obviously you can go to the Code view again, and having enough knowledge in XSLT, add needed elements there, but this does not really sound as out-of-the-box. In my case it was easier because data source (SharePoint document library) had a column IsContract indicating if an item is contract or not. Since reports were built for contracts only, all items coming from data source had value ‘Yes’ in IsContract column. Now the trick is that I can build two levels of grouping: first based on IsContract columns (there will be only one group all the time) and then a property for actual grouping. Then I can use headers and footers for IsContract group level for overall totals.

Problem 2 – Incorrect totals for grouping hierarchies with more than two columns
That solution with overall total worked fine for me until I needed more than two levels of hierarchy. After I added third column to grouping hierarchy I realized that totals are calculated incorrectly. After inspecting XSL code for I found a simple solution to fix incorrect totals – names for group levels should be hierarchical themselves (see the picture below):

Problem 3 – Report with two hierarchies
Initially, when I just met the problem 2 described above and until I found a solution for it, I was really disappointed with the way SPD builds those groupings. But later it helped me building another report with two hierarchies under one overall total. As shown on picture below the report displayed two parts based on the type of contract – Awarded To and Awarded By. Inside those parts there were groups of contracts based on two different properties. Those ‘To’ and ‘By’ parts could be split into two separate reports but overall totals were needed, which made them appear on the same report.

The solution was to build one data view web part with 4 groups representing two grouping hierarchies as shown on picture below. Note that the top level is just to get overall totals as it was described in ‘Problem

2’.

One more thing to be done for displaying report correctly was to define conditional formatting rules hiding or showing elements depending on report part.