SSRS Report Builder Part 10.4 - Optional Report Parameters

[@JC-KeepSmiling]

Unsure if Andrew is still around but just wanted to say a massive thankyou for your tutorials. They have helped me so much and often made my job less challenging and given me a sense of accomplishment when learning a new skill in SQL. You deserve all the success and praise that comes your way. Hope you are enjoying whatever life holds for you at present.

[@WiseOwlTutorials]

I certainly am! Happy that you found the videos so useful - and thank you for taking the time to leave such a nice comment, it's always nice to hear when the channel has helped people!

[@johnburn351]

Thanks Andrew, one of the best videos for Report Builder on KZbin. Can't thank you enough for your hard work and effort. I am struggling to create a report with multi value parameters and pass a blank and null value. Can you please do a video as above on Allowing Blank and Null Values with Allow Multiple Values' option

[@WiseOwlTutorials]

Thanks John, I appreciate the comment! Multi-value parameters, drop down list parameters and working with Nulls are all topics on the list and we'll have videos on these in the coming weeks. In the meantime, if you want to create a multi-value drop list parameter which contains a NULL value you can do the following. This example uses the Movies database - the aim is to create a drop down list parameter containing Country names and include a NULL option in the list. The report will show a list of films made in the selected countries. There is one film in the Film table which has no CountryID assigned to it. 1) Create a dataset called FilmsFromSelectedCountries using this query: SELECT Film.Title ,Film.ReleaseDate ,Country.Country FROM Film LEFT JOIN Country ON Country.CountryID = Film.CountryID WHERE ISNULL(Film.CountryID, -1) IN (@CountryIDList) ORDER BY Film.CountryID, Film.Title The important part is the use of the ISNULL function in the WHERE clause to substitute any NULLs with a value of -1 (I'm using -1 as I know that value can't be assigned to any existing Country). When you click OK, the report will generate a parameter called @CountryIDList. 2) Create a second dataset called CountryList to populate the drop down list of countries. Use this query: SELECT DISTINCT Country.CountryID ,Country.Country ,1 AS OrderColumn FROM Country INNER JOIN Film ON Country.CountryID = Film.CountryID UNION SELECT -1 ,'No Country Assigned' ,0 ORDER BY OrderColumn, Country The first half selects all the CountryIDs and names for countries which have been assigned to films in the Film table. The second half (after UNION) adds an extra row to the result set whose CountryID field has a value of -1 and Country field has a value of 'No Country Assigned'. The third column, OrderColumn, allows us to sort this extra row so that it appears at the top of the drop down list values. 3) Go to the properties dialog box for the CountryIDList parameter 4) Change the DataType to Integer (optional but good practice) 5) Check the box for Allow Multiple Values (ignore the Allow Null Values box) 6) Go to the Available Values page and choose Get Values From a Query 7) Set the Dataset to CountryList 8) Set the Value Field to CountryID 9) Set the Label Field to Country That's it. You can now select any item from the drop down list, including "No Country Assigned" with its underlying value of -1. The FilmsFromSelectedCountries dataset substitutes NULL values in the CountryID field with -1 so you'll see the one film with no country assigned to it (with an appropriate title to match). Hope that helps!

[@Microgen86]

thanks for the precious info

[@WiseOwlTutorials]

You're welcome, thanks for watching!

[@Microgen86]

@@WiseOwlTutorials im kinda stuck on using indicators. do you have a video on that?

[@WiseOwlTutorials]

@@Microgen86 Hi, we have a video on indicators from an older playlist which you can see here kzbin.info/www/bejne/qYqlfKyvf5Wogrs There will be an updated video in this Report Builder playlist eventually as well. Is there something specific you're stuck with?

[@Microgen86]

@@WiseOwlTutorials my bad, was using incorrect ranges. just fixed it after watching the video. thanks 👍👍👍

[@WiseOwlTutorials]

@@Microgen86 Glad you got it sorted!

[@MichałSzczygiecki]

Hi Andrew. Are you able to show row level security. How we can set filters depend of logged user?

[@WiseOwlTutorials]

Hi! Row level security is a Power BI feature rather than a Reporting Services one, is that what you mean? Reporting Services controls access to items at the folder or report level. You can use the UserID built-in field to identify the user viewing the report but you'd need to write expressions to control filters or the visibility of objects in the report.

[@AaronCraven79]

Thanks for all these tutorials. Because of the ambiguity between European date formatting and American date formatting, I actually prefer the ISO 8601 date format convention (YYYY-MM-DD), as it tends to be immediately obvious to even very casual users (2024-03-01 is almost always going to be interpreted as March 1, 2024 by both Americans and Europeans). Alas, the date literal doesn't seem to allow this (at least in report builder), but CDate() does allow it (e.g. =IIf(IsNothing(Parameters!MyDate.Value), CDate("1901-01-01"), Parameters!MyDate.Value) ). Especially if you have a multinational team, this may be well worth the extra keystrokes when entering date literals.

[@si8002]

Thank you very much for these great videos. I am just wondering, what is the stored procedure would look like in SQL?

[@WiseOwlTutorials]

Hi there, try this video kzbin.info/www/bejne/naKvaq1slLhsoKc I hope it helps!

[@abdullahquhtani4247]

Amazing Mr. “Out of this world”👍🏼☺️. BTW, is it possible to substitute NULL word with a local language word (term). As you know normal people who are supposed to use reports don’t have an idea about such expressions, same thing goes for “View report” button caption. Thank you again 🙏🙏.

[@WiseOwlTutorials]

Thank you Abdullah! There is a very complicated way to change the NULL label but it involves creating your own front end to display the reports you've created. It's a lot of effort for a simple change but if you're interested you can read about it here docs.microsoft.com/en-us/sql/reporting-services/application-integration/integrating-reporting-services-using-reportviewer-controls-get-started?view=sql-server-ver15 As a limited workaround, you could also use your own Boolean parameter to allow the user to choose to ignore a parameter. I'll create a video on this topic at some point but in the meantime, here's how to create an example using the Movies database: 1) Create a dataset using this query: SELECT Film.Title ,Film.RunTimeMinutes FROM Film WHERE (RunTimeMinutes >= @MinRunTime OR @IgnoreMin = 1) AND (RunTimeMinutes

[@abdullahquhtani4247]

@@WiseOwlTutorials Thank you 🙏 🙏🙏

[@WiseOwlTutorials]

@@abdullahquhtani4247 You're very welcome!

[@srinivasbestha7293]

Hi , Is it possible to use single (parameter contains values from multiple columns like name,ID etc ) to filter the report , using report level parameters ? If possible how ?

[@WiseOwlTutorials]

Hi! If I understand what you're trying to do I think you'd need to create a calculated field which combines all the columns you're interested in and then use this column to populate the drop list parameter and to create your filter. I hope it helps!