SSRS Report Builder Part 10.2 - Query Parameters and Filters

[@HannaKandratsenka-v5t]

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.

[@WiseOwlTutorials]

Thank you so much for the kind comments, happy to hear that you found it useful!

[@Microgen86]

been waiting for this tutorial. awesome content✌

[@Microgen86]

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

[@WiseOwlTutorials]

That's an excellent question and an important thing to consider! Fortunately, the way query parameters work is a little more complicated than simply concatenating everything into a dynamic SQL string. You can use the SQL Server Profiler to see what actually happens when you run the report: exec sp_executesql N'SELECT Film.Title ,Film.ReleaseDate ,Film.RunTimeMinutes FROM Film WHERE Film.RunTimeMinutes

[@Microgen86]

@@WiseOwlTutorials thanks for the info 👌

[@WiseOwlTutorials]

@@Microgen86 You're welcome!

[@abdullahquhtani4247]

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?!

[@WiseOwlTutorials]

Hi Abdullah, the subject of the next video will be how to set default values for parameters. I have already recorded this but haven't had time to edit it yet - I hope to do that this weekend. Your second question is interesting! We would need to calculate two additional values: 1) Any text which appears before the value we are searching for. 2) Any text which appears after the text we are searching for. We can then use three placeholders in a cell in the table in this sequence: [TextBefore][ParameterValue][TextAfter] We can then format the placeholder for the [ParameterValue] with a different colour compared to the other two placeholders. That will make a good subject for a video!

[@WiseOwlTutorials]

Hi Abdullah, here's some SQL code to calculate the three parts of the string, you can copy and paste it into the Dataset Properties dialog box: select Film.Title ,Film.ReleaseDate ,Film.RunTimeMinutes ,LEFT( Title, CHARINDEX( REPLACE(@TitleText,'%','') ,Title ) - 1 ) as TextBefore ,SUBSTRING( Title ,CHARINDEX( REPLACE(@TitleText,'%','') ,Title ) ,LEN(REPLACE(@TitleText,'%','')) ) as SearchText ,SUBSTRING( Title ,CHARINDEX( REPLACE(@TitleText,'%','') ,Title ) + LEN(REPLACE(@TitleText,'%','')) ,LEN(Title) ) as TextAfter from Film where Film.Title LIKE @TitleText You can then put the three fields into a single cell in a table so that the cell looks like this in design view: [TextBefore][SearchText][TextAfter] You can then select the [SearchText] placeholder and apply different formatting to it. I hope it helps!

[@abdullahquhtani4247]

@@WiseOwlTutorials I like you man ☺️ 🙏🙏🌹

[@wadjay1117]

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.

[@WiseOwlTutorials]

Hi! The query designers in Report Builder are different depending on which type of data source you're connecting to.

[@suleidyhernandezcarratala5995]

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

[@abdullahquhtani4247]

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?! 🙏

[@WiseOwlTutorials]

Hi Abdullah, yes there can be a big a difference. In basic terms, with report parameters, all rows are returned to the report and then the results are filtered. With query parameters, the criteria are evaluated before rows are returned to the report, so only the required rows are retrieved. You can observe the difference yourself using the system view called ExecutionLog3 stored in the report server database. This query returns information on the number of rows returned and the time taken, etc. (you need to alter the database name to match the name of your Report Server database): SELECT TOP (1000) [InstanceName] ,[ItemPath] ,[UserName] ,[Parameters] ,[TimeStart] ,[TimeEnd] ,[TimeDataRetrieval] ,[TimeProcessing] ,[TimeRendering] ,[ByteCount] ,[RowCount] FROM [ReportServer$SQL2017].[dbo].[ExecutionLog3] ORDER BY [TimeStart] DESC Try running reports using report parameters compared to reports using query parameters - the query parameters will be much more efficient. There's a very comprehensive write-up of the potential benefits of using stored procedures here stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode I hope that helps!

[@abdullahquhtani4247]

@@WiseOwlTutorials Great. That really what came to my mind but I wanted to confirm that from a professional expert like you. I think it’ll be highly appreciated if you could do a tutorial on this important issue so followers can differentiate between those two methods and realize that it is not a matter of choice but a matter of situation, performance and efficiency. AGAIN, 🙏🙏🙏🙏🌹🌹

[@WiseOwlTutorials]

@@abdullahquhtani4247 Hi Abdullah, yes I have that topic on my list as well! Thanks for your support!

[@v1ceCSGO]

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

[@WiseOwlTutorials]

docs.microsoft.com/en-us/sql/reporting-services/pass-a-report-parameter-within-a-url?view=sql-server-ver15

[@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?

[@WiseOwlTutorials]

Hi Herbi, you might find this video on Optional Query Parameters useful www.wiseowl.co.uk/report-builder/videos/report-builder-2016/optional-query-parameters/ Hope it helps!

[@chrishatton2642]

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

[@WiseOwlTutorials]

Hi Chris! We don't have a separate video on that topic but it's essentially the same as described in the "Creating Parameters in SQL" section in this video - you simply type the parameter names in your query to create a linked report parameter. I hope that helps!

[@lemonlearner7315]

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¿¿???

[@WiseOwlTutorials]

Hi! If you're using the same sample data as in the video this works correctly, e.g. using 11/06/1993 as the start date and end date shows details for Jurassic Park. Does your date column also store a time? If so, you'll need to account for that. For example, if you enter the start date and end date as 11/06/1993, this is assumed to be midnight on that date, 11/06/1993 00:00:00. If Jurassic Park's release date was stored as 11/06/1993 00:00:01 it would not be included in the results. One solution is to write your criteria like this: WHERE ReleaseDate >= @StartDate AND ReleaseDate < @EndDate Then pick the following day in the EndDate parameter. Alternatively, you can write the time in the EndDate parameter like this 11/06/1993 23:59:59

[@kevinsimkin3296]

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

[@WiseOwlTutorials]

Hi Kevin, I don't know of a way to do this apart from embedding your report in the ReportViewer control in a custom front end docs.microsoft.com/en-us/sql/reporting-services/application-integration/integrating-reporting-services-using-reportviewer-controls-get-started?view=sql-server-ver16

[@gabrielacastillo9041]

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

[@WiseOwlTutorials]

Hi Gabriela, sorry but I haven't seen that problem.

[@gabrielacastillo9041]

@@WiseOwlTutorials Oh...don't worry, thanks for replying. By the way, your videos are very good! Greetings from Mexico

[@WiseOwlTutorials]

@@gabrielacastillo9041 I hope that you find a solution! I visited Mexico a few years ago, it's a fantastic place, I think Chichén-Itzá was my highlight!

[@ellac5159]

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!

[@WiseOwlTutorials]

Hi Ella, you may find it useful to read this documentation on caching datasets docs.microsoft.com/en-us/sql/reporting-services/report-server/cache-shared-datasets-ssrs?view=sql-server-ver15 And for general performance advice you could try this docs.microsoft.com/en-us/sql/reporting-services/report-server/performance-snapshots-caching-reporting-services?view=sql-server-ver15 I hope it helps!