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:
A simple SQL PIVOT query to transform the data:
SELECT * FROM(SELECT * FROM UserRequirement) AS UserInfo PIVOT (MIN(ExpirationDate) FOR Requirement IN ([Alpha], [Bravo], [Charlie], [Delta], [Echo]) ) AS UserPivot
The following table illustrates the results of the SQL PIVOT query:
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:
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:
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:
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.
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.