?
Solved

Report Builder 3.0 optional Parmeters

Posted on 2014-09-17
3
Medium Priority
?
456 Views
Last Modified: 2014-09-23
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_No
  ,tiburon.INVEH7_VIEW.Invl_Date
  ,tiburon.INVEH7_VIEW.[Year]
  ,tiburon.INVEH7_VIEW.Make
  ,tiburon.INVEH7_VIEW.Model
  ,tiburon.INVEH7_VIEW.Style
  ,tiburon.INVEH7_VIEW.Color
  ,tiburon.INVEH7_VIEW.License_No
FROM
  tiburon.INVEH7_VIEW
WHERE
  tiburon.INVEH7_VIEW.Invl_Date >= @Invl_Date
  AND tiburon.INVEH7_VIEW.Invl_Date <= @Invl_Date2
  AND tiburon.INVEH7_VIEW.Color LIKE @Color
0
Comment
Question by:Drakard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 40329186
I assume if you enter a color like Blue the query works.

Try entering % for the color

WHere are you building the query?
Is it in the report designer of in the database?

mlmcc
0
 

Accepted Solution

by:
Drakard earned 0 total points
ID: 40330600
Here's what I settled on through some in-house collaboration.
The problem was needing to allow for blank fields with multiple parameters.

DECLARE @Invl_Date as Date
DECLARE @Invl_Date2 as Date
DECLARE @Color as  Varchar(7)
DECLARE @LicN varchar(10)
DEClARE @Year Varchar(4)
DECLARE @Make Varchar(4)
DECLARE @Model varchar(3)
DECLARE @Style varchar(2)


SET @Invl_Date = '01/01/2009'
SET @Invl_Date2 = '12/31/2009'
SET @Color = 'blu'
SET @LicN = ''
SET @Year = ''
SET @Make = 'ford'
SET @Model = ''
SET @Style = '2d'


SELECT
  tiburon.INVEH7_VIEW.Report_No
  ,tiburon.INVEH7_VIEW.Invl_Date
  ,tiburon.INVEH7_VIEW.[Year]
  ,tiburon.INVEH7_VIEW.Make
  ,tiburon.INVEH7_VIEW.Model
  ,tiburon.INVEH7_VIEW.Style
  ,tiburon.INVEH7_VIEW.Color
  ,tiburon.INVEH7_VIEW.License_No
FROM
  tiburon.INVEH7_VIEW
WHERE tiburon.INVEH7_VIEW.color like case WHEN Len (@color) = 0 then '%' + @color else @color end
  AND tiburon.INVEH7_VIEW.License_No like case WHEN Len (@LicN) = 0 then '%' + @LicN else @LicN end
  AND tiburon.INVEH7_VIEW.[Year] like case WHEN Len (@Year) = 0 then '%' + @Year else @Year end
  AND tiburon.INVEH7_VIEW.Make like case WHEN Len (@Make) = 0 then '%' + @Make else @Make end
  AND tiburon.INVEH7_VIEW.Model like case WHEN Len (@Model) = 0 then '%' + @Model else @Model end
  AND tiburon.INVEH7_VIEW.Style like case WHEN Len (@Style) = 0 then '%' + @Style else @Style end
  AND tiburon.INVEH7_VIEW.Invl_Date >= @Invl_Date
  AND tiburon.INVEH7_VIEW.Invl_Date <= @Invl_Date2
0
 

Author Closing Comment

by:Drakard
ID: 40338652
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question