Ayende @ Rahien

Refunds available at head office

Challenge: NH Prof Exporting Reports

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:

image

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>
</query>
</unique-queries>

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.

Comments

Mic
09/15/2009 11:54 AM by
Mic

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

marco
09/15/2009 12:14 PM by
marco

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.

zihotki
09/15/2009 12:17 PM by
zihotki

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
09/15/2009 12:23 PM by
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
09/15/2009 12:32 PM by
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")

Dan
09/15/2009 01:14 PM by
Dan

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

Jeremy
09/15/2009 01:24 PM by
Jeremy

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
09/15/2009 01:44 PM by
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.

Magesh
09/15/2009 02:02 PM by
Magesh

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

using iTextSharp.text;

using iTextSharp.text.pdf;

Document doc = new Document();

        string ns = "

http://nhprof.com/reports/2009/09";

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

        doc.Open();

        Table table = new Table(3);

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

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

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

        XDocument.Load(@"ReportExports\unique-queries.xml")

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

            .ToList()

            .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));

            });

        doc.Add(table); 

        doc.Close();
Rob
09/15/2009 02:42 PM by
Rob

.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.

liviu
09/15/2009 03:19 PM by
liviu

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
09/15/2009 03:21 PM by
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.

Dan
09/15/2009 03:25 PM by
Dan

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

ANaimi
09/15/2009 04:51 PM by
ANaimi

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

Mr_Simple
09/15/2009 06:46 PM by
Mr_Simple

Create an HTML report like SyncBackSE.

Shawn Wheatley
09/15/2009 08:45 PM by
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
09/15/2009 08:50 PM by
Ayende Rahien

Shawn,

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"

Tobin
09/15/2009 08:53 PM by
Tobin

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
09/15/2009 09:20 PM by
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

Tobin
09/15/2009 11:15 PM by
Tobin

@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
09/15/2009 11:28 PM by
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?

Ray
09/15/2009 11:47 PM by
Ray

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).

Lothan
09/16/2009 12:12 AM by
Lothan

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
09/16/2009 12:31 AM by
Ayende Rahien

Shawn,

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
09/16/2009 01:00 AM by
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
09/16/2009 01:50 AM by
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
09/16/2009 08:58 AM by
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

James

Johannes Rudolph
09/16/2009 08:52 PM by
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
09/16/2009 10:53 PM by
Johannes Rudolph

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

Josh Perry
09/20/2009 11:20 PM by
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.

JR
10/23/2009 10:48 PM by
JR

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

Ayende Rahien
10/23/2009 10:58 PM by
Ayende Rahien

Yes, I did.

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

Comments have been closed on this topic.