Report Builder and optional parameters

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 of products grouped by the name of the supplier. I want users to be able to see the entire list, or narrow it down to 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 the parameter expression once. So I cannot check for empty value and apply the filter in the same expression.

As a last resort, I used the following workaround: created a new formula named "Enter 'ALL' to see all suppliers" that would return text 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 if I could create 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 guess or remeber the name of supllier

if you have any idea, post it

Ruvy

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:

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

2. 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 that it doesn't allow me to generate the report with an empty parameter. any advice

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")