PetaPoco DoddleReport Dynamic PIVOT Query

3 minute read

TL;DR – Sample ASP.NET MVC3 application demonstrating how to load PIVOT query results as a dynamic object with PetaPoco and exporting the data to Excel using DoddleReport.

This blog post can be forked on GitHub: tawman / PetaPocoPivot.

Dynamic Data Structures with PIVOT

SQL Server 2008 R2 provides the capability to PIVOT and UNPIVOT table data depending on your need to flatten rows into columns or columns into rows. In our case, we needed to transform multiple rows of employee expiration dates into a single employee row with columns for each requirement expiration date.

SQL PIVOT Query Example

The following source data is used to illustrate a SQL PIVOT query in action:

Employee Requirement Expiration Date
John Doe Alpha 01/12/2011
John Doe Bravo 11/07/2012
John Doe Charlie NULL
John Doe Delta 10/11/2009
Sally Smith Bravo 05/10/2012
Sally Smith Charlie 07/04/2012
Sally Smith Echo 08/31/2012

A simple SQL PIVOT query to transform the data:

The following table illustrates the results of the SQL PIVOT query:

Employee Alpha Bravo Charlie Delta Echo
John Doe 01/12/2011 11/07/2012   10/11/2009  
Sally Smith   05/10/2012 07/04/2012   08/31/2012

Data Driven Object Structure

Obviously, defining a POCO object to load the PIVOT data ahead of time is not possible if the aggregated column values are not known until runtime. Our initial implementation bypassed PetaPoco entirely and used a DataTable to export the data to Excel using DoddleReport. Working around PetaPoco did not seem right so I looked for an alternate approach involving PetaPoco.

PetaPoco and Dynamic Types

Fortunately, C# provides a dynamic type but I did not know if PetaPoco and DoddleReport could support this option. Every time I think we pushed PetaPoco to its limit, I learn that it has more tricks up its sleeve. I decided to not even consult the documentation and wired it up to see if we could return an IEnumerable<dynamic> instead of a DataTable.

The answer came as no surprise when coding the PIVOT query into the Repository that PetaPoco could care less if you actually had a POCO:

DoddleReport and Dynamic Types

Now that PetaPoco was returning a dynamic data set, I needed to figure out what DoddleReport could do with it. I had not worked with DoddleReport up to this point, but quickly located the code in our Controller that performed the export to Excel functionality. I checked the ToReportSource() method overloads and saw it had one for an IEnumerable.

Typical ReportResult Action with DoddleReport does not require much code, but it sure does a lot behind the scenes:

My first attempt to run the export with no change to the DoddleReport code was met with a blank export. I tweeted out my status of trying this and Matt Hidinger quickly pointed me to the DynamicReportSource.cs on CodePlex. I added the DynamicReportSource code as a Helper in my project (Note: There is a Nuget package for it) and tried to use it. The first issue I ran into was that the code used an ExpandoObject, but I changed it to use dynamic instead. The second issue I ran into was the handling of NULL values, but I worked around this by using a new object() when the t.Value is null.

Modified DoddleReport DynamicReportSource.cs file used:

Dynamic PetaPoco and DoddleReport

With the final changes to DoddleReport in place, our project now supports exporting PIVOT reports to Microsoft Excel. I put together a sample ASP.NET MVC3 application that demonstrates the flow of the dynamic data from SQL Server via PetaPoco and DoddleReport to Excel.

Standard MVC ActionResult View

The default Index action displays the dynamic data in a normal table view by iterating over the dynamic results: PivotWebExample

Exporting Data to Excel

The DoddleReport Excel link in the header will call the /Home/PivotReport .xlsx ReportAction to trigger the export to Excel. The .xlsx extension it what tells DoddleReport we want an Excel result:

PivotExcelExample

Generating a Default HTML View

If you leave off the extension like the DoddleReport HTML header link does, then DoddleReport will generate the HTML view similar to the Index view I coded: PivotDoddleHtmlExample

DoddleReport supports additional formats like PDF, but I did not add the necessary Nuget packages to support it. With the proper library references in place, all it takes to generate a PDF is to add the .PDF extension to the ReportResult Action URL and DoddleReport will happily generate it for you.

Conclusion

Yet again I learned to never underestimate PetaPoco. I now have the same respect for DoddleReport as it makes a dynamic teammate with PetaPoco to deliver Excel exports based on our PIVOT query results.

I hope you found this blog post helpful and be sure to check out the full source code on GitHub: tawman / PetaPocoPivot to see this dynamic pair in action.