Date Question in Formulas for SSRS

I am trying to create a formula in SSRS that will return a value by month in a tabular report.  I have a parameter date, which is equal to the last date of my dataset.  I created the formula for that date as follows:

=iif(Fields!statement_date.Value = Parameters!Date.Value,Fields!revenue,0)

I am trying to write a formula for a field in the next column that would return the data from the prior month (all records are as of the last day of the month).  I have tried the following:

=iif(Fields!statement_date.Value = DATEADD(m, 0, Parameters!Date.Value - DAY(Parameters!Date.Value) + 1) - 1) , Fields!revenue.Value,0)

My DATEADD function works in SQL if added to a WHERE clause (returns the last day of the prior month), but when I put it into a Reporting Services field, I get an error message that I did not declare m.  What syntax would I use to make this formula work?
Who is Participating?
mdouganConnect With a Mentor Commented:
Well, I'll get the points, and that boosts my standing.  The points are fun, but I don't really care.  I just like to help out if I can.  If you need the points, just keep them.  For me, though, I always accept somebody's answer, just to promote good will in the future.

At the time you accept (if you do) I think you have the option to add it to the knowledgebase (I don't know if that is your personal knowledgebase or everyone's).  If you think your solution would be valueable to others, I'd choose that option while accepting.

I'm surprised you haven't gotten any responses previously.  Perhaps you've asked really difficult questions!

You could try using the word   month  instead of   m    Under SQL Server, it should take either one, but it is possible that Reporting Services will only take   month
Sleestack90Author Commented:
'Month' did not work in the formula in SSRS.  

"Argument not specified for parameter "DateValue" of publicfunction Month(DateValue as Date) as interger"
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

OK, forget that idea, you could try DateInterval.Month instead.  But, I'm looking at your DATEADD function and it doesn't make sense to me...

=iif(Fields!statement_date.Value =
DATEADD(m, 0, Parameters!Date.Value - DAY(Parameters!Date.Value) + 1)
 - 1) , Fields!revenue.Value,0)

Here is the syntax for the DateAdd statement
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)

You've got an interval of zero, so, why would you be doing the DateAdd in the first place?
Then, you're trying to subtract DAY from the parameter date value, but to subtract you should really be doing another DateAdd but with a -Day value.  Can you explain exactly what you are trying to compare statement_date.value to, giving examples of real dates?
Sleestack90Author Commented:
I tried the above formula, and received a similar error message.  It doesn't like Month

An interval of 0 within that formula essentially returns the first date of the parameter month Ex if Parameter = 6/30/2008, then
DateAdd(m, 0, Parameters!Date.Value - Day(Parameters!Date.Value) + 1 = 6/1/2008.  The (- 1) at the very end returns 5/31/2008.  If I just substituted (month, -1), then I would get a return value of 5/30/2008.

Well, you said that "all records are of the last day of the month", so, the way that I'd approach it is to add 1 day to the date, then subtract 1 month.  

I have heard of shortcuts, such as you're proposing, but I'm still don't think the way you are subtracting the DAY value from the DATE is valid.

My thought would be:
=iif(Fields!statement_date.Value =
(DateAdd(DateInterval.Month, -1, (DateAdd(DateInterval.Day, 1, Parameters!Date.Value))), Fields!StartDate.Value, 0)
Sleestack90Author Commented:
I was not able to get your above formula to work, but I was able to solve the problem by creating the select statement below, and adding it to my existing dataset:

select distinct
DATEADD(m, 0, statement_date - DAY(statement_date) + 1) - 1 AS Date1,
DATEADD(m, - 1, statement_date - DAY(statement_date) + 1) - 1 AS Date2,
DATEADD(m, - 2, statement_date - DAY(statement_date) + 1) - 1 AS Date3

I was then able to reference these calculated dates in report formulas.

I'm sure this is not the best way to get there, but it appears to be good enough for now.  I appreciate your help.
No problem.  I usually find it easier to generate the fields in the SQL too!

You can cancel the question if you like and ask for the points to be refunded.  Good Luck!
Sleestack90Author Commented:
This was my first question that someone actually responded to.  What happens if I just accept?
Sleestack90Author Commented:
Thanks again for your help.  This is actually my 2nd question in 3 years.
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.