As I started this series, I emphasized the importance of using SSRS for what it does best, not for everything it can do. I ended the post by noting the importance of having someone on your team with knowledge of T-SQL and access to the database.
Whenever I make this point, whether in presentations or with colleagues, they ask me to back up my argument. So let me lay it out for you here:
1. You can’t assume that SSRS will be your only reporting tool
Over the years, many IT shops have tried to control the number of ways folks see data. And this control makes sense, in theory. There’s value in having at least some of your staff up to speed on your preferred tool. But in reality, end users like the tools they like, regardless of anything you have to say about it.
Because of this human proclivity to use whatever damn tools we want, a more realistic approach is to say, “If you’ll let me (or someone on your staff who knows what they’re doing) develop your queries, you can then use whatever tool you want to make your presentations pretty.”
Those tools could be Excel, or Excel with an add-in like Jet Reports, Power BI or Tableau. Which tools they choose doesn’t really matter. If you build the logic once—and know it’s good—you’ll have a greater sense of comfort (if not complete ease) when that logic gets used with other tools.
At the least, you’ll be reasonably confident that the data is accurate.
2. Debugging in SSMS is much, much easier than debugging in SSRS
Microsoft makes some great development tools. I work with both SSMS and Visual Studio on a regular basis. However, even though SSDT (SQL Server Data Tools) is an add on to Visual Studio, it isn’t that nice to work with if you’re developing queries while building reports.
Indeed, just this week I found that the query designer in SSDT isn’t just hard, it’s sometimes wrong. I had copied code from SSMS into the Query Designer. I wanted to quickly see some results. But when I did, I realized that SSDT had rewritten my query and actually changed my logic. But I digress.
Further, I find it’s crucial to fully verify data before formatting. Therefore, I’ll often write my queries, dump output to Excel and verify—all before I get around to creating reports (or passing the data to someone else to create reports). This process is much, much easier in SSMS, especially if you, like me, buy inexpensive tools like SQL Prompt to make development easier.
3. Six months, one year or two years later—you can still understand what happened
My point isn’t to just put some code in SQL server. The idea is to do most of your work in SQL server. I’ve seen situations where the report developer started with a view (or stored procedure) on the database, and then created all kinds of additional calculations in the dataset.
Then, six months or two years later, something needs to be changed. At which point, the developer (or even worse, a new developer), will spend days, weeks or months clicking on formulas, trying to figure out where the calculation came from.
In this respect, SSRS isn’t any worse than many other tools. (I’m thinking of the many years I spent with Crystal Reports). But still, if you don’t want to create “write only code,” it’s better to get your stuff in T-SQL and store it as a view, stored procedure or even datamart in your database. This way, it’s much easier to figure out what happened when you open up your report years later.
In my next post, I’ll delve into further into this topic of how to store code on the server.