Solved

Date Question in Formulas for SSRS

Posted on 2008-09-29
10
1,306 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
  • 5
  • 5
10 Comments
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
Comment Utility
'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
Comment Utility
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
 

Author Comment

by:Sleestack90
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Sleestack90
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again for your help.  This is actually my 2nd question in 3 years.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
How to increase the row limit in Jasper Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now