I spent time working with Reporting Services again today on a client site. The complexity of the reports we were working on reminded me that I really, really don't like seeing T-SQL scripts (or really any business logic) embedded in reports.
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 Access-based applications and have then lost control of the ability to ever change anything in the database because of the hundreds of little applications and reports that they might then break. I spend a lot of time talking to them about how to regain control of the situation.
Reporting Services is another area where this can happen. Fortunately, 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 instead of having T-SQL embedded in it. I see the advantages of this approach as:
1. Refactoring the database is easier.
2. Unit testing of the code is much easier. You can easily build tests to just call the stored procedures. While possible via the web service interface, it's much harder to test the reports directly and requires a different skill set.
3. It allows the UI to be built by one person and the stored procedures to be built by another.
4. It's easy to deal with 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).
5. I may well get benefits on procedure cache efficiency.
6. I can use the same stored procedure on multiple reports. 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. (Fortunately in SQL Server 2008, it looks like I'll have more flexible Excel rendering options).
— "Fortunately, the reports are typically located in a single location"
… unless you have users keen on using Report Builder to create and save custom reports on their desktop 🙂 To make the refactoring even more interesting, they base these reports on a report model whose definition needs to change.
Argh…
Multi-valued parameters are annoying when used with stored procedures.
Any chance SSRS 2008 will be able to use table value parameters?
Agreed. I tend to use comma-delimited lists for these. No idea on support for TVPs in SSRS 2k8 but that's a good question. I'll ask.
Thank you for the post.
Totally agree, I'd love to use SP for SSRS if it wasn't for the multi-valued parameter, and the frequent #temp table error during the report creation using wizard
If you use #temp inside the SP, the wizard won't let you run it, so I constantly to have put in a dummy query or the SP SELECT into the wizard, to generate the report first, then switch data source to SP after
jerryhung,
Although I don't use the wizard, I've had problems with stored procedures using temporary tables as sources for reports, until I found on a blog somewhere
SET FMTONLY OFF
Put that at the top of your procedure and temporary tables can even be the means of output.
On the subject of multi-valued parameters, I pass the demited list to a table-valued function that outputs it as a table. This can then be used as a table within the stored procedure, typically in a 'where variable in (select * from fncConvertMultiList(@DelimitedList))' clause.
Great tip Brian !
Can we use a stored procedure in the report model? If we can do you recommend doing this or not?
Thanks
Hi Sean,
Not in any way I know of as yet. The report model requires a dataview source and that is built over tables or views. I think it's a signficant limitation that it can't at least do non-parameterised sprocs.
A view is the closest you can currently do, even though it's not the same.
Regards,
Greg
just refresh the dataset