Date Question in Formulas for SSRS
Posted on 2008-09-29
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?