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