One of the biggest challenges that faces our project is reporting.  Most of the custom requests that are made are regarding reports and currently when each report is created, it has to be done by hand by a developer.

This creates an extreme amount of strain on resources especially within a company that is not that large.  When I read The Enterprise and Scrum, one of the trending patterns the author recognized when consulting companies for Scrum was they were spending an unnecessary amount of time on support and bug fixing their product.

I wanted to find a way to avoid this and make reporting as easy as possible.  The primary requirements we wanted to have were:

• Integration with the 3.5 framework well
• Ability for a non-technical person to create or change reports
• Ability to create reports 100% outside of Visual Studio environment
• Reports can be portable
• As cost effective as possible
• Ability to create complex data structures and pivots

Looking at all of the reporting options and what I had worked with, the following reporting suites came to mind:

• Crystal Reports
• Data Dynamics Active Reports
• Microsoft SQL Reporting Services

Crystal Reports

I have worked with Crystal Reports in the past but have had a lot of limitations with it.  Especially if you’re using the version that ships with Visual Studio, it has serious performance limitations.  Here is what an MSDN article says regarding this:

While you may allow access to the runtime software by many users, the version of the runtime software provided with Crystal Reports for Visual Studio .NET is a performance-limited edition intended for development purposes and use with Server/Web applications accessed by small groups. If a report is accessed while the server is at its performance limit, the request will be delayed until an existing request finishes. When a request is delayed, the server writes a warning message to the event log. After a finite period of time, the request will time-out. When this happens, the server writes another warning to the event log and throws an OutOfLicense exception.

This obviously would become an issue since we want to spend the least on licensing costs.  Also as far as I know Crystal Reports doesn’t have an independent report designer.

Data Dynamics Active Reports

We’ve used Active Reports on many projects and it’s actually quite a great reporting tool.  We use it to design our ticket reports which need a lot of flexibility since they’re printing out of a Boca printer and require custom ticket stock.  But the tickets themselves are pretty straight forward and don’t need complex reporting data structures.  The grouping logic in Active Reports isn’t bad but very complex groups seemed to not work the way I wanted.

Also the report designer component would need be built as well using their control which we don’t have time to do.

Microsoft SQL Reporting Services

I have been wanting to really spend some time with SQL Reporting Services for a while, I just haven’t had time to do so.  Now that I finally have I am glad I did.

SQL Reporting Services is a really great reporting component Microsoft has provided.  Since SQL Server 2005 Microsoft has shipped Reporting Services free of charge with your SQL Server license.

Sounds good to me.

What about complex reporting data?  Most definitely.  Reporting Services provides a control you use called a “Tablix” which allows you to build a very complicated grouping structure within your report tables.  It’s really, really powerful.

Also, almost every property on every control can be controlled via “Expressions”.  These are basically VB style code snippets that allow you to dynamically set groups, visibility, colors, you name it.

Microsoft also provides a report tool to design your reports 100% outside any development environment, for free.  This is called Report Builder.  It is an awesome tool and provides even more than you could find in the Visual Studio version of the report designer.

And lastly, we can embed the actual SQL query within the report file making it portable and transferable between different customers.

LocalReport vs ServerReport Snafu

After I initially did my tests, all was good.  Except one thing.  I found out that when you want to have your reports portable (saving to a local .rdl file), the report has a few problems:

• In LocalReport mode, the report will NOT save any connection information with the report
• But in the flip side, we don’t want to save connection info since the reports are supposed to be portable
• There is no way to dynamically set the connection at all in LocalReport mode
• Microsoft assumes if you’re in LocalReport mode, you would pass your data objects already constructed to the report.

But if this is the case, what is the point of designing the query within your report?  You can’t because if you need to allow the users to construct the query there is no way you can pass it from the server.

Creating a Custom RdlReport

What I ended up doing was writing up some sophisticated code to handle local reports.  This is what it does:

• Report criteria page dynamically parses the selected report .rdl file and displays the parameters to the user
• User selects parameters and runs the report.  When this happens, I automatically parse out all queries contained within the report, and add them to a Queries enumeration, also with each parameter within the query
• Each query is dynamically constructed, with all parameters and ran on the server
• The resulting data objects are then passed back to the report with the LocalReport.DataSources enumeration

In a nutshell, this is how we are dynamically running all report queries on the server and binding them back to the report.  Not including additional parameters being passed.  Then the result is being rendered to the client either in PDF or Microsoft Excel.

What About CSV?

One last requirement we had was we also need CSV option.  Crap.  But actually, after thinking about this for a second it was actually pretty easy.  All I need to do is render the Excel, then use OleDb to read the contents, and create a custom CSV by just parsing the spreadsheet.  Then stream this CSV to the response stream.  Problem solved.

This phase is wrapping up at the end of this year but I think this solution will be a very promising and flexible option.  I will try to post some code here soon to explain more in detail how I worked with the report structure dynamically.

• BROWSE / IN TIMELINE
• « 2010
• » Favorite Places to Eat in Fresno

• BROWSE / IN Development
• « Setting Windows Mobile Device Backlight with .NET Compact Framework
• » 59 Days of Code