Sql Server Reporting Services vs Reporting Via .NET Application

Question

My boss wants me to create a few reports in the near future and I think he wants to use SQL Server Reporting Services to deploy the reports. I'm not so sure this would be such a great idea considering that we are a pretty small organization and I cannot see us making good use of or needing the features that this solution offers such as setting up Users, Groups and Subscriptions.

Although I have not used SSRS before, I've watched a 3-day webinar on it and it looks like it's one of those things that are nice and OK for simple situations but becomes a pain & too restricted when the requirements become more complex. I would much deploy the reports as local reports (.rdlc) in a .net application because:

1. I would much rather process & format data with .NET then SQL. Sure you can use CLR, but this route just seems like it would be harder to maintain and less ideal than just processing data as I usually would in a .NET application.
2. Limitations on UI when adding parameter controls - if I remember you do not have much control over the layout.

So I guess my question would be in what situations does SSRS work good, what situations does it not work good? Are my points valid or am I just being a skeptic?

Answer 1

You can set up the security for Reporting services just like in windows, so if a security requirment changes you can just modify the security in RS. If it's embedded in an application you'd have to update the application (i haven't done this so i don't know all of the steps) and then redeploy the application to update the security.

Answer 2

Your points are valid.

For a smaller app, I'd consider using a ReportViewer control in an ASP.NET app if you don't need all the bells and whistles. Even from a maintenance perspective: you only have to manage one app. My team is planning to stop using SSRS.

I know some of our sister team have complex reports and structures, and need the bells and whistles.

Answer 3

I use a little of both, and have found that there are trade-offs with each approach.

• For whatever reason, the designer for .rdlc is a little different than the designer for .rdl. It can get pretty confusing when an online example makes assumptions on what your designer is.
• I generally favor SSRS-deployed reports if I'm trying to be client-agnostic, as .rdlc-based reports require you to provide the client.
• I generally favor .rdlc-based reports for stand-alone applications, especially for clients that do not have a data center. These tend to be applications where both the application and the database are on the client machine.
• I like LINQ, and find it easier to use as a data source for .rdlc-based reports.
• I have a love/hate relationship with .rdlc-based reports when it comes to refactoring. Keeping your data structures in a separate library than your reports is important; otherwise, changing a property name will cause your build to fail on account of the report, but the new property won't be available on the data source for the report until you build.
• Controlling the client (.rdlc-based report) gives you limitless flexibility on how you present and collect parameter values, which is pretty nice.

At any rate, I doubt there is any dogmatic approach you should stick to, other than "do what makes sense." For me, in practice, I use .rdlc-based reports for small client applications, and deploy enterprise-grade reports to a SSRS server.

Good luck!

Answer 4

Your instincts are good, keep using them.

Many people fall into the trap of pushing complex business logic into SQL and reporting tools. The proper place is in the ETL. Don't be intimidated by the term it covers simple ad-hoc perl scrips to complex SSIS. Even then 80% of the time people will use SSIS to only Extract and Load data saving the transform for the report at run time (Why are these reports so slow?!).

Even if you are forced to serve the data via SSRS keep your transform layer separate from the report in the tool/language of your choice, keeping your sql simple and concise.

For a small shop aspx is probably fine but keep this in mind. You get allot of free stuff from SSRS with security and export to excel being a huge plus for you boss. Reports are also a black hole of busy work. Your first handful of reports quickly multiply for different users and different business reasons and become unmanageable. If you setup a good SSRS base you can migrate the work to someone else when the time is right.

If you are more interested I suggest reading up data warehousing.

One more thing. Be aware of running reports against live data. Reports typically have a different performance profile than OLTP queries. OLTP = a few records a time where Reporting (DW) queries sometime require full table scans and can cause locking issue if not setup properly.

Answer 5

No one said anything about Report Builder 2.0 or 3.0. It is a great app just to build reports without any need of SSRS or anything. Just fire it up and set it up to consume whatever data source you have available and you are good to go. I mean, you can easily compile this report in no time. Think about it.

You definitely don't need another custom-tailored .NET solution for this.

Getting Started with Report Builder 3.0