SSRS and Fabric Paginated Reports: Avoid Hiding T-SQL in Reports

SSRS and Fabric Paginated Reports: Avoid Hiding T-SQL in Reports

I often work with reports either in SQL Server Reporting Services (SSRS or now Power BI Reporting Services) and Fabric Paginated Reports. At a site this week, the complexity of the reports we were working on reminded me that I really, really don’t like seeing T-SQL code (or really any complex business logic) embedded in reports.

Don’t make refactoring difficult or impossible

DBAs tend to be considered a conservative bunch. One thing they’re usually conservative about is refactoring their databases. In many cases, this is because they have little idea what they will break when they make database changes.

I’ve seen countless organisations that have allowed direct access to tables from client-based applications and have then lost control of the ability to ever change anything in the database. When they make changes, hundreds of little applications and reports might break.

And they have no way to know which things might break before they do, yet they know they’ll be blamed when it goes wrong, because they made the change

So what do they do? They get more and more hesitant about ever changing anything. And the irony is that when I talk to the development teams, they say they work this way because they needed to be agile, yet what they’ve built is the absolute opposite of agility.

I spend a lot of time talking to them about how to regain control of the situation.

Reporting Services/PBI Report Server/Fabric Paginated reports

Reports in SQL Server Reporting Services, Power BI Report Server, or Fabric Paginated Reports are another area where this can happen. Fortunately, at most sites, the reports are typically located in a single location. When a database change is needed, at least you don’t then have to search the enterprise for reports that might break.

However, life is much simpler if all the report does is call a stored procedure to retrieve the data, and then just render it, rather than having T-SQL embedded directly within the report. I see the advantages of this approach as:

  • Refactoring the database is much easier. Most of the code with any complexity is already within the database.
  • Unit testing of the code is much easier. You can easily build tests to just call the procedures. Even if the updated version doesn’t render the reports perfectly, it’s unlikely that you’ve actually broken the reports. The alternative is to test the reports directly. While possible via the web service interface, it’s far harder to test the reports directly and requires an entirely different skill set.
  • It allows the UI to be built by one person and the stored procedures to be built by another. It’s really common for the people in the organization who are good at designing the layout of reports, and the people who are good at getting data out of a database efficiently, are entirely different people.
  • It’s far easier to control report permissions. Typically I create a Reports schema in the database and grant EXECUTE permission on that schema to the reporting users groups. That way, I don’t have to manage individual permissions on the stored procedures and I don’t have to grant permissions on the tables (or perhaps views).
  • I might well get benefits on procedure cache efficiency.
  • I can reuse the same stored procedure on multiple reports. I’m not writing code to load a list of countries, or a list of business categories, etc. more than once. I just use the same procedure in multiple reports.
  • I can easily produce totally different versions of the same report. I quite often find I have to write each report twice: once to look nice, the other to just dump the data into Excel format. Typically, when clients ask for Excel output, they just want the data, not the pretty formatting. I can use the same procedure for both versions. (While it is possible to have expressions based on the rendering target, in practice it’s often easier to have different version of the report.

Don’t hide T-SQL

Organizations end up far more agile and able to adapt to change if T-SQL isn’t hidden all over the place: in reports, in Excel spreadsheets, in Access databases, in custom applications, etc.

2026-04-18