Solved

Date Question in Formulas for SSRS

Posted on 2008-09-29
10
1,315 Views
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?
0
Comment
Question by:Sleestack90
[X]
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
10 Comments
 
LVL 18

Expert Comment

by:mdougan
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
0
 

Author Comment

by:Sleestack90
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"
0
 
LVL 18

Expert Comment

by:mdougan
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?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Sleestack90
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.

0
 
LVL 18

Expert Comment

by:mdougan
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)
0
 

Author Comment

by:Sleestack90
ID: 22599703
mdougan:  
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
statement_date,
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.
0
 
LVL 18

Expert Comment

by:mdougan
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!
0
 

Author Comment

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

Accepted Solution

by:
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!

Cheers!
Mike
0
 

Author Closing Comment

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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 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