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


Last Day of Month subscription for Shared SSRS schedule


Last Day of Month subscription for Shared SSRS schedule

Author
Message
nathon
nathon
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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!



drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15247 Visits: 11199
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
rjordan
rjordan
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
dso808
dso808
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211487 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dso808
dso808
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211487 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SqlServerLover
SqlServerLover
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 417
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211487 Visits: 41977
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. :-D Heh... what good are computers if they require human interaction for things like previous month reports? Hehe

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SqlServerLover
SqlServerLover
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 417
I did not mean to run manually.
Set up shared schedule to run at 12 am on the 1st of the month.
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