Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Expression Issue Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 9:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:48 AM
Points: 30, Visits: 187
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,
Post #1607108
Posted Monday, August 25, 2014 11:39 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1607154
Posted Monday, August 25, 2014 12:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:48 AM
Points: 30, Visits: 187
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
Post #1607165
Posted Monday, August 25, 2014 12:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:19 AM
Points: 321, Visits: 681
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

Post #1607168
Posted Monday, August 25, 2014 12:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:19 AM
Points: 321, Visits: 681
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
Post #1607170
Posted Monday, August 25, 2014 1:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:48 AM
Points: 30, Visits: 187
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
Post #1607200
Posted Monday, August 25, 2014 1:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
Just replace TODAY() with Parameters!StartDate.Value in each of the other calculations.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1607225
Posted Monday, August 25, 2014 1:48 PM This worked for the OP Answer marked as solution
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:19 AM
Points: 321, Visits: 681
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
Post #1607226
Posted Monday, August 25, 2014 2:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:48 AM
Points: 30, Visits: 187
Jack & Rxm,

Thank you for your response. I'm new to SSRS and trying to get used to it. Sorry about that.

Amol
Post #1607236
Posted Monday, August 25, 2014 2:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:48 AM
Points: 30, Visits: 187
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.
Post #1607240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse