SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Expression Issue


SSRS Expression Issue

Author
Message
SSRS Newbie
SSRS Newbie
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 609
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,
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43935 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
SSRS Newbie
SSRS Newbie
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 609
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
rxm119528
rxm119528
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 804
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
rxm119528
rxm119528
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 804
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
SSRS Newbie
SSRS Newbie
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 609
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43935 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
rxm119528
rxm119528
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 804
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
SSRS Newbie
SSRS Newbie
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 609
Jack & Rxm,

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

Amol
SSRS Newbie
SSRS Newbie
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 609
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search