SSRS Report Builder Part 10.2 - Query Parameters and Filters

By Andrew Gould

Chapters
00:00 Topic List
00:49 The Example
04:19 Creating a Parameter in the Query Designer
05:41 Query Parameters and Report Parameters
08:09 Changing a Parameter Name
10:43 Creating Parameters in SQL
11:48 Changing Parameter Properties
14:32 Adding Another Query Parameter
17:42 Text Parameters and SQL Wildcards
19:52 Concatenating Wildcard Characters
22:31 Displaying Parameter Values in the Report
25:47 Displaying a No Rows Message

This video explains how to create query parameters in Report Builder to filter the results of a dataset. You'll learn how to add query parameters in the Query Designer and by writing the SQL code. You'll see how a query parameter is linked to a report parameter and how to modify these parameters without breaking the link. You'll also learn how to display the parameter values in a page header so that they can be viewed when the report is exported and how to display a "no rows" message when the parameter values don't return any results.

[HannaKandratsenka-vt]

I really appreciate the high quality and well-organized content of each session! The author put in a lot of effort to explain each aspect in a very detailed manner.

[Microgen]

been waiting for this tutorial. awesome content✌

[abdullahquhtani]

Great my ideal instructor and I have two questions and I hope I find answers for the!
1-How to work around a problem might users do in case they didn’t choose a value to an operator and make report put a default value for that parameter so the report don’t through error message?

2- In case using wildcard, is it possible to color the value that the user enters in different color on the grid?!

[Microgen]

may i ask if the concatinatiin technique used in the query parameters is prone to sql injection

[chrishatton]

Very informative. Would you have a tutorial that covers creating query parameters using the Text-based Query Designer User Interface?

[suleidyhernandezcarratala]

Hi! thanks for the greats video, do you know how to implement hours parameters like you explain with the slicer with dates?

[abdullahquhtani]

One last question on the last two videos on this playlist (SSRS).
Is there a significant difference on the level of hitting database between report filter and query filter?! In case I have a very big data and I want to minimize hitting the database and also speed the process which method should I use? One more thing, which is better, safer and faster, using simple query or paramatarized store procedure?! 🙏

[ellac]

Hi! Thanks for a great video 🙂 I still though have some issues with understanding the differences between report parameters and query parameters...

I'm using a connection string to AAS as data source, and I'm trying to display some simple order data in a table - like order number, supplier, order quantity etc. I only want to show the information for one order on the page, therefore I want the order number to be a parameter that the user inserts. Order number 12345 generates one row in the table on the page.

If the user wants to put in several order numbers, it has to view the report several times. For example, if the user has 3 order numbers, it has to view the report three distinct times. This means that we want to fetch a subset of the dataset for each parameter, right? Therefore, my idea is that it would be better to set up the order number parameter as a report parameter and NOT as a query parameter.

I tried this, but the result is that when using query parameters it takes less time to load the report (around 400k rows each time), than when using report parameters (around 800k rows each time). My problem is that I used query parameters from the beginning, and hoped that changing to report parameters would decrease the loading time of the report... Is there a way to load all the data from the dataset once, and then it's possible to filter out different subset of the data through parameter (when the data is already cached)?

Any suggestions on what to do is welcome! Thanks!

[wadjay]

How come I don't see the same query designer with the same options shown here when I add a Data Source through "Add PowerBI Dataset Connection" and from a dashboard? I'm shown a different menu without the "Add Filter" portion and option to add parameter.

[DaHerbiD]

Hello, is it possible to use an OR condition for the user, so you can choose between maxruntime and startdate instead of needing both?

[vceCSGO]

How do you pass multiple parameters from the parent page using URL to go the report page? Want to use URL to open a new tab with the passed parameters

[kevinsimkin]

Can you have a parameter box that allows you to start typing an addres to get to the right address quickly when your address list is like 500 address

[lemonlearner]

why data isn't showin if i put the same day in start date and endate, what if i want to know all the information in a specific day¿¿???

[gabrielacastillo]

Hi everyone, I have a problem. When I run the report, it never displays the parameter field, and Visual Studio stops working.
Does anyone have the same issue? I'm using Visual Studio 2019