If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
By Andrew Gould
https://www.wiseowl.co.uk - Parameters allow your users to filter reports by typing in values or selecting from drop down lists. But what if they want to leave a parameter blank and still see results in the report? This video teaches you how to handle null values in parameters to make this possible. You'll learn how to allow a null value in a parameter and how to write a WHERE clause to handle the null properly. We'll also look at how you can create drop down list parameters which allow null values, including how to append a null value to a dataset, how to mask the null value with descriptive text and how to sort the drop down list to make sure the null appears at the top.
You can download a script to create the database used in this video from the following link:
https://www.wiseowl.co.uk/files/execise-question-files/qf-898.zip
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
@dave5888منذ 9 سنواتExcellent job! You saved me, I needed to allow user to select from a pre-populated list of values in one parameter filtered by another parameter, AND allow id="hidden1" class="buttons"> for the 'ALL OPTIONS' (NULL) option as well. Works great. Many thanks! ....وسعت
@
@Terracronzمنذ 10 سنواتThat UNION SortOrder field was brilliant btw 2
@
@jesflo4منذ 3 سنواتBrilliant, thank you for this information, just what I was looking for. Cheers! 3
@
@alxvsetiمنذ 4 سنوات Adding a Filter Writing a WHERE Clause in a Query Adding More Parameters Modifying the WHERE Clause Adding Even More Parameters Dropdown List Params Datasets for Dropdown Lists Fiters for Dropdown List Params Appending a Null Value to The List Masking the Null Value in the List ....وسعت5
@
@pierrel5222منذ 8 سنواتThis is an outstanding series, made me actually want to go through all of them even if they don't all apply, great job
@
@khalidz1981منذ 8 سنواتYou are a very organized person and I love your work and I am going to finish the whole SSRS series
@
@MissChenTWمنذ 7 سنواتGreat video series! I think for people who are not as familiar with SQL thus the output created from "SELECT DirectorID, DirectorName, DirectorName id="hidden3" class="buttons"> AS SortField FROM tblDirector UNION SELECT NULL, 'Show all Directors', '0' ORDERBY SortField " adding the query's output will help viewers understand the last part of this great video =) The query output looks like below. Note, a row of "." simply means a valid data record. DirectorID DirectorName SortField NULL Show all Directors 0 . . . . . . . . . 4 Steven Spielberg Steven Spielberg 48 Steven Soderbergh Steven Soderbergh . . . . . . ....وسعت1
@
@krismalyمنذ 11 سنواتI enjoyed the video. This chapter is important for any developer. Thanks
@
@RaviYadavGvمنذ 9 سنواتSuper. Thank you so much for Preparing and sharing these all.
@
@autumn9175منذ 11 سنواتI learned something very useful for my job. Thank you!
@
@krismalyمنذ 11 سنواتI love this episode especially show all directors
@
@cameronj8075منذ 10 سنواتGreat video. Any suggestion on the workaround if 'DirectorName' column had duplicates and required a DISTINCT filter? With it, I can add the NULL, but not the DirectorID in the UNION @19.50
@akshayheroor3396منذ 9 سنواتIts very helpful and informative.. Thank you so much :)..
@
@qaiseriftikharمنذ 10 سنواتGreat videos, thanks for sharing them. The only thing I've noticed that you are suggesting to filter on dataset via SSRS. Isn't that very inefficient? id="hidden5" class="buttons"> Shouldn't the filtering happen at database layer? When I apply filters the way you've mentioned in your videos with 10000+ rows it takes a few minutes for report to render. ....وسعت
@
@kingrich742منذ 11 سنواتGreat video & very helpful! Is it possible to group a number of values against one label when attempting to specify values in a Parameter? I'm id="hidden6" class="buttons"> trying to list Year Groups and having difficult with a "6 and Below" option. ....وسعت
@
@fUjiMaNiaمنذ 11 سنواتThanks for the help and thanks for the replies to my other questions I had on the other videos :-)
@
@hrypo711منذ 9 سنواتThank you for the video!! You probably know this, but I think you can set the "Show all directors" as null in the Default value tab in Parameter. Thank you again
@
@nitintharwani7450منذ 11 سنواتHey your tutorials are really helpful.I have learnt alot from them.thanks for sharing.having said that I m trying to achieve masking of parameter id="hidden8" class="buttons"> just like normal password fields on login pages.do u have any idea or workaround for this. ....وسعت
@
@deepak21000منذ 7 سنواتNice video, thanks for the explanation. However i have a requirement similar to this, how can we still have a NULL checkbox as a default value id="hidden9" class="buttons"> which runs report for all directors and when NULL is unchecked we have a drop-down list of DIRECTORS to choose instead of the current scenario where we have to manually input a DIRECTOR'S name. Your reply for this is much appreciated.cheers ....وسعت
@
@devexpost8508منذ 7 سنواتThanks. Well done. One very minor note however, at in the video, I believe that the addition of the "Show all Directors" list entry does not----as you say----"break the link to the criteria in your other data set", but instead simply results in a search for a @prmDirector "director" (a director named "Show all Directors") which does not exist in the FilmName table, and thus no results. Best Regards. ....وسعت
@
@nitintharwani7450منذ 11 سنواتThank you for the quick reply.Yeah I know about values getting passed as literal values.but my client is okay with that.he just need to have masking id="hidden11" class="buttons"> for the textbox by any character so that what ever he type in that textbox is not visible.m still looking for the way to do.if you happen to find the solution please let me know.thanks again for the reply ....وسعت
@
@fUjiMaNiaمنذ 11 سنواتquestion: is there a way to prevent the report from running until I click view report in the scenario when the Null check boxes are ticket?
@
@murthymamidi6639منذ 9 سنواتHI Wise, I have watched every video ,Really awesome and I learn more from u,and I have a small doubt in this video ,(14.56)minute u r creating the dtsDirectors id="hidden12" class="buttons"> parameter and datatype as Text ,but (21.16) u have been changed the value field as "directorid" .My question is as a datatype it was text but as a value field it is a integer.How it will be possible,I have a little bit confusion in this area.Please help me. ....وسعت
@
@mahnazrezahosseini173منذ 6 سنواتThanks for video. I have "All Groups" in my dropdownlist and want to return all values whenever I choose it. Couln't do it. I used two ways id="hidden13" class="buttons"> for creating dropdownlist : 1)Specify Vales 2) Get Values from a query. I don't know how to make it . Please help me ....وسعت
@
@pianomanuk1منذ 9 سنواتHi Am working on a report with 12 parameters. 2 x Mandatory Start Date, End Date, 6 x Mandatory Multi Selects and 4 x Optional Multi Selects. My id="hidden14" class="buttons"> multi selects are connected to the dataset stored proc via Table functions that add the commas to the multiselects' WHERE .IN . Statements in the proc. Your above tutorial works for dropdown lists that are only single select. how can i achieve the same on multi select drop downs thus making the list optional ( at present i get 'a multi value parameter cannot accept null values' ....وسعت
@
@MrIrrepressibleمنذ 5 سنواتwhats happens with multi select drop downs with nulls?
@
@rakesh29novمنذ 11 سنواتHey what to do if i have to add Multiselection values including Null in my parameter list and in my paramter list i am hardcoding the availlable values id="hidden15" class="buttons"> what to do to include null in my paramter list waiting for ur reply WiseOwl ....وسعت
@
@Terracronzمنذ 10 سنواتThat was strange, I write a lot of SQL and didn't understand why the statement (FilmName =@prmFilmName or @prmFilmName IS NULL) would retrieve all the results, that feels like it reads where the film name matches the parameters or where the parameter is null, which doesn't make sense to me why that would work ....وسعت
@
@curious3167منذ 9 سنواتCongrats those are some great tutorials out there. I have a question in this session. I noticed that we cannot choose the option "Allow Null Value" and "Allow Multiple values" at the same time. How do you handle a dropdown list of directors with Null and Multiple values? ....وسعت
@
@jodiezhu5173منذ 10 سنواتThat is a great video! But I also tried to display all the data when start date and end date are null, but didn't figure yet, what would you do to make this work?
@
@RAPTORDARK3منذ 11 سنواتI need your help, How i can do a report whit multiples select and filter fields between them
@
@timothyadammeyerمنذ 8 سنواتHi there. Your tutorials have really helped me learn this stuff. I was wondering if you could help with something. For this tutorial, specifically id="hidden19" class="buttons"> regarding null values for a drop down parameter. In the video, you show us how to do a UNION SELECT to add a null value and float it to the top. But what if your values already have a null? I can sort the results by a ORDER BY CASE clause so that the null comes first; however, after I rename the Null value to say Show All, it places it in alpha order. If I change it to 0-Show All, it moves it to the top but it doesn't auto load it in to the parameter as the optional choice which means the end user still has to pick a value. That's the one thing I can't figure out. ....وسعت
@
@nitintharwani7450منذ 11 سنواتWindows Forms or Web application was my last resort.but thanks again for all the quick replies.:-)
@
@rstanek22منذ 6 سنواتWhat if you use instead of a drop down but the ability to choose multiple values? In the scenario I have I want to be able to choose multiple values and id="hidden20" class="buttons"> also choose the value of null or just the value NULL. Using your examples doesn't work with multiple parameter value selection. ....وسعت
@
@kathryngr021منذ 4 سنواتI tried applying the same code to a shared dataset WHERE field = @parameter OR @parameter IS NULL but it will not populate results. Is there something else that needs to be done here?
@
@fbersolمنذ 9 سنواتHi, I need your help. I have a database for persons and it store yours photos but only de image path, so how to display image from images path stored in my database.
@
@dmitryvakhrushev7916منذ 5 سنواتIf you use this query in the second ("Directors") data set you do not need to remap the parameter to Director ID and add extra sorting to put id="hidden23" class="buttons"> "Show all directors" on top of the list. WITH [cte1] AS ( SELECT [DirectorName] FROM [tblDirector] UNION SELECT NULL ) SELECT * ,[Label] = ISNULL([DirectorName],'Show all directors') FROM [cte1] ORDER BY [DirectorName] ....وسعت1
@
@dhanasekar1966منذ 6 سنواتUNION not working , An error occurred during local report processing. all queries combined using a union, intersect or except operator must have an equal number of expressions in their target lists.
@
@blobbles78منذ 9 سنواتI thought a better implementation would be to have a <Show all> for the directors name, then just leaving it as is. This way the < comes before id="hidden25" class="buttons"> all alpha characters PLUS you can have this as a standard across multiple reports (meaning users always know that to get all they click the <Show all> at the top of every report). Makes for a consistent user experience! ....وسعت
@
@foxybarkمنذ 6 سنواتAt in your video, what if you are working with a list of dates, what would your single quotes contain? Could this even be done with a list of dates?
@
@viralshah5263منذ 9 سنواتHi Wise, I am stuck with my report for parameters. I have created multivalue parameter but i would like to give selection to user that they can id="hidden27" class="buttons"> either select mutiple value or without select any value from drop down and report will work. How would i do that can you please suggest something. ....وسعت
@
@null_x9منذ 3 سنواتWhen we need to add an AND condition.rather than modify the code as shown in the video.can we add another filter expression in the Filter list? Are multiple filter expressions treated as AND between them?
@
@20mim20منذ 10 سنواتi have an urgent issue i really need ur help I am using sharepoint 2010 and I created a list and i want to do this in it the problem is the query looks very different than this is there a way to do it ?
@
@ronpurczynski4803منذ 8 سنواتWhen I use the following statement in the dataset sql query: "WHERE FieldName IN (@prmMyParam) OR @prmMyParam IS NULL" and select id="hidden30" class="buttons"> a single value in preview it works fine, but if I select more than one value I get the following error "An expression of non-boolean type specified in a context where a condition is expected, near ','. I'm using SQL Server 2014. Could this be a data source provider issue? ....وسعت
@
@csanya25منذ 8 سنواتIS NULL is not working for me. Only the error message all the time. :S
@
@rishalinimanoharan1224منذ 9 سنواتhi wise, i need a help form you, im stuck with this problem for a long time. i have three parameters which are startmonth, endmonth and years as drop id="hidden31" class="buttons"> down list, when a user selects all three options, the report should only view sum up of the isuess created within that month and year of selection. im cracking my head so badly and i have no idea how to do this? help me please!! ....وسعت
@
@1snirمنذ 10 سنواتI am Working with SSRS(Sql Server Reporting Services 2012).I have an Datetime parameter.And tick the Allow Null property.In report a checkbox comeup with id="hidden32" class="buttons"> side a caption like NULL.I want to change that caption . Please Help Me(Its valuable for business). ....وسعت
مقاطع الفيديو ذات الصلة على Reporting Services (SSRS) Part 13 - Null Values in Parameters:
Writing a WHERE Clause in a Query
Adding More Parameters
Modifying the WHERE Clause
Adding Even More Parameters
Dropdown List Params
Datasets for Dropdown Lists
Fiters for Dropdown List Params
Appending a Null Value to The List
Masking the Null Value in the List ....وسعت 5
"SELECT DirectorID, DirectorName, DirectorName id="hidden3" class="buttons"> AS SortField
FROM tblDirector
UNION
SELECT NULL, 'Show all Directors', '0'
ORDERBY SortField
"
adding the query's output will help viewers understand the last part of this great video =)
The query output looks like below. Note, a row of "." simply means a valid data record.
DirectorID DirectorName SortField
NULL Show all Directors 0
. . .
. . .
. . .
4 Steven Spielberg Steven Spielberg
48 Steven Soderbergh Steven Soderbergh
. . .
. . . ....وسعت 1
Thank you so much for Preparing and sharing these all.
However i have a requirement similar to this, how can we still have a NULL checkbox as a default value id="hidden9" class="buttons"> which runs report for all directors and when NULL is unchecked we have a drop-down list of DIRECTORS to choose instead of the current scenario where we have to manually input a DIRECTOR'S name.
Your reply for this is much appreciated.cheers ....وسعت
Am working on a report with 12 parameters. 2 x Mandatory Start Date, End Date, 6 x Mandatory Multi Selects and 4 x Optional Multi Selects. My id="hidden14" class="buttons"> multi selects are connected to the dataset stored proc via Table functions that add the commas to the multiselects' WHERE .IN . Statements in the proc.
Your above tutorial works for dropdown lists that are only single select. how can i achieve the same on multi select drop downs thus making the list optional ( at present i get 'a multi value parameter cannot accept null values' ....وسعت
I have a question in this session. I noticed that we cannot choose the option
"Allow Null Value" and "Allow Multiple values" at the same time.
How do you handle a dropdown list of directors with Null and Multiple values? ....وسعت
For this tutorial, specifically id="hidden19" class="buttons"> regarding null values for a drop down parameter. In the video, you show us how to do a UNION SELECT to add a null value and float it to the top. But what if your values already have a null? I can sort the results by a ORDER BY CASE clause so that the null comes first; however, after I rename the Null value to say Show All, it places it in alpha order. If I change it to 0-Show All, it moves it to the top but it doesn't auto load it in to the parameter as the optional choice which means the end user still has to pick a value.
That's the one thing I can't figure out. ....وسعت
WITH [cte1] AS
(
SELECT [DirectorName]
FROM [tblDirector]
UNION SELECT NULL
)
SELECT
*
,[Label] = ISNULL([DirectorName],'Show all directors')
FROM [cte1]
ORDER BY [DirectorName] ....وسعت 1
I am stuck with my report for parameters. I have created multivalue parameter but i would like to give selection to user that they can id="hidden27" class="buttons"> either select mutiple value or without select any value from drop down and report will work. How would i do that can you please suggest something. ....وسعت
I am using sharepoint 2010 and I created a list and i want to do this in it the problem is the query looks very different than this is there a way to do it ?
"WHERE FieldName IN (@prmMyParam) OR @prmMyParam IS NULL"
and select id="hidden30" class="buttons"> a single value in preview it works fine, but if I select more than one value I get the following error "An expression of non-boolean type specified in a context where a condition is expected, near ','.
I'm using SQL Server 2014. Could this be a data source provider issue? ....وسعت
Please Help Me(Its valuable for business). ....وسعت