Welcome to another week of SQL University. I will continue to step outside my comfort zone by giving you some advice on how to get started with SQL Server Reporting Services. I am by no means an expert, but I have dabbled with SSRS from time to time. I know just enough to be dangerous, really. So let’s get started!
tempdb
The first thing to understand about SSRS is that it is a different service from the database engine. The second thing to understand about SSRS is that despite the first letter in the acronym it doesn’t mean that your DBA is an expert in all things related to SSRS. Many products start with the letters ‘SQL’ and as a result people think that because of those letters then their DBA is an automatic expert.
Not true. You may have a great DBA, but that doesn’t mean that they know how to best configure SSRS, how to make it scale, or how to write reports for your analysts. While it may not take them a lot of time to get up to speed, it should not be handed to them and expected that they will immediately know all the answers.
msdb
OK, now that we have that out of the way, let’s get some work done. First thing you want to do is dive in and start building some reports. Reports for what? It doesn’t really matter, but I am sure that as a DBA you have enough data laying around that you would want displayed in a report. Still need an example? Fine, build a report that lists all of the servers and editions of MS SQL that you have. I am assuming you have this data in a table, or in a file somewhere, and you will use this as a source.
It is important for you to start rooting around and building something. This way you can have experience as a user yourself which will be valuable when you provide support for others.
model
At this level you are going to have an awareness of performance and scalability. You will want to be aware that some reports are best written with an underlying stored procedure, that some are best with common data source strings, and that others will have requirements for attachments and emails.
You are also going to want to have a standard in place with regards to report deployments as well as subscriptions. Your end users need to know that their report is returning the right data, but they are also going to want that data to be returned fast.
master
If you want to be a master of SSRS then I have but two words: Report Builder. If you can master that, then you have truly mastered SSRS. To use Report Builder effectively means that you would have also mastered building models of your data, which would also mean that you have gained extensive knowledge into database design. These are all good things to have.
resourcedb
You can find a lot of great information on SSRS over at Pragmatic Works. In addition to their training, these individuals are good ones to follow as well:
Thomas, thanks for listing my blog as a resource – I appreciate it very much!
Melissa
Tom, you just motivated me to crank out a daily scheduled SSRS report to email the results s of our daily checklist evaluated with CMS and PBM to Management and the DBA Team.
On another note, I wouldn’t recommend Report Builder for two reasons. First, Report Builder has no support for source control. Second, I can only speak for myself but BIDS is a lot easier to use then Report Builder. It also gives you more control.