February 3, 2009 at 3:02 am
We have a report which we want to set up a subscription to output a excel file on the first of each month to show the previous months data. The default value of the report is yesterday so clearly I cant use defaults when setting the parameter values. I also cant hard code the dates into the subscription since they will change every month.
The only solution I can come up with is to deploy a new version of the reports where the default date values are the previous month and build the subscription using the default values. Not an ideal solution is there an alternative? The parameter values when setting up a subscription do not take expressions, or at least I couldnt get it to.
February 3, 2009 at 3:20 pm
Great question...I'm looking for the answer too....I also create two reports to get around this...
February 4, 2009 at 2:51 am
Your idea with the default dates seems perfectly acceptable to me but if you don't want to do it like that you could set up a data driven subscription.
You would need to write a query that will contains the information to used in the subscriptions i.e. email recipients etc and report parameters. In the query you could set the date you want to use in the report to yesterday.
February 4, 2009 at 12:39 pm
Can't you use the dateadd feature in the base report date parameters
On the first of the month when the report is run
the following default will minus 1 month
dateadd("m", -1,now())
and use the following for the end of the month
dateadd("d", -1,now())
February 4, 2009 at 1:22 pm
both of these posts are possible, but both require creating a second report. What would like is the ability to only create one report but have different parameters based on whether we run it automatically as a monthly report and also run it manually as a daily report.
One thought...what if we built a parameter expression based on the time of day the report is running?
IIF running at 11:59 pm then use monthly parameters
else use daily parameters....
That might work....thoughts? I'll try to scratch out some time to try it...
February 4, 2009 at 2:02 pm
This works....this code if pasted into your parameter expression will change the begdate parameter based on what time of day the report is being ran. This particular one, if ran at 2pm (14) will have a begin parameter of the first day of this month....Else the begin parameter is the beginning of the day today.....
=iif(hour(Globals!ExecutionTime) = 14,datevalue(dateadd("d",1+(datepart("d",now()) * -1),now())),datetime.today)
Anyone have any better ideas?
February 4, 2009 at 3:22 pm
You don't need 2 reports you can have the parameter default values set for manual execution and then have the monthly subscription use different values which would be returned by the data driven query.
February 4, 2009 at 3:26 pm
As far as we've seen, the subscription parameters have to be hard coded. Expressions don't work there....
February 4, 2009 at 4:04 pm
Bob (2/4/2009)
This works....this code if pasted into your parameter expression will change the begdate parameter based on what time of day the report is being ran. This particular one, if ran at 2pm (14) will have a begin parameter of the first day of this month....Else the begin parameter is the beginning of the day today.....=iif(hour(Globals!ExecutionTime) = 14,datevalue(dateadd("d",1+(datepart("d",now()) * -1),now())),datetime.today)
Anyone have any better ideas?
Sounds like a good idea, im gonna try it tomorrow!
February 5, 2009 at 1:30 am
Bob (2/4/2009)
As far as we've seen, the subscription parameters have to be hard coded. Expressions don't work there....
That is only regular subscriptions. With data driven subscriptions you can base the parameter values on the fields in a T-SQL data set and therefore make them dynamic.
February 5, 2009 at 1:49 am
benlatham (2/5/2009)
Bob (2/4/2009)
As far as we've seen, the subscription parameters have to be hard coded. Expressions don't work there....That is only regular subscriptions. With data driven subscriptions you can base the parameter values on the fields in a T-SQL data set and therefore make them dynamic.
Looked into that but it seems data driven subscriptions are only available on enterprise edition.
February 5, 2009 at 7:38 am
More info on Data Driven Subscriptions....
February 5, 2009 at 9:52 am
I have just implimented the following into a month-end report used by the business. I have a subscription setup to run @ 7am on the first of every month.
The date range is as follows:
DATE BETWEEN DATEADD(MONTH,-1,GETDATE()) AND GETDATE()
February 6, 2009 at 6:30 am
We've run into this with my users and what we've come up with is a parameter that allows the user to pick specificic date ranges that are dynamic, such as LastWeek, LastMonth, CurrentMonth, Today, Yesterday, etc. When they setup their subscription, they have the flexibility to choose whatever range they want but still allow it to be dynamic. The stored procedure then calculates the actaul To and From dates to use based on this parameter.
No need for multiple versions of the same report and the calculation for the To and From can be placed in a fucntion to allow all reports to use this functionality.
February 6, 2009 at 2:44 pm
Very nice.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply