Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reporting Services parameter and SQL query issue - no rows returned

Posted on 2007-03-21
7
Medium Priority
?
923 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
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…

704 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