Hi, Is it possible to create optional parameters in Report Builder? An *ordinary* parameter is created by marking a filter expression as "prompted". At run-time, the user will be asked to provide a value for this parameter (or accept a default value if it is available). This works fine. What I want is to let the user choose whether to provide a value, or leave the parameter empty. In latter case, the reports should not take the parameter into account and display all available data. Example:I have a list ofproductsgrouped bythe name of the supplier. I want users to be able to see the entire list, or narrow itdownto one supplier only. So I create a free text parameter that will contain a part of the supplier's name. It works ok, but if parameter is left blank, or set to NULL, the report will display no data (all products always have their suppliers). I tried to create a filter formula to check for empty value and bypass the filter if necessary, but the formula only allows me to use theparameter expression once. So I cannot check for empty value and apply the filter in the same expression. As a last resort, I usedthe following workaround: created a new formula named "Enter 'ALL' to see all suppliers" that would returntext constant"ALL". I then added another prompted filter expression for this field and grouped it with my supplier prompt using "Any of" group. User is now able to choose a supplier, or enter "ALL" to the second prompt field. This clumsy approach actually works, but then another problem emerges - although I mark both filter fields as Prompted, the Report Builder will forget this flag for one of the fields when the report is reopened. I think it could be a bug (we use SQL2005 SP1) Anyway, it'd be nice ifI couldcreate an optional parameter as one expression, not two. Is there any way to do that? TIA Denis

Denis, I ran into this same problem today and spent sometime trying to come up with a solution or hack. The problem is report builder does not allow access to the parameters collection nor do you have the ability to add a parameter when you choose to create a filter formula. I was able to get around this limitation by following the below steps. I did run a trace after making the changes and the SQL statement that reporting services created was what I was expecting. I verified the solution with a string field filtering by equality and contains. 1. With the filter edit form open add a field to the filter.2. Right click on the field and choose "Prompt"3. Right click on the comparison operator and choose "From...To". This is a key step as it will create two parameter values to work with.4. Right click on the filed name again, but choose "Edit as Formula" this time.5. In the formula text box update the text as follows. Note* you can not delete the parameter names. You must type the formula without removing the existing parameters. The FIND operator converts the formula to a version of an SQL LIKE clause. OR(FIND(<<field name>>,<<param name>>) <> 0, <<param name>> = EMPTY) The above formula will return all rows if the "NULL" check box is left checked and no filter is specified. If a filter is specified, it will return only records that contain the filter. The above formula will produce the below SQL statement WHERE (NOT(ISNULL(CHARINDEX(<<param name>>,<<field name>>), 0) = CAST(0 AS INT)) OR <<param name>> IS NULL) If anyone else has come across this problem and found a more elegant solution, please post.

Kevin, I should have found this myself! Yes it works just as I wanted, I can either enter filter value or set it to NULL and have all the records. Brilliant! Also solved the bug with not remembering "Prompt" flag of one of the parameters in a filter group. I don't think there is more elegant solution than yours, unless MS guys will add "Prompt as optional" flag, or something like this. Denis

What Filter tab are you uys talking about? I have RS2005 and I don't see the "prompt" option. Is this in the data filter section?

We are talking about Report Builder, a small application designed to allow end users to create ad hoc reports based on data models. RB has serious limitations in the way the report parameters are handled. You cannot explicitly create parameters, but only set "Prompt" flag for filter conditions. You can see "Prompt" when you right-click on the name of the expression in the Filter Data window. I'm new to SSRS and may be mistaking, but I think if you have RS2005, you should also have Prompt option. Dunno about RS2000 though. Denis

hi Denis, thank for your post after reading your message i did this OR(<<field name>> = <<param name>>, <<param name>> = EMPTY) it works, but imerge another problem the parameter uses text box, not checkbox the user has to guessor remeber the name of supllier if you have any idea, post it Ruvy

Hello,I'm having the same problem. I would like to add a filter that would allow the report user to only see rows that are of interest to the user. But, I would also like to provide the option to see the entire result set. I tried two approaches, and neither worked.Approach 1 - Specify a report parameter with the label All, and the value % (i.e. the wildcard character). For this case I used the filter operator "like", because the value to be compared is a text field. This didn't work.Approach 2 - Specify a report parameter with the label All, with the value specified as a comma separated list of all possible values for the field. For this case I used the filter operator "in", because, at least in T-SQL, this operator takes a comma separated list. This didn't work either.I entered the report parameters as non-queried values in the (Label, Value) input box in Visual Studio.Why didn't these approaches work. What is the best way to accomplish this?

Found an acceptable solution to my own problem. Instead of directly providing an "All" option for filtering the result set (in other words no filtering), I defined my report parameters as "Multi-value". Now when the user wants to run the report, he or she must first select, from a drop down list, the filter value to be applied. Because the report parameter is defined as "Multi-value", one, or more values may be selected. The available options are presented to the user with a check box next to each option. So the net effect is that the user can see ALL of the rows (by selecting ALL of the defined report parameters), or the user can select just a single value. Note, this solution would become very clumsy if the number of possible values for a column is large,-Brandon55

I am also having this problem. The above solution isn't bad, but unfortunately I fall into the category of "number of possible values for a column is large". My situation is rows of data that contain a customer number, invoice number, month, and year. I created an ASP page months ago that have four text boxes the user can enter info in to narrow down their search of the data. They can put in an customer number and see all their invoices. They could put in a customer number and year and see all their invoices for that year, or they could put search parameters into each box and get one specific invoice at one specific date. Well, frankly I think my ASP page is ugly and I wanted to put this into SSRS (because I've recently been learning it and love it), but the inability to mimic my ASP page and have optional search parameters is becoming ridiculous. How can such a robust tool as SSRS leave this out? Has anyone else come up with solutions? I am using the report designer. Thanks!

It looks like I found a solution to my problem. I was able to use wildcards and get the results I was looking for. I'll demonstrate. Here is the SQL behind my dataset:select * from customermerges where customername like '%' + @cusname + '%' and month like @monthorder by year desc My @cusname parameter is such that the user will enter text. So it is set up in the SQL so that the user can search for a customer in many ways. For instance, if they are looking for Burger King (not one of our customers) they could type in Burger or King or Burger King. My @month parameter has non-queried values in it listed as ALL, January, February, March, etc. The values for those labels are the same as the labels, i.e., January, February, etc. The value for the ALL label is simply a percent sign (%) wildcard. This allows the user to select ALL from the drop down and get everything. I have also set that parameter to accept blank values. So, if the user wanted everything, they could choose ALL for the month and put nothing in the customer name parameter and they would get the whole list. I hope this helps anyone else out having trouble with this.

Thanks for posting your solution 1. In your solution are you referring report services developer designer or report builder You loose the benefit of letting user pick customer name from list, correct me I am wrong. Ruvy

Hello, I follow the steps given to edit the formula on Report Builder, OR(FIND(field name,parametername)<>0,parametername = EMPTY) and didn't work for me. The problem is thatit doesn'tallow me to generate the report with an empty parameter. any advice?

Try instead of empty to put string like 'All'

1. I am referring to the Visual Studio report designer. 2. I never had the user picking customer name from a list. The list is way too large to allow that. They simply type in the customer name or part of the customer name to search for it.

It worked.Thanks! OR(FIND(field name,parametername)<>0,parametername = "ALL")

Hi I am happy it works why you need the find, does it have any advantage on OR(<<field name>> = <<param name>>, <<param name>> = 'All') ruvy

If your user selects "January" and "February", your query will not work - the "LIKE @MONTH" willthrow an error messagesince the returned valuein @MONTHwill be "January,February".

That would be true if I allowed the user to type in what month(s) they want. However, I have it in a drop-down list where they can choose from ALL, January, February, March, etc. The drawback is they can only look at data for one discrete month, or all months, but not just a couple months (like in your example). My users don't mind, though. Now, for Customer they do enter text and could break the report by using your method of entering more than one value seperated by a comma. However, when my users use this report they only ever need to enter one customer. I've never had a problem with the report yet and if someone did enter more than one customer on the line and got an error I would get the phone call and I could simply explain to them how it should work. But I see what you're getting at, and if I didn't have 100 things to do I mightbuild more error checking and correction into the report. I know.... poor excuse.

Hi Denis, I have a promp parameter in report builder which is a drop down list. If I apply your above steps for drop down list , when I am running the report I can see a text box instead of drop down list. Also if I keep it empty and click on view report button, alert is throw that the field cannot be blank. I want to have a optional parameter for drop down, and if it is left blank the alert should not be thrown. Also can you help how to set multi-value parameter in report builder. My model is based on Analysis service cube. Thanks

Did you get multi-value select works in filter (Report builder) ? I also try out OR(FIND(<<field name>>,<<param name>>) <> 0, <<param name>> = EMPTY) OR(FIND(Fund_Project_Dim_ID, Parameter:Fund_Project_Dim_ID) <> 0, Parameter: Fund_Project_Dim_ID = EMPTY) OR(FIND(<<field name>>,<<param name>>) <> 0, <<param name>> = "ALL") OR(FIND(Fund_Project_Dim_ID, Parameter:Fund_Project_Dim_ID) <> 0, Parameter: Fund_Project_Dim_ID = "ALL") But keep getting Formula Error : The arguments to the following function are not valid: FIND <OK> Please help! Also how did you populate the list so that the user can choose from?

I am running into a problem with prompt values and date criteria. I build reports and use the filter to set up multiple prompt options with default values,I then use the subscription functionality to condition the reports with different data sets depending on who the report is being set to. My issue is the lose of the default prompted value for date data. In no consistant fashion I will set up a reports filter options, save it and get the subscriptions values set and somehow, somewhere the default values gets lost and the report errors 'The subscription contains parameter values that are not valid.' When I try to modify the report in the builder I get the following error 'Value expression has the wrong type'. This then indicates I can save my report and reopen it and then modify the value - this does not work. So far the only solution is to rebuild the report but I still need the prompted aspect, sometimes the reports will work as I need them to and others just lose the value. Has anyone else ran into this issue? The issue is only related to date type values used as prompts with default values like 'last 7 days'.

Kevin, I created the Filter But I cannot find the "Prompt" option in the field.

Hi, I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this. my query is: "select username from registration where username like '" + TextBox1.Text + "%'"; It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.

I believe I am running into a similar problem ... with maybe a slightly different twist. I have built my report w/ a Prompted Date filter ... using Relative Dates, I selected "Last x days" and selected 1 for my default value. Saved the report, run it, subscribe to it ... all works as expected. I found that using prompted filters works well to display the filter "values" in a text box (e.g. a Production Date) ... but was only able toaccomplishthis in the following manner... =DateAdd("d",-Parameters!DateDateinlastndays.Value,Now()).ToString("MM/dd/yyyy") Again, everything works as expected. However, when I pull up the saved report in Report Builder and go to the Filters, my Date filter is no longer "Prompted" and my default value is gone. And then, I can no longer Save the report without getting this error ... The Value expression for the textbox Title refers to a non-existing report parameter DateDateinlastndays. ---> The Value expression for the textbox Title refers to a non-existing report parameter DateDateinlastndays. (because my default value is gone) Slightly different issue but similar to yours. Any thoughts?

What's the formula for an optional RANGE filter? Since you start off with a prompted range filter, and modify it into an optional "equals" filter - well what if you wanted an optional RANGE filter? What about an optional greater than, or less than filter? What about an optional LIKE filter, or CONTAINS filter? Thanks! It's really frustrating that something that is so rudimentary is missing from report builder.

Hi, I am trying to get this scenario to work in the report builder also but can seem to manage it. Here is what I would like a dropdown that displays possible selections and if no selection is made the entire set is returned. I can get the prompt dropdown to display in the filter but it forces a selection. I have tried all the various suggestions with no luck. Either my code for the filter formula is wrong or I am missing something. Thanks for any help.

I figured out what I needed to do. Select Prompt and then choose the IN LIST operator. This will provide you with a dropdown checkboxlist with the ALL option at the top. I guess I was making it more complicated then necessary.

I am also having this same exact problem! When the default values gets lost and the report errors 'The subscription contains parameter values that are not valid.' When I try to modify the report in the builder I get the following error 'Value expression has the wrong type'.

Philip, How did you set up your ASP page with the four parameters? I want to create a similar page. What is the code behind? Your help would be appreciated. Tim

Hi, Thanks in Advance. I am working in report builder to create ad-hoc reports. I have a filter applied for one of the field say, SellersAccountSaleMemberID ( This is a list with some members). I need to change the Name of the parameter that comes by default. How to change the parameter name / filter name in report builder? If i say Right click , edit as formula, it changes the parameter itself, when i try to rename it. Cheers..JD

Hi I have tried using the code that you gave and I am getting the following error (the following syntax error occured. Expected end of formula). And(court session >= Parameter: Court Session, Court Session <= Parameter:Court Session)or(find(CourtSession, Court Session) <> 0,Court Session = empty) It dose not seem to like the Or

You need to rewrite the OR using function notation (similar to Excel): OR(AND(Court Session >= Parameter: Court Session, Court Session <= Parameter: Court Session), FIND(CourtSession, Court Session) <> 0, Court Session = empty) Seems like you're also missing some quotation marks in the FIND function above but I can't remember which argument is which off the top of my head. Or may CourtSession and Court Session are both field references?

Please Help,I am in similar kind of problem but it did not work for me. I tried OR (First Name = Parameter: First Name, Parameter: First Name = EMPTY)and tried OR (First Name = Parameter: First Name, Parameter: First Name = "ALL").Its throwing me an warning(error) saying that " Please enter a value for the Parameter 'First Name'. The parameter cannot be blank.I have three parameter prompted to the user as 'First Name', 'Last Name' and 'ID'. I would like the user to insert only one value and leave the other two blank to get the result.thanks

Hi, Just started using SSRS Report Builder 2.0 a few weeks ago. We have a report that we want to allow a default of All values - i.e null search condition, and also provide a selection list of the available options for Product Group. I like the parameter option of Allow null value, however, this does not work with a selection list of available records from the db. We are using the edit formula OR(<<field name>> = <<param name>>, <<param name>> = EMPTY - thanks for this solution. What we did in our data model is to add a new object - "Product_Group_List". This object uses the following SQL statement to get the available records from the db, but also provide for a null value. Select null as 'Value', 'All' as 'Label' Union all Select field_value as 'Value', field_label as 'Label' from <tablename> with (nolock) where field_value = <search condition> This allows the use of the Available Values property - Get values from a query where the Dataset is Product_Group_list and the Value and Label fields are used. We also set the Default Values property to Get values from a query, where the Dataset Product_Group_list is used setting Value field = Value. This allows the users to effectively use the same function as the Null checkbox when executing the report by just leaving the "All' value selection unchanged. However, it provides a list for the users to select from using the dynamic values from the db. - Rod