May 11, 2015 at 11:05 am
I am using 2013 SSRS extracting data from db2 (iSeries) I am trying to get my reports more automated by having subscription running on invoice date from Monday – Friday and the report will be generated on Saturday morning. The problem is I have to input the dates earlier in the week. I would like to have the subscription run the program every Saturday morning automatically based on the Saturday’s date. The problem I have is that the date field in db2 for May 9, 2015 is: 20150509. How can I get SSRS to recognize the date format generate the report from invoice date Monday – Friday and generate on Saturday morning. I suppose I can format the dates but I don’t know how to do that. All I want is to run a report every Saturday morning. Any help or suggestion are appreciated. Thank you in advance.
May 26, 2015 at 8:02 am
Do you have the option of using Data Driven subscriptions in Reporting Services?
If you do, then you can set up the data driven subscription with start and end date parameters.
If you don't, then you can put the date range in your report data set.
Once you answer this question, then I can give you more detailed instructions.
May 26, 2015 at 1:08 pm
Thanks SSC Journeyman,
Thanks for responding to me.
Based on your questions, see below.
Do you have the option of using Data Driven subscriptions in Reporting Services?
Yes, I have the option to setup subscription if that what you mean. I already input in the parameters on Monday for following Saturday report.
If you do, then you can set up the data driven subscription with start and end date parameters.
I have setup the dates in my parameters in subscription, but the dates go like this: Start: 20150524 End: 20150530
If you don't, then you can put the date range in your report data set.
Not sure what you mean on this.
What I looking for is a more automated process have SSRS subscription run the report on Saturday getting the current week of data Sunday - Saturday every week without me inputting the parameters for start and end date. If I was using MS SQL I have a pretty good idea, but I am using DB2 with date format as yyyymmdd I am clueless. All I want is to have the report fire off on Saturday, which it does and get the current weeks data without me inputting the dates in the parameters. Thank you in advance.
May 26, 2015 at 2:12 pm
My name is Sarah. The SSC Journeyman is a rank that SQL Server Central gives you based on how much you participate on their website. Your rank is grasshopper.
The query in a Data-Driven subscription is a SQL query.
Here is what I would put in the query to get the start and end dates in the correct format:
DECLARE @Begin_Date datetime
,@End_Date datetime
SET @Begin_Date = DateAdd(dd,-7,GETDATE())
SET @End_Date = GETDATE()
select CAST(Year(@Begin_Date) as VARCHAR(4)) + RIGHT('0' + CAST(Month(@Begin_Date) as VARCHAR(2)),2) + CAST(day(@Begin_Date) as VARCHAR(2)) AS START_DATE
,CAST(Year(@End_Date) as VARCHAR(4)) + RIGHT('0' + CAST(Month(@End_Date) as VARCHAR(2)),2) + CAST(day(@End_Date) as VARCHAR(2)) AS END_DATE
May 26, 2015 at 4:42 pm
Thanks Sarah. I really, really appreciate you helping me out I will try this. Thank you again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply