Solved

Date Question in Formulas for SSRS

Posted on 2008-09-29
10
1,309 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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