PetaPoco DoddleReport Dynamic PIVOT Query

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:

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:

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.

Fork me on GitHub

Be Sociable, Share!

About Todd Wood

Solution Architect and owner of Wood Consulting Practice, LLC. C#, ASP.NET MVC, Oracle, RoR, and Linux et al rolled up into one on my Mac.
This entry was posted in DoddleReport, PetaPoco and tagged , . Bookmark the permalink.
  • Martin Camp

    Hey Thanks Todd!

    I need to report on some pivit & unpivit data from SQL. Your method & PetaPoco worked a treat.

    My current need is to do Grouping and Totals!

    Doodle does not handle grouping. Nor your grid.

    I was trying to use DJME http://djme.codeplex.com/ as it does dynamic grouping and exporting to excel, it has an option to Auto generate the columns which I thought would fit, but it doesn’t seam to handle the  object.

    Hoping to turn this into a dynamic report generator where the user can
    select a bunch of columns to include and select level of grouping and
    totals.
    Currently I do grouping and totals in the view with some javascript for a few static reports, but does not export to excel nicely.

    Do you know of a jquery grid that will work with PetaPoco & dynamic column

    Thanks

     

    • http://www.woodcp.com/ Todd A. Wood

      Martin,

      I worked a lot with DataTables.net and it is very extensible, but not sure if it does the grouping like DJME does. I have used the DevExpress grids that provides these types of features and Excel exporters, but it is a commercial product.

      I had not heard of the DJME project and grid, but it looks good and I will need to check it out some more. I would recommend you check out DataTables if you have not seen it before as well. DataTables has been my Swiss Army knife of grids lately, but many projects still roll their own grid when faced with specific tricky requirements.

      Good luck and let me know how it turns out.

      Thanks.