Solved

Reporting Services parameter and SQL query issue - no rows returned

Posted on 2007-03-21
7
913 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now