SSRS 2008 Dataset Query Command Text Error

Below Query Command Text runs fine in SSRS Dataset to retrieve records. It retrieves data from a function with parameters.

SELECT DISTINCT
SubRegion
FROM WKLD.fnGetOEDGETransReportData(@FromDate,@ToDate,@FromHr,@ToHr,@FromMin,@ToMin,@Office,@Desk,@Shift,null)
ORDER BY 1

Now,  what I am trying to do is add a Where Clause so the query looks like this.

SELECT DISTINCT
SubRegion
FROM WKLD.fnGetOEDGETransReportData(@FromDate,@ToDate,@FromHr,@ToHr,@FromMin,@ToMin,@Office,@Desk,@Shift,null)
Where ActivityName = "TOP"
ORDER BY 1


But, I get the below error.

Invalid column name 'TOP'.

How can I include a Where Clause in this query or can it not be done.
I do not want to change the function since I did not write it unless I have to.
Just want to change this Query Command Text in the SSRS Dataset to include Where Clause.
thayduckProgrammer AnalystAsked:
Who is Participating?
 
thayduckProgrammer AnalystAuthor Commented:
I figured out a way that works:

=IIF(Parameters!Office.Value <> "HOMEWOOD","SELECT DISTINCT SubRegion FROM WKLD.fnGetOEDGETransReportData(@FromDate,@ToDate,@FromHr,@ToHr,@FromMin,@ToMin,@Office,@Desk,@Shift,null) WHERE ActivityName IN ('TOP') ORDER BY 1","SELECT DISTINCT SubRegion FROM WKLD.fnGetOEDGETransReportData(@FromDate,@ToDate,@FromHr,@ToHr,@FromMin,@ToMin,@Office,@Desk,@Shift,null) WHERE ActivityName IN ('TA') ORDER BY 1" )

But curious why the other way does not work.
0
 
ManjuIT - Project ManagerCommented:
Top in your where clause should be in single quotes.

'Top'
0
 
thayduckProgrammer AnalystAuthor Commented:
I tried it that way and it still failed.
0
 
thayduckProgrammer AnalystAuthor Commented:
I am going to close this question since I have a work around.
0
 
thayduckProgrammer AnalystAuthor Commented:
I figured out how to get it to work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.