04:50 Adding a Filter 05:56 Writing a WHERE Clause in a Query 07:57 Adding More Parameters 08:45 Modifying the WHERE Clause 11:09 Adding Even More Parameters 12:40 Dropdown List Params 13:17 Datasets for Dropdown Lists 15:22 Fiters for Dropdown List Params 17:10 Appending a Null Value to The List 18:38 Masking the Null Value in the List
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 for the 'ALL OPTIONS' (NULL) option as well. Works great. Many thanks!
Great video series! I think for people who are not as familiar with SQL thus the output created from "SELECT DirectorID, DirectorName, DirectorName 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 . . . . . .
Thanks. Well done. One very minor note however, at 20:17 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.
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 trying to list Year Groups and having difficult with a "6 and Below" option.
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 just like normal password fields on login pages....do u have any idea or workaround for this...
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 "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]
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 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
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
At 19:51 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?
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
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?
I'm soo sorry that is such a dumb question to ask. I never realized that. I guess I was in hurry to ask you a question. But thanks a lot for answering back :)
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 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
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? 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.
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 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.
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?
***** Yah!! That works! Thank you! Do you mind to add SSRS videos about passing Parameters to URL and how to encrypt the URL, that will be really helpful as well!
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?
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 what to do to include null in my paramter list waiting for ur reply WiseOwl
Hi there. Your tutorials have really helped me learn this stuff. I was wondering if you could help with something. For this tutorial, specifically 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.
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
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 for creating dropdownlist : 1)Specify Vales 2) Get Values from a query. I don't know how to make it . Please help me
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 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 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?
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.
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 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.
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.
I thought a better implementation would be to have a for the directors name, then just leaving it as is. This way the < comes before 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 at the top of every report). Makes for a consistent user experience!
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 ?
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 also choose the value of null or just the value NULL. Using your examples doesn't work with multiple parameter value selection.
Oh. I figured out. I tried to change the shared dataset but its not correct because only the the actual dataset knows anything about my parameter. Now its working perfectly. Thank you very much for these videos.
When I use the following statement in the dataset sql query: "WHERE FieldName IN (@prmMyParam) OR @prmMyParam IS NULL" and select 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?
Thanks much for your quick reply. I'm trying to understand why the "IN (@prmMyParam)" worked in the tutorial? I guess I'm puzzled as to where the IIF statement (step 2 in the article you linked) should go? I know the "JOIN(@prmMyParam,",") won't parse in the SQL.
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 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!!
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 side a caption like NULL.I want to change that caption . Please Help Me(Its valuable for business).
04:50 Adding a Filter
05:56 Writing a WHERE Clause in a Query
07:57 Adding More Parameters
08:45 Modifying the WHERE Clause
11:09 Adding Even More Parameters
12:40 Dropdown List Params
13:17 Datasets for Dropdown Lists
15:22 Fiters for Dropdown List Params
17:10 Appending a Null Value to The List
18:38 Masking the Null Value in the List
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 for the 'ALL OPTIONS' (NULL) option as well. Works great. Many thanks!
Brilliant, thank you for this information, just what I was looking for. Cheers!
You're welcome!
The best tutorial ever.
Thank you Lourdes!
This is an outstanding series, made me actually want to go through all of them even if they don't all apply, great job
You are a very organized person and I love your work and I am going to finish the whole SSRS series
I enjoyed the video. This chapter is important for any developer. Thanks
That UNION SortOrder field was brilliant btw
Super...................
Thank you so much for Preparing and sharing these all................
best ever tutorial dude ...
Ahh thanks man, appreciate it!
I learned something very useful for my job. Thank you!
I love this episode especially show all directors
Very useful video!
Thank you Hernan!
Great video series! I think for people who are not as familiar with SQL thus the output created from
"SELECT DirectorID, DirectorName, DirectorName 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
. . .
. . .
Thanks. Well done. One very minor note however, at 20:17 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.
Amazing video, it saved my day! :)
Very resourceful session, Thank yoou !!!
Its very helpful and informative.. Thank you so much :)..
very usefull as usual... thx a lot.
Thanks for the help and thanks for the replies to my other questions I had on the other videos :-)
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 trying to list Year Groups and having difficult with a "6 and Below" option.
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 just like normal password fields on login pages....do u have any idea or workaround for this...
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 "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]
Thanks Dmitry!
Amazing! Thank you!
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 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
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
At 19:51 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?
Thank you kindly!
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
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?
I'm soo sorry that is such a dumb question to ask. I never realized that. I guess I was in hurry to ask you a question. But thanks a lot for answering back :)
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 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
Windows Forms or Web application was my last resort....but thanks again for all the quick replies...:-)
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? 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.
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 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.
I need your help, How i can do a report whit multiples select and filter fields between them
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?
***** Yah!! That works! Thank you! Do you mind to add SSRS videos about passing Parameters to URL and how to encrypt the URL, that will be really helpful as well!
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?
Hi, yes that's correct, you can add multiple filters to the filter list of either the dataset or the table which will be treated as AND conditions.
@@WiseOwlTutorials Thanks so much.
@@ESCM-9sb No problem!
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 what to do to include null in my paramter list waiting for ur reply WiseOwl
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?
whats happens with multi select drop downs with nulls?
Hi there. Your tutorials have really helped me learn this stuff. I was wondering if you could help with something.
For this tutorial, specifically 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.
Thank you so much for your response! Once I figure out how to do what you suggested, I should be good to go!
amazing thanks!!!!!!!!!
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
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 for creating dropdownlist : 1)Specify Vales 2) Get Values from a query. I don't know how to make it . Please help me
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 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 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?
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.
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 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.
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.
I thought a better implementation would be to have a for the directors name, then just leaving it as is. This way the < comes before 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 at the top of every report). Makes for a consistent user experience!
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 ?
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 also choose the value of null or just the value NULL. Using your examples doesn't work with multiple parameter value selection.
IS NULL is not working for me. Only the error message all the time. :S
Oh. I figured out. I tried to change the shared dataset but its not correct because only the the actual dataset knows anything about my parameter. Now its working perfectly. Thank you very much for these videos.
When I use the following statement in the dataset sql query:
"WHERE FieldName IN (@prmMyParam) OR @prmMyParam IS NULL"
and select 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?
Thanks much for your quick reply. I'm trying to understand why the "IN (@prmMyParam)" worked in the tutorial? I guess I'm puzzled as to where the IIF statement (step 2 in the article you linked) should go? I know the "JOIN(@prmMyParam,",") won't parse in the SQL.
YOU ARE THE F**** BEST
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 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!!
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 side a caption like NULL.I want to change that caption .
Please Help Me(Its valuable for business).