Solved

Reporting Services parameter and SQL query issue - no rows returned

Posted on 2007-03-21
7
919 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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