Drakard
asked on
Report Builder 3.0 optional Parmeters
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.
SELECT
tiburon.INVEH7_VIEW.Report
,tiburon.INVEH7_VIEW.Invl_
,tiburon.INVEH7_VIEW.[Year
,tiburon.INVEH7_VIEW.Make
,tiburon.INVEH7_VIEW.Model
,tiburon.INVEH7_VIEW.Style
,tiburon.INVEH7_VIEW.Color
,tiburon.INVEH7_VIEW.Licen
FROM
tiburon.INVEH7_VIEW
WHERE
tiburon.INVEH7_VIEW.Invl_D
AND tiburon.INVEH7_VIEW.Invl_D
AND tiburon.INVEH7_VIEW.Color LIKE @Color
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.
SELECT
tiburon.INVEH7_VIEW.Report
,tiburon.INVEH7_VIEW.Invl_
,tiburon.INVEH7_VIEW.[Year
,tiburon.INVEH7_VIEW.Make
,tiburon.INVEH7_VIEW.Model
,tiburon.INVEH7_VIEW.Style
,tiburon.INVEH7_VIEW.Color
,tiburon.INVEH7_VIEW.Licen
FROM
tiburon.INVEH7_VIEW
WHERE
tiburon.INVEH7_VIEW.Invl_D
AND tiburon.INVEH7_VIEW.Invl_D
AND tiburon.INVEH7_VIEW.Color LIKE @Color
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually used the above solution in sql query window and then copy pasted it into the query designer of report builder after the fact. The declaration and set sections are not needed there.
Try entering % for the color
WHere are you building the query?
Is it in the report designer of in the database?
mlmcc