Report Builder 3.0 optional Parmeters
Posted on 2014-09-17
I'm currently working with Report builder 3.0
The database I work with used to be oracle 9i and has been translated to a SQL database.
I'm used to working with crystal reports but it is no longer being purchased and we are going to use the free report builder.
I work for a law enforcement agency and have been tasked for creating a query to list vehicles with different amounts of suspect information. We choose a date range of records and put in all the partial information and we have a list of people to start looking at. Currently a new report is being created for each situation and that works fine. Sometimes we have make color and style but no tag. Other times we a partial license plate, make, style.
I wanted to make a query that lists all the required fields as parameters which can be entered or not. If nothing is chosen I want all the values to be given or if a value is given then I want it to filter on that parameter. For simplicities sake I'm only working with color now. I figure the others will be easy to mimic if i can get this one field to work as expected.
I suspect I need an IIF statement for if @color is null then !tablename.color else Tablename.color = '%' + @color + '%'
I haven't found a way to do this with context menus. I have tried to mimic other peoples solutions to this problem and simply replace my table names but the text editor finds problems with my syntax. Here's what I have so far and it produces the report but it requires a default value.
tiburon.INVEH7_VIEW.Invl_Date >= @Invl_Date
AND tiburon.INVEH7_VIEW.Invl_Date <= @Invl_Date2
AND tiburon.INVEH7_VIEW.Color LIKE @Color