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 applications to answer questions like "what are the top 5 best selling products?"

or "who are my 5 worst producing sales people?" This is a relatively simple matter

using a top values or top ranked query. It may be even more useful if users could

select or enter the number of items to return in the report.

Product Versions

• Reporting Services 2000

• Reporting Services 2005

• Reporting Services 2008

What You'll Need

• A query with ordered aggregate values.

• A parameter to specify the

Designing the Report

To understand the challenge, we?ll start with a simple top values query in a tabular

report. The query will return the top ten most profitable customers.

1. Start by creating a new report with a data source using the AdventureWorksDW2008

or AdventureWorksDW database.

2. Create a dataset using the following SQL query:

SELECT TOP 10

DimCustomer.FirstName + ' ' + DimCustomer.LastName AS CustomerName

, SUM(FactInternetSales.SalesAmount) AS SalesAmount

FROM

DimCustomer INNER JOIN FactInternetSales

ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey

GROUP BY DimCustomer.FirstName + ' ' + DimCustomer.LastName

ORDER BY SUM(FactInternetSales.SalesAmount) DESC

3. Add a table to the report and use it to show the output of this query. The report

should look similar to Figure 1.

Figure 1

Add a parameter to use in place of the static top value (the number 10.) Rather than

adding the parameter reference to the query script and replying on the report designer

to generate the report parameter, we want to manually add the parameter to the report

first.

4. Add a new report parameter and in the Report Parameter Properties dialog, name

the parameter TopCustomers.

5. Provide an appropriate prompt and leave the rest of the properties on the General

page with default values.

Figure 2

6. On the Available Values page, enter a few integer values to be used by a user to

select the number of rows to show on the report.

7. Choose Specify values

8. Add one item for each parameter value and then for each parameter in the list,

enter the same number for the Label and the Value, as you see in Figure 3.

Figure 3

9. Close the dialog and save the new parameter.

Consider the following attempt to parameterize the number of rows returned from the

query results.

10. Edit the dataset query and replace the top value number 10 with the query parameter

reference @TopCustomers. The query should look like this:

SELECT TOP @TopCustomers

DimCustomer.FirstName + ' ' + DimCustomer.LastName AS CustomerName

, SUM(FactInternetSales.SalesAmount) AS SalesAmount

FROM

DimCustomer INNER JOIN FactInternetSales

ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey

GROUP BY DimCustomer.FirstName + ' ' + DimCustomer.LastName

ORDER BY SUM(FactInternetSales.SalesAmount) DESC

When you save or run the query, you will be prompted for the parameter value. Enter

a value such as 5. This behavior is expected however the report designer will display

an error after you enter this value, as you can see in Figure 4.

Figure 4

This query will not work because the query designer can't resolve the TopCustomerCount

parameter.  There are certain key words and values in a T-SQL query that cannot

be parameterized, including the TOP statement.  So, how do we work around this

limitation?

11. On the Dataset Properties dialog, click the expression button (fx)

next to the Query box to open the expression editor.

To deal with this challenge, the entire query is converted to a string with the parameter

value concatenated into the appropriate position. String concatenation is performed

using Visual Basic.NET expression code. Carriage returns may be inserted for code

readability but have no bearing on the actual string that will be presented to the

data provider. Since the database engine doesn?t care about line returns and extra

spaces, it?s not important to add these to the code but it is important to make sure

that there is at least one space between each operator and object name.

The following script returns the string equivalent of the same query with the embedded

parameter value:

="SELECT TOP " & Parameters!TopCustomers.Value

& " DimCustomer.FirstName + ' ' + DimCustomer.LastName AS CustomerName "

& ", SUM(FactInternetSales.SalesAmount) AS SalesAmount "

& "FROM "

& " DimCustomer INNER JOIN FactInternetSales "

& " ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey "

& "GROUP BY DimCustomer.FirstName + ' ' + DimCustomer.LastName "

& "ORDER BY SUM(FactInternetSales.SalesAmount) DESC"

When the expression is saved, the query parser may complain because a SQL statement

can't start with an equal sign (=). If this happens, just accept the error and move

on. It's actually fine because the field metadata has already been saved with the

dataset object. After this, the designer will know to resolve the expression rather

than treating as a SQL command. It's important to run the query once in the query

designer before using the string concatenation technique so the report designer can

resolve the dataset Fields collection.

We'll add some header text to show the number of items the user decided to show in

the report.

12. Add a textbox to the report body, above the table.

13. Right-click the textbox and add the following expression:

="Top " & Parameters!TopCustomers.Value & " Customers by Total Sales Amount"

14. Preview the report again.

15. Select a value from the parameter drop-down list and click the Run Report button

on the toolbar. The report should run and return the appropriate number of rows, as

seen in Figure 5.

Figure 5

For even more flexibility, create a custom function in the report properties Code

window and use Visual Basic.NET code to build and return the entire query string.

Top Value Reports for Cubes

The same fundamental technique may also be applied to an MDX query. The MDX query

designer, which was introduced in SSRS 2005 for reports that use SQL Server Analysis

Services as a data source, has some strict restrictions about using in-line parameters.

The query designer can also be unforgiving when you modify generated query script.

Queries may be hand-written in the query editor text mode. In many cases it may be

easier or even necessary to assemble the query script using a Visual Basic.NET expression.

1. To apply this pattern, create a new report, similar to the one in the previous

example, with a data source connecting to the Adventure Works DW 2008 Analysis Services

database for SQL Server 2008 or the Adventure Works DW database for 2005.

2. Following the same pattern as before, create a new dataset. In the MDX query designer,

switch to text mode using the Design Mode button on the right-side of the toolbar,

and type the following query:

SELECT

{[Measures].[Internet Sales Amount]} ON COLUMNS

, NON EMPTY TOPCOUNT([Customer].[Customer].[Customer].MEMBERS, 5

, [Measures].[Internet Sales Amount]) ON ROWS

FROM [Adventure Works];

3. Execute the query to build the dataset and Fields collection.

4. Add the TopCustomers parameter per the previous instructions.

5. In the DataSet Properties dialog, replace the query text with the following expression:

="SELECT "

& "{[Measures].[Internet Sales Amount]} ON COLUMNS, "

& " NON EMPTY TOPCOUNT([Customer].[Customer].[Customer].MEMBERS, "

& Parameters!TopCustomers.Value & ", [Measures].[Internet Sales Amount]) ON

ROWS "

& " FROM [Adventure Works];"

6. Preview the report, select a Top Customers parameter value and click View Report

to execute the query and run the report.

Summary

You've seen how to solve an important business problem by building a custom query

using an expression. This technique has many applications and may used to resolve

complex decision structures in code before assembling the SQL Statement string. 

There are two fundamental principles that will expand your report design horizons.

The first is that although query languages, like TSQL and MDX, are powerful and be

use creatively, they have their limitations. Query languages are optimized for data

retrieval and manipulation but have limited capabilities for decision structures and

branching logic. True programming languages exist for this purpose and combining the

strengths of both programming and query languages can often solve even the most challenging

business problems.

The query designers also have limitations that can easily be circumnavigated with

a little code, but typically without the aid of graphical query-building tools. As

a rule, write the query in its simplest form using the design tool first and execute

the query to generate the fields' metadata. After that, replace the query with script

or code to handle the complexities of conditional logic and parameterization.

Weblog by Paul Turley and SQL Server BI Blog.