Post reply

using subscriptions for Date Parameter Reports

  • November 1, 2012 at 6:07 am

    #284134

    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?

  • November 1, 2012 at 10:07 am

    #1555096

    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.

  • November 1, 2012 at 11:50 am

    #1555126

    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?

  • November 1, 2012 at 11:53 am

    #1555128

    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[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 1, 2012 at 12:04 pm

    #1555131

    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

  • November 1, 2012 at 1:02 pm

    #1555151

    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))

  • November 2, 2012 at 6:48 am

    #1555440

    Enterprise Editon?

    Data Driven Subscriptions come to mind, especially with Quarterly and Yearly you mentioned.

    Each subscription could pass the different dates easily.

  • November 2, 2012 at 7:28 am

    #1555463

    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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply