Solved

Reporting Services parameter and SQL query issue - no rows returned

Posted on 2007-03-21
7
915 Views
Last Modified: 2008-02-01
I'm having a lot of trouble adding parameters in reporting services.
Basically I would like for the user to be able to enter a choice of parameters to retrieve data from the query,
The parameters are:
Job_number
Job_Title
Cust_Name
Project_Number

I also want them to be able to enter just part of the field.
For example if the cust_name was "bloggs", and the job_title was "Cooking with Apples", the user should be able to find this using the parameters
Cust_Name = "Blo"
Job_Title = "Apples"

I have set the database fields for these as type Char (although I'm happy to change them to anything else to get this working)

My problem is that nothing seems to work. This is the result of a trace when running the query

exec sp_executesql N'SELECT Job_Bag_Main.*, jobtitle AS Expr1, Customer1 AS Expr2, project_number AS Expr3 FROM Job_Bag_Main WHERE (jobnumber LIKE ''%'' + REPLACE(@P1, '' '', '''') + ''%'') AND (jobtitle LIKE ''%'' + REPLACE(@P2, '' '', '''') + ''%'') AND (Customer1 LIKE ''%'' + REPLACE(@P3, '' '', '''') + ''%'') AND (project_number LIKE ''%'' + REPLACE(@P4, '' '', '''') + ''%'')', N'@P1 char(10),@P2 char(50),@P3 char(50),@P4 char(53)', '0         ', '0                                                 ', 'Mitch                                             ', '0                                                    '
I thought that the spaces were the problem, so I added the replace function for each parameter to remove the spaces, but still no joy.

Current the sql looks like this:
SELECT     Job_Bag_Main.*, jobtitle AS Expr1, Customer1 AS Expr2, project_number AS Expr3
FROM         Job_Bag_Main
WHERE     (jobnumber LIKE '%' + REPLACE(@Job_Number, ' ', '') + '%') AND (jobtitle LIKE '%' + REPLACE(@JobTitle, ' ', '') + '%') AND
                      (Customer1 LIKE '%' + REPLACE(@Cust_Name, ' ', '') + '%') AND (project_number LIKE '%' + REPLACE(@Project_Number, ' ', '') + '%')

Can anybody help, please?


0
Comment
Question by:somewherehot
[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
  • 2
  • 2
  • +1
7 Comments
 
LVL 14

Expert Comment

by:simon_kirk
ID: 18766871
Hi.
Your better off creating a SP and then using this within the report.  
Can you give me the datatypes of the fields and I'll post the SQL you need:
 jobnumber
 jobtitle
 customer1
 project_number

@

CREATE PROCEDURE sp_YourSPName(@JobNumber As [***]
SELECT     Job_Bag_Main.*, jobtitle AS Expr1, Customer1 AS Expr2, project_number AS Expr3
FROM         Job_Bag_Main
WHERE     (jobnumber LIKE '%' + REPLACE(@Job_Number, ' ', '') + '%') AND (jobtitle LIKE '%' + REPLACE(@JobTitle, ' ', '') + '%') AND
                      (Customer1 LIKE '%' + REPLACE(@Cust_Name, ' ', '') + '%') AND (project_number LIKE '%' + REPLACE(@Project_Number, ' ', '') + '%')
0
 
LVL 21

Expert Comment

by:Yurich
ID: 18766875
a few pointers:

try to work from small to big - try to get it working with one definite parameter, like that:
SELECT *
FROM Job_Bag_Main
WHERE jobnumber = @jobnumber

then move on to have one not definite parameter:
SELECT *
FROM Job_Bag_Main
WHERE jobnumber LIKE '%' + @jobnumber + '%'

you can try to get it working first in query analyzer

be careful with datatypes - is your job number an int or char or varchar?

don't use REPLACE, use RTRIM - if it's a fixed size field, you shouldn't have any leading spaces.

if your values are not of the fixed size, why do you use chars? use varchar and you won't have extra spaces.

* not exactly related, but still useful - try using stored procedures where possible (like this case)

Good luck,
Yurich
0
 
LVL 1

Author Comment

by:somewherehot
ID: 18769481
The datatypes can be anything we want, so please feel free to make the SQL I need. This project is in development so we can do anything we want at this stage.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 21

Expert Comment

by:Yurich
ID: 18769527
Try following the simple step I described and let us know about your progress (try with one explicit parameter first, then with implicit (LIKE), then add another parameter)...
0
 
LVL 18

Accepted Solution

by:
chrismc earned 500 total points
ID: 18769549
If any of the parameters contain Null, it won't select, for each parameter your testing do an IsNull around it.
i.e.  IsNull(@P1,'')

Also if the user intentionally wanted a space between words, the replace will stop that working. If you need to clear spaces use LTrim and RTrim. In fact you could make an assumption that they would be no superfluous spaces on the left anyway and just use RTrim.

I'd suggest;

Select @JobTitle = '%' + RTrim(IsNull(@JobTitle, '')) + '%', @JobNumber = '%' + RTrim(IsNull(@JobNumber, '') + '%', ... etc ...

Select * From Job_Bag_Main
    Where IsNull(JobNumber,'') Like @JobNumber
        And IsNull(JobTitle, '') Like @JobTitle
        And ...etc...

Because you are 'AND'-ing everything, every condition must be true. If you don't test for it, one Null in there will make it false.

If you still have a problem, follow Yurich's advice and comment out each AND clause in turn to find the field/parameter causing the problem.

Cheers
Chris
0
 
LVL 1

Author Comment

by:somewherehot
ID: 18771426
Thanks Chris,

You make me look good!

Jp
0
 
LVL 18

Expert Comment

by:chrismc
ID: 18773654
Glad to be of assistance!

Thanks for the points.

Cheers
Chris
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

710 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