Help With Filtering Results In Reporting Services/ Or SQL part

[Jdbenike]

I have a code in reporting services

SELECT*, 
UNPLANNED = (ACTUALHOURSWEEKTODATE + SCHEDULEDHOURSREMAINING) - SCHEDULEDHOURS,
ANTICPATED = (ACTUALHOURSWEEKTODATE + SCHEDULEDHOURSREMAINING)
FROM(
SELECT DISTINCT
       A.HOMELABORLEVELNAME2,
       A.HOMELABORLEVELNAME3,
       A.HOMELABORLEVELDSC4,
       A.PERSONFULLNAME, 
       A.WORKERTYPENM,
       SUM(CAST(DATEDIFF(N, B.SHIFTSTARTTIME, B.SHIFTENDTIME) AS FLOAT) /60) AS SCHEDULEDHOURS,
       (SELECT SUM(CAST(DATEDIFF(N, C.SHIFTSTARTTIME, C.SHIFTENDTIME) AS FLOAT) /60) 
		  FROM VP_SCHEDULE C 
          WHERE A.PERSONNUM = C.PERSONNUM 
          AND CONVERT(VARCHAR(10), C.SHIFTSTARTDATE, 120) >= CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() - 4, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() - 5, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE() - 6, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() , 120)
		  WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() - 1, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() - 2, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 120) END
          AND CONVERT(VARCHAR(10), C.SHIFTENDDATE, 120) < CONVERT(VARCHAR(10), GETDATE(), 120))AS SCHEDULEDHOURSWEEKTODATE,
	   (SELECT SUM(CAST(E.TIMEINSECONDS AS FLOAT) /60/60)
		  FROM VP_TOTALS E
          WHERE A.PERSONNUM = E.PERSONNUM
          AND E.PAYCODENAME = '00 Total Worked Hours'
		  AND CONVERT(VARCHAR(10), E.APPLYDATE, 120) >= CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() - 4, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() - 5, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE() - 6, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() , 120)
		  WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() - 1, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() - 2, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 120) END
          AND CONVERT(VARCHAR(10), E.APPLYDATE, 120) < CONVERT(VARCHAR(10), GETDATE(), 120)) AS ACTUALHOURSWEEKTODATE,
       (SELECT ISNULL(SUM(CAST(DATEDIFF(N, D.SHIFTSTARTTIME, D.SHIFTENDTIME) AS FLOAT) /60),0) 
		  FROM VP_SCHEDULE D 
          WHERE A.PERSONNUM = D.PERSONNUM 
          AND CONVERT(VARCHAR(10), D.SHIFTSTARTDATE, 120) >= CONVERT(VARCHAR(10), GETDATE(), 120)
		  AND CONVERT(VARCHAR(10), D.SHIFTENDDATE, 120) <= CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() + 2, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() + 1, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE(), 120) 
		  WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() + 6, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() + 5, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() + 4, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() + 3, 120) END) AS SCHEDULEDHOURSREMAINING,       
       CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() - 4, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() - 5, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE() - 6, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() , 120)
		  WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() - 1, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() - 2, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 120) END AS BEGINDATE,
       CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() + 2, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() + 1, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE(), 120) 
		  WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() + 6, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() + 5, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() + 4, 120)
		  WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() + 3, 120) END AS ENDDATE
FROM   VP_EMPLOYEE A,
       VP_SCHEDULE B
WHERE A.PERSONNUM = B.PERSONNUM
AND A.HOMELABORLEVELNAME2 IN (@Area) 
AND A.HOMELABORLEVELNAME3 IN (@Store)
AND CONVERT(VARCHAR(10), B.SHIFTSTARTDATE, 120) >= CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() - 4, 120)
WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() - 5, 120)
WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE() - 6, 120)
WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() , 120)
WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() - 1, 120)
WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() - 2, 120)
WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 120) END
AND CONVERT(VARCHAR(10), B.SHIFTENDDATE, 120) <= CASE WHEN DATENAME(dw, Getdate()) = 'Thursday' THEN CONVERT(VARCHAR(10), GETDATE() + 2, 120)
WHEN DATENAME(dw, Getdate()) = 'Friday' THEN CONVERT(VARCHAR(10), GETDATE() + 1, 120)
WHEN DATENAME(dw, Getdate()) = 'Saturday' THEN CONVERT(VARCHAR(10), GETDATE(), 120) 
WHEN DATENAME(dw, Getdate()) = 'Sunday' THEN CONVERT(VARCHAR(10), GETDATE() + 6, 120)
WHEN DATENAME(dw, Getdate()) = 'Monday' THEN CONVERT(VARCHAR(10), GETDATE() + 5, 120)
WHEN DATENAME(dw, Getdate()) = 'Tuesday' THEN CONVERT(VARCHAR(10), GETDATE() + 4, 120)
WHEN DATENAME(dw, Getdate()) = 'Wednesday' THEN CONVERT(VARCHAR(10), GETDATE() + 3, 120) END
GROUP BY A.PERSONFULLNAME, A.WORKERTYPENM, A.PERSONNUM, A.HOMELABORLEVELNAME2, A.HOMELABORLEVELNAME3,A.HOMELABORLEVELDSC4
)
tbl

In the report I need to add a filter to the 'ANTICPATED' field above. Something like, Anticpated > 40.00 only.
This is where ANTICPATED is delcared in code only.

SELECT*, 
UNPLANNED = (ACTUALHOURSWEEKTODATE + SCHEDULEDHOURSREMAINING) - SCHEDULEDHOURS,
ANTICPATED = (ACTUALHOURSWEEKTODATE + SCHEDULEDHOURSREMAINING)
FROM(....

So when I add a filter in the properties like Field!Anticpated > 40 to the table, it comes back with an error saying can't convert double to string. I tried a ton of different approaches. Can someone help me? I only want to bring back results 'greater than or less than' a value I enter here.

 

[NathanGriffiths]

Your filter should be written as;

Fields!Anticpated.Value > 40

If this gives the same error then check the data type of the column being returned as ANTICPATED - you may need to cast it as an INT or NUMERIC data type.

You should also consider adding the filter to the SQL query instead of in the report itself - this generally gives better performance as less data is returned so the report server has less work to do (particularly relevant if the query might return a large amount of data which then has to be filtered by the report).