SQL Server Reporting Services tips and tricks
Licensing models can sometimes make database modeling seem trivial. Per processor or per seat? Single core, dual-core, multi-core processors? Multiple instances on one server? Virtualizing servers on a single server? It can get very confusing, very quickly.
Recently, I was asked by a former student about licensing for a SQL Server Reporting Services instance. In the proposed scenario, the Report Server would be installed on one server and the back end database would be installed on a separate, remote server. How many licenses of SQL Server are required?
In short, each server where a Business Intelligence component is installed requires...
Sometimes, SQL Server Reporting Services users like to see what could be considered multiple reports in a single SSRS report. In most cases we can easily accomplish this using more than one data region in the report. If not, we can always use a subreport.
Let's say we have a report called the Top 10 Report. In it, we wish to see the top ten products sold by order quantity. We'd also like to see the top 10 resellers of our products, again by order quantity. This could be accomplished using multiple data regions or using two subreports.
We'll use two...
I was recently doing a little research on scaling out SQL Server Reporting Services and found these articles. I thought I'd share them with you here (and so I could quickly find them again later if I needed to).
Got any other useful resources or white papers? Please share them as a comment below.
While teaching a Microsoft SQL Server Reporting Services class in Charlotte, North Carolina, this week, one of the students asked to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource. Here are some screenshots from the demonstration.
The first step in using an Excel spreadsheet as a datasource is to create a System Data Source Name (DSN) for the spreadsheet. Open the ODBC Data Source Administrator from Control Panel | Administrative Tools.
Add a new System DSN, providing a name in the Data Source Name box, and selecting the workbook that you wish to...
In a prior post, I demonstrated how to use conditional formatting techniques to change the background color of every other row in tabular report. In that example, I used the Immediate If (IIF) function to change the BackgroundColor property of the odd rows to silver.
The IIF function only allows for two states. In my example, the odd rows were silver and the even rows had a white background color.
In the comments section of that post, one reader, Wally, asked to have more than two states, silver, white, and red. Another reader, Darin, replied that you can nest your...
One common question from many of those who are relatively new to Microsoft SQL Server Reporting Services is how to alternate the background color of detail rows in a table data region.
Although I'm sure samples of this have been posted elsewhere on the Internet, I thought I'd share a common technique in the hopes that those searching for a solution will find it, either here or elsewhere.
Let's consider an example. We have an employee phone list report as shown below.
To make the report a little easier to read horizontally, we'd like to change the background color of every...
Business Intelligence Developers Studio (aka BIDS) for Microsoft SQL Server 2005 Reporting Services makes creating engaging and dynamic reports nearly trivial in some respects. You can create drill-down and drill-through reports with just a few mouse clicks.
You can also create reports that provide interactive sorting. Users can click on column headings and have the report automatically sort by that column. This question regularly comes up as I'm leading training sessions and occasionally in the online forums. So, let's walk through a graphical example to demonstrate.
Here's an example of a contact phone list report created in BIDS. It lists...
Thanks to everyone who attended my SSRS class in Charlotte this week. As promised, here is a list of online resources that may prove useful to you as you work with Reporting Services.
Reporting Services Tutorials
Reporting Services Samples on Codeplex
Reporting Services Developer Info Center
Beginning SQL Server 2005 Reporting Services Simple-Talk article series by Steve Joubert
Microsoft MSDN Reporting Services Forum
TechNet SQL Server TechCenter
Additionally, the following...