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.