DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

"Operation is not valid due to the current state of the object."

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second

DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

"Operation is not valid due to the current state of the object."

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second

DateAdd

Trying to use the DateAdd feature so I can do some MTD/YTD
comparisons. When I use the Expression builder, I am getting errors.
Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
I've tried year and Year. I've even seen it put into quotes ("yy").
I'm basically just trying to take the current date and subtract 1 year
from it. My ultimate goal is to create a report where I get LYTD/YTD
and MTD compared to the same month a year ago. This would key of a
report parameter since the user may want to look at particular month,
not just current. The report works fine as long as you manually put
in every date. I just need the DateAdd to make some automatic
calculations for me. Thanks in advance.On Dec 5, 3:51 pm, Barry <ba...@.tecelectric.com> wrote:
> Trying to use the DateAdd feature so I can do some MTD/YTD
> comparisons. When I use the Expression builder, I am getting errors.
> Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
> I've tried year and Year. I've even seen it put into quotes ("yy").
> I'm basically just trying to take the current date and subtract 1 year
> from it. My ultimate goal is to create a report where I get LYTD/YTD
> and MTD compared to the same month a year ago. This would key of a
> report parameter since the user may want to look at particular month,
> not just current. The report works fine as long as you manually put
> in every date. I just need the DateAdd to make some automatic
> calculations for me. Thanks in advance.
DateAdd is very finicky, and the documentation is really poor. It's
case sensitive, and only certain combinations seem to work. You will
want to use yyyy for Year, M or m for Month, d for Day, H for hour, n
for Minute, s for Second.
You want to not use the double quotes when you are using the function
in SQL, but do use the quotes when it is in a .Net expression.
1 Year Ago from Date
= DateAdd( "yyyy", -1, Parameters!CurrentYearsDate.Value )
-- Scott|||On Dec 5, 3:30 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 5, 3:51 pm, Barry <ba...@.tecelectric.com> wrote:
> > Trying to use theDateAddfeature so I can do some MTD/YTD
> > comparisons. When I use the Expression builder, I am getting errors.
> > Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
> > I've tried year and Year. I've even seen it put into quotes ("yy").
> > I'm basically just trying to take the current date and subtract 1 year
> > from it. My ultimate goal is to create a report where I get LYTD/YTD
> > and MTD compared to the same month a year ago. This would key of a
> > report parameter since the user may want to look at particular month,
> > not just current. The report works fine as long as you manually put
> > in every date. I just need theDateAddto make some automatic
> > calculations for me. Thanks in advance.
> DateAddis very finicky, and the documentation is really poor. It's
> case sensitive, and only certain combinations seem to work. You will
> want to use yyyy for Year, M or m for Month, d for Day, H for hour, n
> for Minute, s for Second.
> You want to not use the double quotes when you are using the function
> in SQL, but do use the quotes when it is in a .Net expression.
> 1 Year Ago from Date
> =DateAdd( "yyyy", -1, Parameters!CurrentYearsDate.Value )
> -- Scott
PERFECT!!! I have spent many numerous hours searching for the correct
syntax. Worked great! Thanks so much.