June 5, 2012 at 12:21 pm
I have an existing report in SSRS which has two input parameters (Begin Date, End Date). Now I am trying to create Daily and Monthly subscriptions for this report.
Daily subscription runs at mid night and Montly runs on 1st day of every month.
To pass dynamic variable values based on subscription type I followed the approach mentioned in the link below,
Existing Parameters:
Begin_Date (Date/Time)
End_Date (Date/Time)
New Parameter:
DateIntervalType (Text)
In the available values window I added two values
Label: "Daily" Value: "D"
Label: "Monthly" Value: "M"
I went back to Begin_Date properties --> Default Value window I added a expression (see below)
=Switch(
Parameters!DateIntervalType.Value="D",ToDay(),
Parameters!DateIntervalType.Value="M",ToDay().AddDays(-30)
)
When I run the report I am getting an error "An error occurred during local report processing. begin_date"
To test I replaced above expression with below one and it worked fine..
=Switch(
"D"="D",ToDay(),
"D"="M",ToDay().AddDays(-30)
)
So, the problem seems to be with Parameters!DateIntervalType.Value.. Can anyone help me to fix this? or else is there a different approach?
Thanks
June 7, 2012 at 7:05 am
Have you set a default value for your D/M parameter?
If you aren't sending it via the subscription yet you may be trying to send a NULL which isn't handled in your switch statement whereas with your changed BeginDate switch with D=D the first condition is always true so you won't get the same error.
June 7, 2012 at 3:59 pm
One report 2 data driven subscriptions
Daily subscription query
SELECT DATEADD(dd,DATEDIFF(dd,0,GetDate()),-1) AS StartDate
,DATEADD(dd,DATEDIFF(dd,0,GetDate()),-1) AS EndDate
Monthly subscription query
SELECT DATEADD(mm,-1,DATEDIFF(dd,0,GetDate())) AS StartDate
,DATEADD(dd,DATEDIFF(dd,0,GetDate()),-1) AS EndDate
Later in the subscription wizard you associate the StartDate value to your parameter input Begin_Date, and your EndDate value to your parameter input End_Date.
If your data has time components other than 00:00:00 you will have to tweak the EndDate calculations
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply