SSRS Consultant Tip: What SSRS Does Well (And What It Doesn’t)

I recently presented on SSRS at the NAV Focus Conference in St. Louis. As often happens at these events, I wasn’t exactly sure who was going to show up and what they would already know. (Especially at an event like this, which had a development track but with no specified levels.)

In any case, it became clear to me that many folks in the audience could benefit from a series of posts aimed at a “SSRS 102” level. They’ve mastered “SSRS 101” material—they can create a basic report and know what data source, datasets and reports are—but now, they’re ready to move up.

To move from a SSRS 101 level of understanding to a SSRS 102 level, you have to understand what SSRS does well—and what it doesn’t.

Like most software tools, SSRS can do many things. Some of those things it does really well. Other things, not so much. When you get your knowledge from books (and I read and scan through lots of books), you tend to learn about what you CAN do with a program, rather than what you SHOULD do. And when it comes to SSRS, this is an important distinction.

In my next few blog posts, I’m going to try and take you to the next level of understanding. Everything I describe will stem from my cardinal rule of SSRS performance happiness:

SSRS is an excellent tool for presenting and distributing data. It’s NOT a good tool for querying and processing data. That’s what SQL Server is for.

Let’s dig a little further into the implications of this rule:

• You want queries to return ONLY the data you need for SSRS. (For more on this, see my post “SSRS Tip: Put Parameters in Your Query, Not Your Filter.”)
• You should mostly perform aggregation and sorting in SQL server and not in SSRS. (Which we’ll cover when we discuss detail reports.)
• If you need to combine data from multiple sources, it’s better to do that in SQL Server rather than have multiple data sources in one SSRS report.

In other words, use SQL Server for data crunching. Save SSRS for reporting.

Following from the above, there’s one more key point I want to make: you cannot build efficient, easy to maintain SSRS reports without someone on your team knowing T-SQL and having access to the database.

Yes, there’s certainly a role for folks with less raw database skills on reporting projects.

Certainly, at Red Three we’ve successfully trained many super users on SSRS. But even skilled SSRS super users aren’t going to write the SQL themselves. Instead there going to take those queries and create many different kinds of pretty presentations.

In my next post, I’m going to elaborate on my reasons for using SSRS only for what it does best.