Create short lists using the LookupSet function

Sometimes you have a short list of values that you really don’t want to show on individual rows; you’d rather just concatenate them into a list, separated by commas, and put them all in a single row. For example, suppose instead of listing the states in each U.S. Census region each on their own row,…

3

TechEd session: Tips and tricks for building rich reports

Here’s a recording of my TechEd 2012 North America session last week in Orlando: Tips and Tricks For Building Rich Reports The entire session is based on Report Builder 3.0. All content is relevant to both SQL 2008 R2 and SQL 2012. Topics covered: Use a tablix to organize data how you want it Use…

2

Add Excel-like "color scale" conditional formatting to your reports

I’ve been meaning to do this for a long time, and it looks like David Lean beat me to it earlier this year by posting an incredibly thorough four-part discussion of how to do conditional formatting in Reporting Services. I’ve played with his sample code a bit and put together a variation on his theme…

16

Localizing a report model

Reports and report models can be localized at many levels. Following are some ideas around the current support in SQL Server 2008 for each type of localization.   –          Localized metadata at report design time o   Example: Spanish report author sees “Cliente” instead of “Customer” in model explorer o   Report models do not support multiple…

3

Sorting the values in parameter dropdowns in Report Builder

In SQL 2005 the semantic queries generated by Report Builder do not support sorting. This is not a problem in the report itself, because all sorting is defined and implemented in the report definition instead of the query. However, for parameter dropdowns there is currently no solution: if the query doesn’t sort the data, there’s…

5

Requiring Report Builder users to choose a perspective

Some customers have report models that are large enough they do not expect any user to ever request the entire model, and would actually prefer to disallow that option, and require them to choose a perspective instead. There is an undocumented and unsupported feature that provides this behavior in the current release. It is used…

6

Launching Report Builder from the command line

A number of people have asked if it is possible to launch RB from the command line. While it is in fact possible, it is an unsupported feature in SQL Server 2005. That said, here’s how to do it. You just need to specify what report server to connect to, like this:      ReportBuilder.exe /s=http://mybox/reportserver Other supported…

4

Enforcing timeouts on Report Builder queries

I’ve attached a sample C# project illustrating how to create a custom data processing extension (more info) that enforces a maximum timeout on any SQL queries submitted to it, including those generated from Report Builder. To try it out: Open and build the attached C# project in VS. Copy output assembly to the …\ReportServer\bin folder. Add…

9

Creating a report model that can be used against multiple databases

Sometimes it is useful to create a report model that can be used against multiple databases that have the same structure, but reside on different servers and/or have different schema qualifiers. Uploading a second copy of a report model and pointing it at a different database is certainly easy enough, but you can run into…

5

How to get the SQL for a Report Builder report

There are two ways to get the generated SQL for a Report Builder report (or for a model-based query in any RS report): 1) use SQL Profiler to capture the incoming SQL commands, or 2) enable query logging in the report server. To enable query logging on the report server (option #2), make the following…

5