Archives: March 2010
Our SQL Server Reporting Services Recipe book was released in April. This is a cookbook style book for experienced SSRS designers with 63 individual report recipes, each addressing a specific business requirement or scenario. My co-author, Robert Bruckner, is a technical lead on the SSRS product team and a prolific…
Creating one report to meet multiple business requirements can save a lot of work down the road. A common scenario is for different users to ask for similar reporting metrics that may just be variations of existing reports. Using SQL Server Reporting Services, you can achieve this goal with a… Read more
I want to return the field value for a specific row to a textbox below a table in my report. To pull this off, I have to be a little creative as there isn't a simple way to return a value from a specific row in a dataset outside of… Read more
A user has been added to a SSRS role for the report folder or report but is still prompted to login. This can happen if Internet Explorer isn't configured to persist the user's Windows login to Intranet sites.
To rectify this, in Internet Explorer open Tools > Internet Options >… Read more
There are certain key words in a T-SQL query than cannot be parameterized, including the TOP statement. Consider the followong attempt to parameterize the number of rows returned from the query results:
Select Top @TopCustomerCount
Case When DC.MiddleName Is Null Then
FirstName + ' ' +…
Let's say that you have a column named some_id in an outer-joined table that will return a value if a record exists and will return null if a record doesn't exist...
- Add an image item to a table cell.
- Add two embedded images to the report; a check mark and…
Report designers often revert to overly complex report designs and often use subreports unnecessarily. Building a report on a single dataset query to return all related rows gives you greater flexibility to group and filter the data.
The following sample report contains a single dataset using the following query:
Reporting Services supports recursive hierarchies and Analysis Services supports parent-child
…and these two powerful features ought to just work together seamlessly, right? Well, they can if you know what to do.
To follow this example I assume that you have a fundamental knowledge of columnar report design in Reporting… Read more
This question comes up quite often. A report requires a user to be authenticated using their own network user name and password. Perhaps the data source performs row-level filtering for the user or the UserID global user object is used in a query or filter expression to return user-specific data.… Read more
In cases where you want the data or content in a report to be different for each user, there are a couple different ways to approach this. This will depend on whether you want to return user-specific data or whether you want to use logic to hide and show objects… Read more
On the MSDN forums I see a lot of questions about how to use program code or command line script to automate or simplify administrative tasks. Reporting Services has an extensive API for performing practically any task you can perform through Report Manager or Visual Studio. Automation script can be… Read more
I've decided that from now on, consulting project estimates will be padded for an additional week to get Oracle connectivity working on my dev. machine. Designing an Analysis Services solution with an Oracle data source should be easy if you are using the right data provider and have Oracle's drivers… Read more
You can define a calculated field for the datset outside of the query using a Visual Basic.NET expression. In the Data Fields or Report Data window, right-click the dataset and add a new calculated field. Give it a name and then create an expression like the following. There is no… Read more
A forum poster wanted to use a StartDate parameter to limit the range of dates available in a second parameter to that date through the end of the same year.
This can be done if you would like to use drop-down list of dates. To do this, use a query…
To create a "greenbar" report in a table with alternating row colors, you can simply use the RowNumber function to determine whether a row is odd or even. This also works with the newer Tablix-style matrix in SSRS 2008. However the matrix data region in SSRS 2000 and 2005 doesn't… Read more
This is the second in a series of three articles highlighting the Report Solution Patterns and Recipes from Chapter 7 of Professional SQL Server 2005 Reporting Services.
As we have endeavored to solve various business problems, we've learned to do some interesting things with Reporting Services. On… Read more
Chart reports are an effective way to visualize data to show trends and comparisons in a way that can help the user get real meaning from numbers. However, chart data can be meaningless without context. This example will show you how to display a column chart with a goal line… Read more
Top ranked lists are a common type of report. In many cases business users may want to see only a specific number of records at the top or bottom of a range, effectively reporting the best or worst items in ranked order. We see ranked lists in all types of… Read more