Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archives: March 2010

Report Recipe Book Released

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…

Read more

0 comments, 251 reads

Posted in SQL Server BI Blog on 21 March 2010

Dynamically Adding and Removing Columns to a Query and Report

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

0 comments, 357 reads

Posted in SQL Server BI Blog on 16 March 2010

Return the Value of a Specified Dataset Row Outside a Data Region

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

0 comments, 157 reads

Posted in SQL Server BI Blog on 16 March 2010

User is prompted to login when viewing a report

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

4 comments, 911 reads

Posted in SQL Server BI Blog on 16 March 2010

How to parameterize a TOP values query

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
      F.CustomerKey, F.OrderDateKey,
      Case When DC.MiddleName Is Null Then
FirstName + ' ' +…

Read more

0 comments, 308 reads

Posted in SQL Server BI Blog on 16 March 2010

SPB Applications for Windows Mobile

<<placeholder>>

…for product review



Weblog by Paul Turley and SQL Server BI Blog.

Read more

0 comments, 179 reads

Posted in SQL Server BI Blog on 12 March 2010

Displaying a Check Mark for a Row When a Related Record Exists

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...

  1. Add an image item to a table cell.
  2. Add two embedded images to the report; a check mark and…

Read more

0 comments, 177 reads

Posted in SQL Server BI Blog on 11 March 2010

Nested Tables in a Repeating List Data Region

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:

select…

Read more

0 comments, 175 reads

Posted in SQL Server BI Blog on 11 March 2010

Creating a Recursive Report from a Parent Child Dimension

Reporting Services supports recursive hierarchies and Analysis Services supports parent-child dimensions…
…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

0 comments, 2,621 reads

Posted in SQL Server BI Blog on 11 March 2010

Using a Report Subscription with User Authentication

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

0 comments, 162 reads

Posted in SQL Server BI Blog on 11 March 2010

Showing User-related Content in a Report

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

0 comments, 228 reads

Posted in SQL Server BI Blog on 11 March 2010

How to Script Report Deployment, Move Reports or Change Properties

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

0 comments, 233 reads

Posted in SQL Server BI Blog on 11 March 2010

Oracle .NET Data Provider not Supported but Necessary for Cube Design

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

0 comments, 182 reads

Posted in SQL Server BI Blog on 11 March 2010

Format Date as Week of Month

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

0 comments, 285 reads

Posted in SQL Server BI Blog on 11 March 2010

Report Parameter Date Range

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…

Read more

0 comments, 241 reads

Posted in SQL Server BI Blog on 11 March 2010

Alternate Row Colors in an SSRS 2000 or 2005 Matrix

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

0 comments, 824 reads

Posted in SQL Server BI Blog on 11 March 2010

Navigating to a Report in a Pop-up Window

When using the Jump to Report action int he web browser, the target report is displayed in the same browser window by default. You can call the JavaScript window.open method to open a new browser window. Use an expression to build a Jump to URL action with the appropriate report… Read more

0 comments, 161 reads

Posted in SQL Server BI Blog on 11 March 2010

Creating a Business Scorecard

Report Recipes

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

0 comments, 363 reads

Posted in SQL Server BI Blog on 11 March 2010

Column Chart with Goal Threshold Line

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

0 comments, 1,156 reads

Posted in SQL Server BI Blog on 11 March 2010

Parameterized Top Values Report

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

0 comments, 178 reads

Posted in SQL Server BI Blog on 11 March 2010

Older posts