Best reporting solution on top of SQL Server?

Question

I'm looking to build a reporting portal on top of our database which is SQL Server 2008 R2.

It's been suggested that I use SSRS, and I've played around with it for a few hours. I found it very easy to point it to a table and have it generate a table/graphs for you. It could all be done in a few minutes, and that's great.

I had a hard time customizing the report to make it look nice. I thought it was like using Word to design a website, the customization was pretty limited. I have experience in web development using ASP.NET with jQuery, and I'm currently thinking that I could make the portal much faster if I could use my experience with those technologies.

I'd like to get advice on which path is best for my project.

• Queries to the db are fairly simple, but not trivial. I have no problem building the data and business layers myself by using Linq-to-Sql.
• I do not need print support
• I need an emailing system that sends out reports every day, which I can also build myself (it doesn't need to let users subscribe to reports)
• Don't need the flexibility of having users create their own reports. This is only for 2-3 different reports.
• I enjoy the development experience using web technologies and feel comfortable building a pleasant user experience with jQuery and plugins. I fear that using SSRS, I would be limited to something ugly and not "fun" to use.
• I want this project to be up in 2-3 weeks. I can't afford to re-invent the wheel for everything, but I also don't want to spend most of my time asking questions like "how do I make a fade-in effect in ssrs?"

How easy is it to customize the UI? Are there good examples online of what can be achieved? Does SSRS have some advantage that I could hardly get by writing my custom solution? I'm thinking in terms of application performance and overall functionality.

Thanks for your input.

Answer 1

I use 2 main reporting tools (Business Objects and SSRS) and also Microsoft Access. I can honestly say that I prefer SSRS for 95% of the work I do. The users really like all the drill through options you can do. For example lets say you have a chart showing widgets sold, you can click on that bar of the bar graph and have it drill to a report showing a breakdown of which widgets were sold.

This ability of going from a “high level” to low level detail is really powerful to the users. As for looks, I quite like the default controls that ship with report builder 3.0, they have come a long way and just look what is around the corner with project crescent

http://blogs.msdn.com/b/sqlrsteamblog/archive/2010/11/09/a-glimpse-at-project-crescent.aspx

Answer 2

I have built custom web interface to a suite of SSRS reports. You can easily do this, and submit the gathered report parameters to Reporting Services, and have it render the generated report to a web page, all as if the user had used the SSRS interface to begin with.

Some comments i would make:

• let SSRS handle the reporting side of things. It has all the power and features you could need. And its free. And tested. And extensible. And renders to multiple (printable) formats.
• you could maybe look at buying a set of controls designed to be used with SSRS to get even nicer chart output
• you can build your own (parameter gathering & report triggering) interface within three weeks, but the schedule will be tight
• you can't use jQuery within the rendered reports. A lot of the HTML within the rendered report is hardcoded template type code (styles etc are embedded into the report rather than linking to an external file)
• you don't need jQuery fade-in panels to make your reports pretty :)

Answer 3

So, as you know SSRS allows web and winforms output, printing, and all that. But your users will undoubtedly end up bugging your for PDF and Excel exporting, which is also built-in.

It's also a lot easier to edit the reports without having to alter your app and as you mentioned it has the emailing built in.

SSRS is quite powerful if you play around with the grouping and parameterization. The designer is a little clunky as you make more complicated reports, but I've always been able to design what I want. I've not used Crystal Reports but presumably it's even more powerful but it is another thing to learn and obtain.

Answer 4

I know it's probably not a WEB-BASED technology but I would definitely give Crystal Reports a try. Specially considering your time constraints...