Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,124 | Comments: 45,470

filter by tags archive

ChallengeNH Prof Exporting Reports

time to read 3 min | 590 words

One of the things that I am working on right now is exporting NH Prof reports. This isn’t an earth shattering feature, the idea is to take a report like this:


And give you the ability to save it in a format that you can send to your DBA, or maybe just put it away for later perusal.

I started with the simplest thing that could possibly work, exporting the reports to XML. Using Linq to XML, that has been a true pleasure, and got me this:

<?xml version="1.0" encoding="utf-8"?>
<unique-queries xmlns="http://nhprof.com/reports/2009/09">
<query average-duration="0.1" count="10" is-cached="false" is-ddl="false">
<duration database-only="0" nhibernate="0" />
<short-sql>SELECT ... FROM Comments comments0_ WHERE comments0_.PostId = 1</short-sql>
<formatted-sql>SELECT comments0_.PostId as PostId1_,
comments0_.Id as Id1_,
comments0_.Id as Id5_0_,
comments0_.Name as Name5_0_,
comments0_.Email as Email5_0_,
comments0_.HomePage as HomePage5_0_,
comments0_.Ip as Ip5_0_,
comments0_.Text as Text5_0_,
comments0_.PostId as PostId5_0_
FROM Comments comments0_
WHERE comments0_.PostId = 1 /* @p0 */</formatted-sql>

That is pretty easy to do, but then I hit a wall. This is good, but it isn’t nearly enough. I can imagine that most of NH Prof users will want access to the raw data in this format, so they can slice & dice it as they see fit, but I believe that I also need to provide a more readable form.

I started to look at how I can generate PDFs from this, and I run into a problem. I don’t have any experience doing such things, and while I don’t doubt that I could learn it, I don’t think that what is very likely to be a one off requirement is worth spending the time to really learn the topic.

Therefore, I decided to try something a little bit different. I uploaded four sample reports here. I want to see if someone can provide me with solution to convert those to a nice PDF format. 

I am going to give away one NH Prof license to the first one that can show me a solution that I can use with NH Prof.

The selected solution will also be featured in this blog, of course.

The small print: Just to be clear, some variation on the selected solution is going to end up within NH Prof. I don’t think it is a big task for someone who know what they are doing, and I believe that the NH Prof license offer is adequate compensation for that.

More posts in "Challenge" series:

  1. (28 Apr 2015) What is the meaning of this change?
  2. (26 Sep 2013) Spot the bug
  3. (27 May 2013) The problem of locking down tasks…
  4. (17 Oct 2011) Minimum number of round trips
  5. (23 Aug 2011) Recent Comments with Future Posts
  6. (02 Aug 2011) Modifying execution approaches
  7. (29 Apr 2011) Stop the leaks
  8. (23 Dec 2010) This code should never hit production
  9. (17 Dec 2010) Your own ThreadLocal
  10. (03 Dec 2010) Querying relative information with RavenDB
  11. (29 Jun 2010) Find the bug
  12. (23 Jun 2010) Dynamically dynamic
  13. (28 Apr 2010) What killed the application?
  14. (19 Mar 2010) What does this code do?
  15. (04 Mar 2010) Robust enumeration over external code
  16. (16 Feb 2010) Premature optimization, and all of that…
  17. (12 Feb 2010) Efficient querying
  18. (10 Feb 2010) Find the resource leak
  19. (21 Oct 2009) Can you spot the bug?
  20. (18 Oct 2009) Why is this wrong?
  21. (17 Oct 2009) Write the check in comment
  22. (15 Sep 2009) NH Prof Exporting Reports
  23. (02 Sep 2009) The lazy loaded inheritance many to one association OR/M conundrum
  24. (01 Sep 2009) Why isn’t select broken?
  25. (06 Aug 2009) Find the bug fixes
  26. (26 May 2009) Find the bug
  27. (14 May 2009) multi threaded test failure
  28. (11 May 2009) The regex that doesn’t match
  29. (24 Mar 2009) probability based selection
  30. (13 Mar 2009) C# Rewriting
  31. (18 Feb 2009) write a self extracting program
  32. (04 Sep 2008) Don't stop with the first DSL abstraction
  33. (02 Aug 2008) What is the problem?
  34. (28 Jul 2008) What does this code do?
  35. (26 Jul 2008) Find the bug fix
  36. (05 Jul 2008) Find the deadlock
  37. (03 Jul 2008) Find the bug
  38. (02 Jul 2008) What is wrong with this code
  39. (05 Jun 2008) why did the tests fail?
  40. (27 May 2008) Striving for better syntax
  41. (13 Apr 2008) calling generics without the generic type
  42. (12 Apr 2008) The directory tree
  43. (24 Mar 2008) Find the version
  44. (21 Jan 2008) Strongly typing weakly typed code
  45. (28 Jun 2007) Windsor Null Object Dependency Facility



There is a commercial porting of XSL-FO from AltSoft. We look at this in the past...


i also think that using html (with an old and simple table) it's the best solution, it's light, you can see it on every machine without installing external application (a browse is always installed), and if somebody want more it he can simply import the html file in openoffice or word, works on it and maybe export it as pdf.

you can do the conversion xml to html with a your simple custom class or using an xslt.


It seems to me that it will be very hard, if it's even possible, to create good readable reports for large amount of different data. And most of the time user doesn't need in all data received during profiling. Sometimes user will also need in some custom sorting or grouping of data. There are no way to generate a PDF with support of data's sorting, and grouping, and other interactivity. Even using JS it's not so trivial.

IMO, it will be better to create a report generation tool. This tool will use and interpret data from NHProf (probably in some custom format). In this tool user will be able to select necessary data, create charts, tables and so on, and also he'll be able to save the result in pdf and png formats.

It will be great if this tool will support some scripting languages - boo or iron python, and if it will contain some console window for data manipulation using scripting language. This will allow to select required information very easy.

As for example please look at NDepend. After loading all required assemblies the user can select any metric he wants using simple SQL-like language and do whatever he wants.

Job Samuel

I have used iTextSharp in past to generate PDF reports in my applications. iTextSharp has its own way of representing a pdf document in XML, it will be possible to transform your report XML to iTextSharp XML which can then be easily converted to PDF using iTextSharp

Job Samuel

Another way to do this could be as follows:

  1. Define the report as an NVelocity template - this report is basically is iTextSharp XML with NVelocity markup

  2. Set the context with report data and run the template through the NVelocity engine

  3. The output is a well formed XML according to iTextSharp XML representation for PDF documents

  4. This XML can be used to create PDF directly using iTextSharp dll

Just wanted to add that iTextSharp supports nice features like header, footer, images etc (if you want to add "Powered bu NHProf")


apply xslt then use this html to pdf ( http://www.html-to-pdf.net/Features.aspx ). (and I'm not working for them)

we're using this product like this:

list <items -> serialize -> xml -> apply xslt -> html -> pdf


We have used stand alone reporting services reports to generate nicely formatted PDF output from database input. The report itself works off of a dataset that you can set in code, so you can use whatever method to populate the dataset you want. The code to load the rdlc file, marry it to a dataset, then export to PDF is pretty straightforward. The key here is that you don't need to whole reporting services setup, but to use it in its standalone form.

Frank Quednau

+1 on xps...there is a clean connection from WPF to XPS. Take a piece of XAML, feed it the data you want to show and render the stuff to XPS. "Feel Lucky" with "render WPF to XPS" and you get the code snippet.

Agreed, XPS is probably a joke, but it's there...using WPF to express reports is certainly possible.


using iTextSharp ( http://itextsharp.sourceforge.net/index.html):

using iTextSharp.text;

using iTextSharp.text.pdf;

Document doc = new Document();

        string ns = "


        PdfWriter.GetInstance(doc, new FileStream("Report01.pdf", FileMode.Create ));


        Table table = new Table(3);

        table.AddCell(new Cell("Query Count"));

        table.AddCell(new Cell("Avg. Duration"));

        table.AddCell(new Cell("Short SQL"));


            .Descendants(XName.Get("query", ns))


            .ForEach(d =>


                table.AddCell(new Cell(d.Attributes().Where(a => a.Name == "count").First().Value));

                table.AddCell(new Cell(d.Elements().Where(z => z.Name == XName.Get("duration", ns)).First().Attributes("database-only").First().Value));

                table.AddCell(new Cell(d.Elements().Where(z => z.Name == XName.Get("short-sql", ns)).First().Value));




.NET 3.0 has native support for XPS. So we could define a printable report view in xaml and simply convert that to XPS. Or, we could define our reports in xaml and then use WPF's print features (which can print any visual) and let user decide if they want to print to pdf, xps or paper.


If i were you i would do exactly how office does it:

a free reportviewier that is native code and knows how to display the data

Save the data in raw form.

Chris Martin

Since you already have your XML defined, I would write a simple XSLT to transform it into iTextSharp specific XML and run it through the iTextSharp library.

Simple, done.


hm... people wants NHprof... many wants it.. :)


I don't see what you can do with a PDF. Who would want to read a PDF with queries and hit counts? People will probably generate reports to analyze and compare - not print (and to me a PDF is an excellent if your going to print and/or read something).

Why not:

  • HTML (for a friendly quick display)

  • CSV file (or OpenXML Spreadsheet) for analysis, once you get this into Excel the sky is the limit


Create an HTML report like SyncBackSE.

Shawn Wheatley

Agree with @Anaimi, maybe your report example is over-simplified, but it doesn't seem useful to me. PDF? For a list of SQL statements?

I also agree with @zihotki that for this report to be at all useful for a moderately sized application, you would need to offer the ability to drill down into the data, at which point you may want to look at a dedicated reporting component.

That said, I'd be happy to whip up some code for a free NHProf license :)

Ayende Rahien


If you want to do analysis, you can use the profiler itself to do the slicing & dicing.

The problem that I am trying to solve is "email queries to DBA so he can optimize them"


As a couple of others have suggested, doing it in Reporting Services is very, very easy.

You can do it from within the project, against the objects/collections themselves, and once you have the reports there, the controller comes with options to export to Excel (xls), Word (doc), xml, pdf, etc.

For WPF at the moment you'd have to host the controller in a winforms control, but that code is easy to do and I have a sample I could send through. This is going to be updated I believe in a future version and there is already a Silverlight compatible controller in Codeplex, I believe.

All in all, it gives you several options for output, can be adjusted really easy and is very familiar to people running MS solutions.

Andrea Balducci

@Tobin: The report viewer control can be used without UI at all. I'm using the MS .rdlc engine to generate pdf reports with WCF services. I'ts quite easy.

@Ayende: why not just expose a data dump and let us dev to write our own visualization plugin ? ;D


@Andrea: True, and could just use a textbox, or an image in a generic rdlc and pipe through the query (or as an image) into the generic report and do a dump to PDF from the backend without hte UI.

Saves any licensing issues - just would include a bit of bloat in requiring the extra library/install if required, especially since the whole control isn't necessary, but quicker to dev against, and stable.

Shawn Wheatley

@Ayende: if that's the case, I'd recommend going with "plain" HTML instead. We use Red Gate Software's SQL Compare tool, which if you're not familiar with it, can output a single HTML report that contains a comparison of two different schemas. We regularly send these files to our DBAs and they really like the layout.

Going this route, it would be easy to create a single HTML file that contains JavaScript for client side filtering (if desired), print style sheets (no where near as good as PDF, especially in IE), client side or server side syntax highlighting, and other features I can't think of right now :) Maybe a button to copy the raw SQL text to the clipboard next to each query? You could even embed IE inside NHProf to preview the report, without having to worry that the user doesn't have a PDF viewer for some reason.

This could all be achieved via XSLT or a templating framework (NVelocity maybe, as @Job Samuel mentioned). Would you be interested in something like this instead of a PDF solution?


Seconding the MS report viewer option.

I've only used it in a web application but basically we design the reports (RDLCs), then there are some calls to RenderReport() that allow you to get a byte array of the report rendered in either excel or pdf format (the other formats reserved for full-blown Sql Reporting Services).

This works well for one-click report generation when the actual viewing of the report isn't important (because then you'd use the actual ReportViewer in your UI instead of manipulating it behind the scenes).


I also have to question the usefulness of generating PDF reports since these are static objects that are not easily manipulated and it can be hard to get text out of a PDF in a useful manner. It's more like death by a thousand paper cuts.

If the intent is to send the report to a DBA for optimization, the first thing he/she likely wants to do is pull the into SQL Server Management Studio to run the queries, examine the execution plans, and maybe even run index tuning.

That said, I'd much more appreciate receiving either a SQL Server Project with all the queries or a set of SQL files that I can simply open with SQL Server Management Studio.

Ayende Rahien


This is based on my impressions more than anything else, but there seems to be more gravity placed on PDF vs. HTML

There is also a side issue, how to resolve handling things like images with HTML, which makes PDF much more compelling

Shawn Wheatley

@Ayende: A couple options are available for image "embedding" in HTML:

1) Red Gate side steps the issue by hard-linking images that they serve up themselves. This means the vendor (you) would need to ensure that the URIs must not change ("Cool URIs don't change"--Tim Berners-Lee, http://www.w3.org/Provider/Style/URI)

2) Some browsers, although I'm not sure about IE, support inline "data://" URIs, which can contain a MIME type and Base64-encoded data. I've seen inline images done this way as well.

That said, it sounds like you're still more interested in PDF. My suggestions would be the iTextSharp route (if you're interested in more OSS) or using a reporting engine like SSRS, Crystal (yuck) or Active Reports. I'm sure there are other reporting solutions out there as well, but those are the only ones I have experience with.

Fred Hirschfeld

I typically use Microsoft Reporting built into .NET and have a quick sample created for you to look at. I will e-mail you the solution sample.

James Farrer

This has been a very interesting discussion on reporting and gives me a few pointers for the work I'm doing!

I would second what Lothan said

"That said, I'd much more appreciate receiving either a SQL Server Project with all the queries or a set of SQL files that I can simply open with SQL Server Management Studio"

Obviously you would probably want a HTML report, but the ability to export to .sql files in a .zip would be great - you could include at the top of each .sql all the info about the query in comments and then have the sql query good to go below - could even have a "Developer Comment" section in the comments and allow the developer to enter any additional comments they'd like to


Johannes Rudolph

Im sure you may have already evaluated my suggestion to use combit List&Label, however i want to point out some other things about it:

Im currently working on an application that uses List&Label to print ~10.000 Reports per month, varying from simple lists, over invoices to analysis charts. There are about 20 kinds of different reports, all of them share a common subset of templates, e.g Paper layout, letter with adress etc. Every Report that gets printed will automatically be archived in an FileShare based Report Storage.

Together with modelshredder (see link in my previous post) one can skip the xml creation, you can directly create a reporting model from your domain model.

Whether you want PDF or HTML would be no problem at all, both formats are possible without ANY further work. L&L has strong support for Realtime WYSWIG editing with production data, your users would gain the ability to design how many different reports or analysis they'd like. I am not aware of any other solution that is this flexible in terms of output formats and report design.

It remains to say that it isn't free, however licensing is per developer using it and you may redistribute freely.

Johannes Rudolph

Just wanted to let you know that i have mailed you an example solution.

Josh Perry

Hey Oren,

I agree with the others that HTML is the way to go. I started mocking up a small self-contained HTML page that mimics nprof. You can see it here: http://6bit.com/img/nprofreport.html

I was going to add the ability to view the resulting rows and a session statistics view as well but I thought I'd get your feedback before I put anymore work into this.

All of the javascript, styles, and data needed to view the report is self contained in the html file you merely need to serialize the nprof session to JSON instead of XML and embed it in the html file. The user could then email or post the html file somewhere for a DBA or anyone to view.


Oren, Where is the answer for this? did you find a solution?

Ayende Rahien

Yes, I did.

It will show up soon in a build near you :-)

Comment preview

Comments have been closed on this topic.


  1. RavenDB 3.5 whirl wind tour: You want all the data, you can’t handle all the data - about one day from now
  2. The design of RavenDB 4.0: Making Lucene reliable - 3 days from now
  3. RavenDB 3.5 whirl wind tour: I’ll find who is taking my I/O bandwidth and they SHALL pay - 4 days from now
  4. The design of RavenDB 4.0: Physically segregating collections - 5 days from now
  5. RavenDB 3.5 Whirlwind tour: I need to be free to explore my data - 6 days from now

And 14 more posts are pending...

There are posts all the way to May 30, 2016


  1. RavenDB 3.5 whirl wind tour (14):
    29 Apr 2016 - A large cluster goes into a bar and order N^2 drinks
  2. The design of RavenDB 4.0 (13):
    28 Apr 2016 - The implications of the blittable format
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series



Main feed Feed Stats
Comments feed   Comments Feed Stats