Using a stored procedure as a source for PowerBI Paginated reports

If the reports that you are building do not need to be interactive, you can use Microsoft Paginated Reports (or SSRS). One of the benefits of a paginated report is that it’s source(s) can be stored procedures. I really, really like that option.
In Microsoft SQL Server stored procedures are an incredible programming tool. They have numerous benefits which I won’t get into here but suffice to say I use stored procedures wherever possible. You can have complex logic executed in a more efficient manner than say a handful of CTEs that would be required if you needed to do this in a single view. That result is generally stored in the procedure in a temporary table which needs to be selected back at the end of the procedure. The use of a temporary table requires a slight modification to your procedure, or the report builder will not recognize the output.

At the beginning of your procedure (I usually put it as the first line of code after my comments) you need to put the following:

SET FMTONLY OFF

Now if you look in the Microsoft documents it tells you that the FMTONLY flag is deprecated and gives you a handful of alternatives. I asked CoPilot how I would use these specifically for Report Builder it suggests not using select into #table and either explicitly declaring that temp table or using table variables (which are explicitly declared), but when pressed it said that is not foolproof and the suggested method is the FMTONLY flag.

I will do some additional research myself into declaring # tables ahead of time and the use of table variables, but for now I am comfortable using this. I don’t foresee Microsoft scrapping Report Builder anytime soon.