Query In SSRS
Hi, I want to query the users who did not visit the website from past 3 years. How do i write query for this. I tried using Calculated formula =DateDiff("yyyy",Fields!SCHEDULED_DATE.Value,Now())>= 3. The calculated column shows False for each row and it shows an error "The Value expression for the field ‘SCH_DATE’ contains an error: Operator '>=' is not defined for type 'Date' and type 'Integer'". And Also I want to group the report by user name. i tried using group but the results were weird. Each row showed the username. Any ideas please? Thanks
June 20th, 2011 5:49pm

is Scheduled_Date a date field? Sounds like it might be integer type.
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2011 7:09pm

Scheduled_date is a Date and Time field. Thanks
June 20th, 2011 7:21pm

Hi fadnan, Thanks for your question. Based on your description, I understand that the type of the SCHEDULED_DATE is date/Time, you would like to compare the SCHEDULED_DATE’s value with the value of Now() function on the year level, however, it displays an error of type conflict. Actually, there are two different actions for your requirement, one is in T-SQL query level, another one is add a filter on your Dataset, in this action, please remember that setting the expression’s data type of DateDiff("yyyy", Fields!SCHEDULED_DATE.Value, Now()) is Integer, for detail steps as below, Action one, using DATEDIFF () function in the T-SQL query of the Dataset, just like this: select SCHEDULED_DATE from TableName where DATEDIFF(YYYY, SCHEDULED_DATE,GETDATE())>3 Action two, using DateDiff () function in a filter of the Dataset, detail steps as below, 1. Type in your Dataset’s Query dialog box like this, select SCHEDULED_DATE from TableName 2. Right-click the handle of the detail row in your tablix, select Tablix Properties. 3. In the Tablix Properties dialog box, click Filters in the left pane. 4. Click Add button, click the fx button on the right of the Expression drop-down list, type in the Expression dialog box like this: =DateDiff("yyyy",Fields!CurrentTime.Value,Now()), click OK. 5. Select Integer in the drop-down list of Type, select > in the drop-down list of Operator, type 3 in the text box of Value,click OK. If your issue still exsits, please feel free to let me know. Thanks, Sharp Wang Best Regards. Sharp Wang
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2011 11:05pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics