Reporting Services parameter and SQL query issue - no rows returned

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?


LVL 1
somewherehotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

simon_kirkCommented:
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
YurichCommented:
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
somewherehotAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

YurichCommented:
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
chrismcCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
somewherehotAuthor Commented:
Thanks Chris,

You make me look good!

Jp
0
chrismcCommented:
Glad to be of assistance!

Thanks for the points.

Cheers
Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.