Date Question in Formulas for SSRS

Posted on 2008-09-29
Last Modified: 2012-05-05
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?
Question by:Sleestack90
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 18

Expert Comment

ID: 22597894
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

Author Comment

ID: 22598031
'Month' did not work in the formula in SSRS.  

"Argument not specified for parameter "DateValue" of publicfunction Month(DateValue as Date) as interger"
LVL 18

Expert Comment

ID: 22598998
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?
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

ID: 22599301
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.

LVL 18

Expert Comment

ID: 22599498
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)

Author Comment

ID: 22599703
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.
LVL 18

Expert Comment

ID: 22599920
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!

Author Comment

ID: 22599967
This was my first question that someone actually responded to.  What happens if I just accept?
LVL 18

Accepted Solution

mdougan earned 500 total points
ID: 22601790
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!


Author Closing Comment

ID: 31501414
Thanks again for your help.  This is actually my 2nd question in 3 years.

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Consolidate rows 3 25
Freeze portion of datamart 2 21
Star schema daily updates 2 34
Change this SQL to get all nodes 3 37
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question