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

using subscriptions for Date Parameter Reports Expand / Collapse
Author
Message
Posted Thursday, November 1, 2012 6:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:29 AM
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?
Post #1379766
Posted Thursday, November 1, 2012 10:07 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
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.
Post #1379915
Posted Thursday, November 1, 2012 11:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:29 AM
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?
Post #1379953
Posted Thursday, November 1, 2012 11:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 2,705, Visits: 3,768
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
Post #1379955
Posted Thursday, November 1, 2012 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:29 AM
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


Post #1379958
Posted Thursday, November 1, 2012 1:02 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
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))
Post #1379986
Posted Friday, November 2, 2012 6:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 1,219, Visits: 6,558
Enterprise Editon?
Data Driven Subscriptions come to mind, especially with Quarterly and Yearly you mentioned.
Each subscription could pass the different dates easily.
Post #1380333
Posted Friday, November 2, 2012 7:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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
Post #1380357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse