Where clause predicate works as expected in SSMS but not in SSRS

Question

Where clause predicate works as expected in SSMS but not in SSRS

SSSolice

SSCommitted

Points: 1907
More actions
September 18, 2009 at 10:59 am

#211011

I have a payroll report that needs the addition of two input parameters. One for Associate and one for Position. Both parameters need to handle a choice of 'ALL' or one specific value. So the possible choice combinations would be:
AssociateID&nbsp&nbsp&nbspPosition
ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspALL
123&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspALL
123&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspContractor
ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEmployee
Where Clause:
WHERE
CASE
WHEN VwPayroll.IntAssociate = @Associate AND @Position = 'ALL' THEN 1
WHEN VwPayroll.IntAssociate = @Associate AND VwPayroll.strDetailsAssociatePosition = @Position THEN 1
WHEN @Associate = '99999' AND VwPayroll.strDetailsAssociatePosition = @Position THEN 1
WHEN VwPayroll.IntAssociate = @Associate THEN 1
WHEN @Associate = '99999' AND @Position = 'ALL' THEN 1
END = 1
Parameter For Associate:
Select '99999' as AssociateID, ' ALL' as AssociateName
UNION ALL
Select Distinct
VwPayroll.IntAssociate As AssociateID
,VwPayroll.strFirstName + ' ' + VwPayroll.strLastName as AssociateName
From VwPayroll
The AssociateID is the parameter query value and the Name is the parameter label. Note that Associate 99999 equates to 'ALL' in the Where clause.
Returns:
AssociateID Name
99999&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspALL
123&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAlice Johnson
456&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSteve Smith
789&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspRachel Edwards
...
...
...
Parameter For Position:
Select
' ALL' as Position
UNION ALL
Select Distinct
VwPayroll.strDetailsAssociatePosition As Position
From VwPayroll
Returns:
Position
ALL
Contractor
Employee
My query returns the expected result in SSMS and in query designer (data tab) in SSRS with every possible combination but when I preview the report in SSRS I do not get the expected result when I select 'ALL' and 'ALL' on my two parameters. It returns and empty result set.
Any help or suggestions are greatly appreciated.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

SSSolice SSCommitted Points: 1907 More actions · Answer 1

Not 10 seconds after I posted this I spotted the problem. If you notice there is a space after the single quote in the Position parameter before the word ' ALL'. I noticed this because of the contrast of red against white in the html code when I opened my posting to view it.

I suppose the lesson is: view all your code in SSMS because SSRS does not color format the keywords in the query designer making it more difficult to spot this kind of time-lost, meat-head mistake.