Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using subscriptions for Date Parameter Reports


using subscriptions for Date Parameter Reports

Author
Message
jagnew
jagnew
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
I have a report created that has two datetime parameters.

I want to set up a monthly subscription for the report filling in the first and last days of the previous month as the two datetime parameters.

So each month when the subscription runs, it should generate reporting information based on the previous month.

I don't want to hardcode this because I want to use the same report to do quarterly and yearly subscriptions.

Does anyone know a method to do this?
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
There are a few ways to handle this.

You can leave the report with StartDate and EndDate Parameters.
Edit the report and give the Parameters Default Values.
StartDate will be =Datetime.Today.AddDays(-Day(Datetime.Today.AddDays(-1)))
Will be the first of the Current Month, But if it is the first, it will be first of Prev Month.
and EndDate will be Datetime.Today. And Make sure the report is using these days correctly.
and when you schedule the subscription, just have it use the Default value.

Or you can add a drop down with values like "This Month","Last Month", "This Year". And your code can interpret these values to generate the dates.
jagnew
jagnew
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
these parameter defaults sounds like the way I want to go.

what you've shown me gets me the first of the previous month (assuming the report is generated on the first day of the month). But how do I get the last day of the previous month?
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 4101
Add 1 month to the start date, and subtract 1 day. :-)

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
jagnew
jagnew
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
hmmm... I must not be doing something right. I get this error when I set the default parameters

value provided for the report parameter 'DateStart' is not valid for its type. (rsReportParameterTypeMismatch) Get Online Help
Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076
For start date to always get Previous Month Use
=Datetime.Today.AddDays(-Day(Datetime.Today)+1).AddMonths(-1)
For End Date to get Last Date of Previous Month
=Datetime.Today.AddDays(-Day(Datetime.Today))
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8254
Enterprise Editon?
Data Driven Subscriptions come to mind, especially with Quarterly and Yearly you mentioned.
Each subscription could pass the different dates easily.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2952 Visits: 2629
If you have the enterprise edition a data driven subscription is absolutely the way to go. Assuming your report would run on the first of every month you could use:

SELECT DATEADD('mm',-1,GetDate()) AS StartDate, DATEADD('dd'-1,GetDate()) AS EndDate.

If you don't have the enterprise edition, then you are probably stuck with the default values for your parameters approach
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