Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Menu

Skip to content
  • Home
  • My Books
  • Presentations
  • Best Practice Resources: Blogs & Books from the Experts
  • Video Tutorials
  • COVID-19 Daily Updates Report
  • Visualizations
  • About/Bio
  • Paul’s Bio
  • Note to SPAMers

Tag Archives: Top values

How to parameterize a TOP values query

March 11, 2010 by Paul Turley

0

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 + ' ' + LastName
Else FirstName + ' ' + MiddleName + '.' + ' ' +
LastName End CustomerName,
Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate,
Sum(F.SalesAmount) SalesAmount
From
FactInternetSales F, DimTime DT, DimCustomer DC
Where
F.OrderDateKey = DT.TimeKey
And F.CustomerKey = DC.CustomerKey
And Convert(Int,DT.CalendarYear) = @Year
Group By
F.CustomerKey, F.OrderDateKey, DC.MiddleName,
DC.FirstName, DC.LastName, DT.FullDateAlternateKey
Order By
F.OrderDateKey Desc, CustomerName

This query will not work because the query designer can’t resolve the TopCustomerCount parameter.  To solve this problem, build an expression to concatonate the entire query, with parameterized literal values like the following example:

=”Select Top ” & Parameters!TopCustomerCount.Value & ” ” _ 
& ”      F.CustomerKey, F.OrderDateKey, ” _ 
& ”      Case When DC.MiddleName Is Null Then “ _
& “      FirstName + ‘ ‘ + LastName ” _ 
& ”      Else FirstName + ‘ ‘ + MiddleName + ‘.’ + ‘ ‘ “_
& “     + LastName End CustomerName, ” _ 
& ”      Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate, ” _ 
& ”      Sum(F.SalesAmount) SalesAmount ” _ 
& “From ” _ 
& ”      FactInternetSales F, DimTime DT, DimCustomer DC ” _ 
& “Where ” _ 
& ”      F.OrderDateKey = DT.TimeKey ” _ 
& ”      And F.CustomerKey = DC.CustomerKey ” _ 
& ”      And Convert(Int,DT.CalendarYear) = ” _
& Parameters!Year.Value & ” Group By ” _ 
& ”     F.CustomerKey, F.OrderDateKey, DC.MiddleName, “ _
& “     DC.FirstName, DC.LastName, DT.FullDateAlternateKey ” _ 
& “Order By ” _ 
& ”      F.OrderDateKey Desc, CustomerName”

This same technique can be used to resolve complex decision structures in code before building the SQL Statement string.  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.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in SQL Syndication, SSRS Design. Tagged Parameterize, Parameters, Top values

Parameterized Top Values Report

March 3, 2010 by Paul Turley

0

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.

image

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.

image

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.

image

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.

image

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.

image

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.

Share this:

  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Like this:

Like Loading...
Posted in SQL Syndication, SSRS Design. Tagged Report parameters, Top values

Post navigation

Blog Stats

  • 1,386,585 hits

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,075 other subscribers

Recent Posts

  • Doing Power BI the Right Way: 4. Power Query in Dataflows or Power BI Desktop
  • A First Look at Gen 2 Composite Models with Live Power BI Datasets
  • Power BI: The Week in Graphics
  • Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (2 of 2)
  • Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (1 of 2)
  • Composite Models Gen 2 and DirectQuery over Power BI Datasets
  • Power BI Expert Resources
  • Doing Power BI the Right Way: 7. Planning for separation – data models and reports
  • Doing Power BI the Right Way: 6. Validating data model results – Part 2
  • Doing Power BI the Right Way: 6. Validating data model results – Part 1

Category Cloud

BI Industry BI Projects Business Analytics Microsoft BI Platform MVP Community PASS Power BI PowerPivot Power View Self-service BI SolidQ SQL Server SQLServerPedia SQL Server Pro Magazine SQL Syndication SSAS Administration SSAS Design SSRS Administration SSRS Design Tabular Models

Archives

  • January 2021 (3)
  • December 2020 (4)
  • November 2020 (1)
  • October 2020 (2)
  • September 2020 (1)
  • August 2020 (1)
  • July 2020 (4)
  • May 2020 (1)
  • April 2020 (3)
  • March 2020 (3)
  • February 2020 (1)
  • January 2020 (1)
  • December 2019 (2)
  • November 2019 (5)
  • October 2019 (1)
  • September 2019 (1)
  • August 2019 (2)
  • July 2019 (5)
  • May 2019 (1)
  • April 2019 (3)
  • March 2019 (1)
  • February 2019 (2)
  • December 2018 (3)
  • November 2018 (1)
  • October 2018 (1)
  • September 2018 (3)
  • July 2018 (5)
  • May 2018 (2)
  • April 2018 (2)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (3)
  • December 2017 (3)
  • November 2017 (4)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (4)
  • April 2017 (8)
  • March 2017 (1)
  • February 2017 (2)
  • January 2017 (8)
  • December 2016 (4)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (1)
  • July 2016 (3)
  • June 2016 (3)
  • May 2016 (3)
  • March 2016 (6)
  • February 2016 (5)
  • January 2016 (2)
  • December 2015 (4)
  • November 2015 (3)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (3)
  • July 2015 (6)
  • June 2015 (1)
  • May 2015 (5)
  • April 2015 (4)
  • March 2015 (1)
  • February 2015 (2)
  • January 2015 (4)
  • December 2014 (3)
  • November 2014 (1)
  • October 2014 (4)
  • September 2014 (1)
  • August 2014 (2)
  • July 2014 (5)
  • June 2014 (4)
  • May 2014 (2)
  • April 2014 (6)
  • March 2014 (3)
  • February 2014 (7)
  • January 2014 (5)
  • December 2013 (2)
  • November 2013 (1)
  • October 2013 (1)
  • September 2013 (2)
  • July 2013 (4)
  • June 2013 (5)
  • April 2013 (1)
  • March 2013 (4)
  • February 2013 (3)
  • January 2013 (1)
  • December 2012 (4)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (3)
  • August 2012 (2)
  • July 2012 (2)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (2)
  • February 2012 (3)
  • December 2011 (1)
  • November 2011 (3)
  • October 2011 (11)
  • September 2011 (7)
  • August 2011 (4)
  • July 2011 (2)
  • June 2011 (4)
  • May 2011 (5)
  • April 2011 (5)
  • March 2011 (4)
  • February 2011 (2)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (1)
  • August 2010 (2)
  • June 2010 (1)
  • May 2010 (2)
  • April 2010 (1)
  • March 2010 (19)
  • December 2009 (1)
  • June 2009 (1)

Tag Cloud

" & Workspace and Database Recovery Techniques Aaron Nelson Ad-hoc reporting Add columns Add controls Albert Ferrari Alternate row colors Analysis Services Operations Guide Apple Are There Rules for Tabular Model Design? Article Assemblies Azure Azure Reporting Azure SQL Database BARC Survey best practices BI BI Center of Excellence BI COE BI Conference Bill Gates Birds-of-a-Feather BI Roles and Team Composition BISM BI Survey 10 Blogging Breakcrumb links Browser settings Build career Business Intelligence Business Intelligence for Visual Studio 2012 Business scorecard Can I Use Reporting Services with Tabular & PowerPivot Models? Checkbox in report Checkbox list Check mark Chris Webb Cloud computing Column chart Community Conditional formatting Conference presentation Conference review Conference session Conference Session Topics Cortana Power BI Integration Custom code Custom coding reports Custom Functions Dashboard design Dashboard standards Database Lifecycle Management Data Modeling 101 for Tabular Models Data Quality Services Dataset filter nulls Datazen Datazen control selection Date parameters DAX DAX: Essential Concepts DAX: Some of the Most Interesting Functions DAX: Some of the Most Useful Functions DAX functions DAX reference DAX syntax Demo scenario Denali CTP3 DevTeach DLM Do I Write MDX or DAX Queries to Report on Tabular Data? Do We Need to Have SharePoint to Use Tabular Models? Drill-down Drill-through Drillthrough Dynamic column visibility Dynamics CRM Dynamics reporting Embedded formatting ENterprise SSAS Errors Estimating BI European PASS Filter by user Formula Firewall Funnel charts Garner Magic Quadrant Microsoft BI Getting Started with DAX Calculations Global Summit Live Feeds Greenbar report Grocery shopping demo Hans Rosling Happy Birthday Power BI Hide columns Hitachi Consulting How Do You Design a Tabular Model for a Large Volume of Data? How Do You Secure a Tabular Model? How to Deploy and Manage a Tabular Model SSAS Database How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model HTML text integrated mode Interview Interviews Isn’t a Tabular Model Just Another Name for a Cube? James Phillips Julie Koesmarno King of Spain KPI indicator Licensing Login prompt Manually starting subscription Map Visualization Marco RUsso Master-detail report Master Data Management MDM MDX datasets MDX queries Microsoft Architecture Journal Microsoft humour Microsoft MVP Microsoft news Mobile Reporting Mobile Reports MVP community MVP Deep Dives 2 MVPs support the community MVP Summit navigation Nested tables Null filter Olivier Matrat Olympia WA Oracle vs Microsoft in the movies Oregon SQL Saturday Parameter controls Parameterize Parameters PASS 2012 PASS BAC Blog Feed PASS community leaders PASS Conference PASS Global Summit 2012 PASS Keynotes PASS Summit PASS Summit 2017 PASS Summit 2018 PASS Summit Announcements Paul te Braak PDF image distortion dithering fonts PerformancePoint Pinal Dave Poll About Product Usage Poll Results Pop-up window; Java script Portland OR Power BI Administration Power BI Best Visuals Contest Power BI DAX Power BI Partner Showcase Power BI Premium Power BI Pro Power BI Training Power BI World Tour Power Pivot PowerPivot Power Pivot DAX Power Query Power Query Training Power View Power View multidimensional cubes Preparing Data for a Tabular Model Project Phoenix Recipes Redmond SQL Saturday Reed Jacobson Remove columns Repeating list Report controls report dependencies Report deployment Reporting Services 2016 Reporting Services Training Report navigation Report parameters Report recipe book Reports for MDX Return specific row Rob Collie DAX Book Robert Bruckner Scheduled Refresh Scripting Tabular Model Measures Self-service reporting Seth Bauer SharePoint SharePoint 2012 SharePoint integration Simplifying and Automating Tabular Model Design Tasks SolidQ SolidQ Journal Solid Quality Mentors Spatial queries; happy holidays; Merry Christmas SQLAuthority SQLCAT SQL Saturday SQL Saturday 446 SQL Saturday Portland Oregon SQL Server SQL Server 2012 Upgrade Guide SQL Server community SQL Server Data Tools – Business Intelligence for Visual Studio 2012 SQL Server Denali SQL Server Denali; Self-service reporting SQL Server Denali CTP3 SQL Server MVP SQL Server Optimization SQL Server Pro Magazine SQL Teach SSAS SSAS Performance Logger SSAS Tabular SSAS Tools BI Development Tools SSDT BI SSRS 2016 SSRS dynamic columns SSRS PowerShell SSRS version control standards Start subscription Steve Jobs StreamInsight Strip line style Subscription Survival Tips for Using the Tabular Model Design Environment Tabular DAX Tabular Model & " Tabular Model Common Errors and Remedies Tabular Model Design Tabular Model Design Checklist Tabular Modeling Tabular models Tabular report design TechEd TechEd 2011 Sessions TechSmith Snagit Pro themes Threshold line Top values Training clsses Unconference User-related report content User authentication User prompted to login Using DAX to Solve real-World Business Scenarios Vancouver BC Vern Rabe Visualisation Visualization Visual Report Design Volunteers Weather and Climate Web.Contents Web API What About Multidimensional – Will Tabular Replace It? What are the Naming Conventions for Tabular Model Objects? What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models? What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models? What’s the Best IT Tool for Reporting on Tabular Models? What’s the Difference Between Calculated Columns & Measures? What’s the Difference Between PowerPivot and Tabular Models? Why Tabular? Wrox book
RSS
RSS Feed
Powered by WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: