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 ««123»»

Last Day of Month subscription for Shared SSRS schedule Expand / Collapse
Author
Message
Posted Friday, August 28, 2009 3:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 1:00 PM
Points: 1, Visits: 27
I just ran into this same problem, which is why I arrived at this forum. Man, for such an experienced software development company making such a MASSIVE oversight seems incredible. How could you create a reporting product that can't do something as critical as create month-end reports? And still enable it to create something as worthless as month-beginning reports?!?!?

Who in the world wants to know how much money they've made for the month on the first day of the month? Wow MS... what a HUGE oops!



Post #779327
Posted Friday, August 28, 2009 3:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
If you have a business that is 24/7, such as a website, no matter what time you run your reports at the end of the month, you risk missing some transactions. However, if you run your reports on the first day of the month and report on last month's figures, you are certain to get all of the transactions for last month.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #779331
Posted Friday, February 5, 2010 12:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 23, 2010 1:19 AM
Points: 1, Visits: 7
I have found a workaround that works for me.

Follow these steps to create the report for the entire last month:

1. Go to The Reports http:\\<yourserver>\reports

2. Click on show details on the right

3. Choose one Report you want to schedule and click on the Edit button

4. Click on the Edit Button in the Report definition and save the Report on your drive.

5. Open The Report in the notepad

6. In the first Default Value Section type: DateTime.Today.AddMonths(-1)

7. In the second Default Section type: DateTime.Today.AddDays(-1)

Example:

<DefaultValue>
<Values>
<Value>=DateTime.Today.AddMonths(-1)</Value>
</Values>
</DefaultValue>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<Nullable>true</Nullable>
<DefaultValue>
<Values>
<Value>=DateTime.Today.AddDays(-1)</Value>
</Values>
</DefaultValue>
<Prompt>End Date</Prompt>

8. Save it and go back to the Report tab in your browser click on upload file

9. Click on your Report --> Subscription --> New Subscription

10. Execute your Schedule monthly on calendar day(s): 1

11. In the Start date and End date Values click on both Use Default

Best Regards

RJordan
Post #860200
Posted Wednesday, August 18, 2010 7:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:04 PM
Points: 64, Visits: 164
Hi everyone - just wanted to pitch in. Trying to also set up my subscription to send out at the very last day of the month. I've got a pretty easy method I think but it does have a drawback which is pretty minor (depending on your users I guess).

OK, I have a monthly report that uses 2 parameters - month and year. I have the report parameters default to the current month and year. But this thing has to go out at the very last day of the month so as a workaround, I set the subscription to the very 1st of the month and then default my parameters like so:

MONTH parameter default value = IIF(Month(Now)=1,12,Month(Now)-1)
YEAR parameter default value = IIF(Month(Now)=1,Year(Now)-1,Year(Now))

Have to use the IIFs because of the issue of January - when January, now gotta default to December of the previous year, but otherwise, default to the previous month of the same year.

The downside? When users open the report, it'll default to the previous month. So it may confuse some users but I think it's a fairly minor nuisance. Hope this helps everyone.

Post #971566
Posted Friday, August 20, 2010 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 PM
Points: 36,952, Visits: 31,460
dso808 (8/18/2010)
Hi everyone - just wanted to pitch in. Trying to also set up my subscription to send out at the very last day of the month. I've got a pretty easy method I think but it does have a drawback which is pretty minor (depending on your users I guess).

OK, I have a monthly report that uses 2 parameters - month and year. I have the report parameters default to the current month and year. But this thing has to go out at the very last day of the month so as a workaround, I set the subscription to the very 1st of the month and then default my parameters like so:

MONTH parameter default value = IIF(Month(Now)=1,12,Month(Now)-1)
YEAR parameter default value = IIF(Month(Now)=1,Year(Now)-1,Year(Now))

Have to use the IIFs because of the issue of January - when January, now gotta default to December of the previous year, but otherwise, default to the previous month of the same year.

The downside? When users open the report, it'll default to the previous month. So it may confuse some users but I think it's a fairly minor nuisance. Hope this helps everyone.



Ummm.... why can't the call for the automatic first of the month report provide the date whereas the users provide their own date? I wouldn't build that particular nuance into the code directly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #972615
Posted Friday, August 20, 2010 11:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 22, 2011 2:04 PM
Points: 64, Visits: 164
Thanks for the reply Jeff. The script or code I used is for the parameter's default values. I had to use default values for my report so that I could use the subscription service for it. The user is free to change the parameters once they open the report and the report will reopen with whatever month/year data they're looking for.

That's the drawback I mentioned. When the user opens the report, it'll initially show last month's data - so they'll need to change the parameters and reopen the report to get the current month/year or whatever they're looking for.

So I set up the subscription for 1st of every month. The report will grab last month's data because the month parameter defaults to the previous month - which is why I wrote those IIF statements. For the month, it would've been simple to just write "Month(Now) - 1" for the default value. But then there's a problem with January of the new year.

I hope that clarified my post. I'd much rather be able to send this report at the end of the month like everyone else here, but I needed to make this work ASAP and this works fine for our report minus again the little drawback.
Post #972727
Posted Saturday, August 21, 2010 10:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 PM
Points: 36,952, Visits: 31,460
That's what I'm talking about... You have the Users and the Subscription service (whatever that is for you) calling the same proc and you're running into trouble.

The "final" proc should accept date parameters without any defaults no matter what. When a User calls the proc, they call it directly with the required date parameters.

The Subscription service should NOT call the same proc directly. Instead, the Subscription service should call a similarly named proc (I use procname_SS where SS stands for "Subscription Service"). That proc contains the necessary datemath to make two correct date parameters for the preceeding month and then it calls the "final" proc with the correct date parameters. It makes life REAL easy.

So far as the calculation problem you're having with January, why aren't you using the DATEADD function to do your work for you? It KNOWS how to handle dates correctly so you don't need to worry about it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #972965
Posted Sunday, August 22, 2010 5:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:03 PM
Points: 64, Visits: 312
Can't you just run on the 1st day of the month at 12 am?

This is how i have done as a solution of this problem .
In my company, there won't be any transactions after 10 or 11 pm, so instead of running them on the last day of the month at 11 pm; i run them on the 1st day of the month.

Post #973131
Posted Sunday, August 22, 2010 7:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:42 PM
Points: 36,952, Visits: 31,460
SqlServerLover (8/22/2010)
Can't you just run on the 1st day of the month at 12 am?

This is how i have done as a solution of this problem .
In my company, there won't be any transactions after 10 or 11 pm, so instead of running them on the last day of the month at 11 pm; i run them on the 1st day of the month.



Most of the companies I worked for had a 24/7 policy... and transactions between 10 and 11 pm.

The real key here is that you need to be safe. End of previous month runs should run correctly and without human intervention any time on any day for the whole month following.... IMHO that is. Heh... what good are computers if they require human interaction for things like previous month reports?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #973143
Posted Sunday, August 22, 2010 7:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:03 PM
Points: 64, Visits: 312
I did not mean to run manually.
Set up shared schedule to run at 12 am on the 1st of the month.
Post #973146
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse