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

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2. You can visit his website and blog at TimMitchell.net or follow him on Twitter at twitter.com/Tim_Mitchell.

Report Parameter order in SSRS

Is the order of parameters important in SQL Server Reporting Services reports?  If you’ve got nested parameters (parameters that derive their value from one or more other parameters), it is very important!

Using a trivial report example, I’m going to demonstrate how the order of parameters matters a great deal when those parameters interact with one another.  For this example, I’ll to create a simple sales report in SSRS against the AdventureWorks database to retrieve a list of our highest revenue-generating customers in descending order of revenue.  The report will include a parameter to allow the user to select the number of customers to be displayed on the report.

I’ll start off by creating the report in BIDS, and will add the two parameters:

params

The first parameter will control the number of customer records to be displayed, and I’ll set this to be a static list of possible values (in our case, either 10, 25, or 50 records).  The second parameter will contain the SQL query to be sent to the database, and will be partially derived from the first by including in the query the number of records to be returned:

="SELECT TOP " & Parameters!NumCust.Value.ToString() & " Person.LastName " &
" + ',' + Person.FirstName [CustomerName], Customer.CustomerID, " &
" SUM(SalesOrderHeader.SubTotal) AS TotalSales FROM Sales.SalesOrderHeader " &
" INNER JOIN Sales.Customer ON Sales.SalesOrderHeader.CustomerID = " &
" Sales.Customer.CustomerID INNER JOIN Person.Person ON Person.BusinessEntityID " &
" = Customer.PersonID GROUP BY Person.LastName + ',' + Person.FirstName, " &
" Customer.CustomerID ORDER BY TotalSales DESC"

After adding the three output fields to the data set, I should be able to execute this query and retrieve the expected results.

report

No problems at all.  However, let’s say that I had created the parameters in the reverse order, such that the one containing the query appears before the parameter specifying the number of records:

paramsreverse

Now when I attempt to execute this report, I receive a very generic error message:

reporterror

So obviously, the order of parameters is critical if the value of one of them is based on another.  Fortunately, it’s very easy to change the order of parameters after they have been created using the positional up/down arrows just above the Report Data workspace:

arrows

Since the resulting error message didn’t provide much information for troubleshooting, this type of problem could be difficult to track down, especially if there are a lot of parameters in the report.  Hopefully, this reminder will help someone avoid spending a lot of time tracking down parameter order problems.

Comments

Posted by pavank321 on 10 January 2013

How you used that Query parameter and i need some more clarification..

And i understood order of parameters.

thank u..

Leave a Comment

Please register or log in to leave a comment.