August 25, 2014 at 9:07 am
Hello All,
I'm having an issue with adding parameters to the report with the below statement. When I add @Start as a parameter in report and set the default value to =DATEADD( "M", -1, DATEADD("QQ", DATEDIFF("QQ", 0, Today()),0)), I get an error saying - An error occured during local report processing. How can I set default @Start date to the below value? I also have to set the default value for @End to - DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))? Thanks
declare @Start as date
declare @End as date
declare @NextStart as date
Declare @NextEnd as date
Set @Start= DATEADD( MM, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()),0))
Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))
Set @NextStart = DATEADD(MONTH, 1, @Start)
Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))
Thanks,
August 25, 2014 at 11:39 am
There are a few issues with your attempt at an expression in SSRS.
1. 0 can't be converted to a date by .NET (the basis for the expression language) so you need to replace it with "1900-01-01" which is what 0 translates to in SQL Server for DATETIME.
2. "QQ" is not a valid Date Interval in .NET. The short hand for quarter in .NET is "Q", but I would recommend not using the short hand in either T-SQL or .NET. In .NET you would use DateInterval.Quarter. There is no ambiguity when you spell things out. In this case it has to be Quarter, but when you get to the M's it could be confusing (Month, Minute, Millisecond, etc...
So for your first expression I think you want this:
=DateAdd(DateInterval.Month, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateValue("1900-01-01"), Today()),DateValue("1900-01-01")))
I'm going to assume you can work out the other expressions you need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2014 at 12:07 pm
Hi Jack,
I appreciate your response. I've a quick question for you. My report is the quarterly report so, I would have user enter only the @Start Date. Rest of the three (@end,@NextStart, @NextEnd) parameters will be hidden from user.
Below three parameters will be dependent on the @Start. How can I achieve below statments in SSRS expression? Can you give me a hand in this? Thanks, again
Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))
Set @NextStart = DATEADD(MONTH, 1, @Start)
Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))
Regards,
Amol
August 25, 2014 at 12:13 pm
amolacp (8/25/2014)
Hello All,I'm having an issue with adding parameters to the report with the below statement. When I add @Start as a parameter in report and set the default value to =DATEADD( "M", -1, DATEADD("QQ", DATEDIFF("QQ", 0, Today()),0)), I get an error saying - An error occured during local report processing. How can I set default @Start date to the below value? I also have to set the default value for @End to - DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))? Thanks
declare @Start as date
declare @End as date
declare @NextStart as date
Declare @NextEnd as date
Set @Start= DATEADD( MM, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()),0))
Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))
Set @NextStart = DATEADD(MONTH, 1, @Start)
Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))
Thanks,
Below is the SSRS equivalent expression for start.
DATEADD(DateInterval.Month,-1,DATEADD(DateInterval.Quarter,DATEDIFF(DateInterval.Quarter,CDate("1900-01-01"),CDate("2014-08-25")),Cdate("1900-01-01")))
This how it works
QQ = DateInterval.Quarter
When you are passing 0 in the DATEDIFF it is eqivalent to 1900-01-01
IN SSM try the below code
declare @d1 datetime = 0
select @d1
I hope you can make it other parameters
August 25, 2014 at 12:15 pm
Jack Corbett (8/25/2014)
There are a few issues with your attempt at an expression in SSRS.1. 0 can't be converted to a date by .NET (the basis for the expression language) so you need to replace it with "1900-01-01" which is what 0 translates to in SQL Server for DATETIME.
2. "QQ" is not a valid Date Interval in .NET. The short hand for quarter in .NET is "Q", but I would recommend not using the short hand in either T-SQL or .NET. In .NET you would use DateInterval.Quarter. There is no ambiguity when you spell things out. In this case it has to be Quarter, but when you get to the M's it could be confusing (Month, Minute, Millisecond, etc...
So for your first expression I think you want this:
=DateAdd(DateInterval.Month, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, DateValue("1900-01-01"), Today()),DateValue("1900-01-01")))I'm going to assume you can work out the other expressions you need.
Jack,
I didn't realize that you alredy replied for this.
thanks
rm
August 25, 2014 at 1:01 pm
Jack & Rxm,
My other there parameters @Start, @End, @NextStart, @NextEnd are dependent on @StartDate (2014-06-01). How can I set the other three parameters (@End, @NextStart, @NextEnd) in SSRS expression as same way as T-SQL?
set @Start = DATEADD( MM, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()),0))
Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))
Set @NextStart = DATEADD(MONTH, 1, @Start)
Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))
Here is the T-Sql code:
declare @Start as date
declare @End as date
declare @NextStart as date
Declare @NextEnd as date
set @Start = DATEADD( MM, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()),0))
Set @End = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +1, 0))
Set @NextStart = DATEADD(MONTH, 1, @Start)
Set @NextEnd = DATEADD(day, -1, DATEADD(month, DateDiff(month, 0, @Start) +2, 0))
Select J.Job, cast(ce.sStartDate as Date) 'PrintDate', Cast(j.lastshippeddate as date) 'ShipDate', Cast(i.invdate as date) 'InvoiceDate', i.InvNumber, I.CompanyName 'Customer', I.TotalInvAmount'Total Inv Amount'
from JobExtra J
Join JobMaster m (nolock) on J.JobNumber=m.JobNumber
Join InvoiceHeader I (nolock) on I.JobNumber = J.JobNumber
Join (select MIN (CE.RecID) 'Record', ce.sJobNumber 'Job' from JobExtra J
left join CostEntry CE (nolock) on CE.sJobNumber=J.JobNumber
left join JobExtra JE (nolock) on J.JobNumber=JE.JobNumber
left join [InvoiceHeader] I (nolock) on I.JobNumber = JE.JobNumber
where (CE.sDepartmentCode='AG')
and (cast (CE.sStartDate as date) between @Start and @End)
and (cast (J.LastShippedDate as date) between @NextStart and @NextEnd)
group by ce.sJobNumber
) t on t.Job = i.JobNumber
Join CostEntry CE (nolock) on CE.sJobNumber=t.Job and ce.RecID = t.record
where CAST (I.InvDate as DATE) not between @Start and @End
Thanks
August 25, 2014 at 1:48 pm
Just replace TODAY() with Parameters!StartDate.Value in each of the other calculations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 25, 2014 at 1:48 pm
I am not sure under what kind of pressure you are working but normally after telling how to do it for one parameter,
you should have done it for others. Atleast you should have given a try.
Any way below are the expressions.
ReportParameter1 should be replaced with the name of the Parameter that you are going to define for @start.
@Start=DATEADD(DateInterval.Month,-1,DATEADD(DateInterval.Quarter,DATEDIFF(DateInterval.Quarter,CDate("1900-01-01"),CDate("2014-08-25")),Cdate("1900-01-01")))
@End=DATEADD(DateInterval.Day,-1, DATEADD(DateInterval.Month, DateDiff(DateInterval.Month, DateValue("1900-01-01"),(Parameters!ReportParameter1.Value)) +1, DateValue("1900-01-01")))
@NextStart=DATEADD(DateInterval.Month, 1, (Parameters!ReportParameter1.Value))
@NextEnd=DATEADD(DateInterval.Day, -1, DATEADD(DateInterval.Month, DateDiff(DateInterval.Month,DateValue("1900-01-01"), (Parameters!ReportParameter1.Value)) +2, DateValue("1900-01-01")))"
Thanks
rm
August 25, 2014 at 2:14 pm
Jack & Rxm,
Thank you for your response. I'm new to SSRS and trying to get used to it. Sorry about that.
Amol
August 25, 2014 at 2:22 pm
One last question to you guys. The report that, I'm working on is the quarterly report. When I change the @start date to (10/01/2014) on report, other three (@End, @NextStart, @NextEnd) parameters still show the same date.
When @start date is changed to (10/01/2014), as per the SSRS expression:
@End should change to 10/31/2014
@NextStart should change to 11/01/2014
@NextEnd should change to 11/30/2014
Thank you for your patience Rxm & Jack.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply