locked
Daily, Weekly, Monthly and Yearly parameter for scheduled SSRS report RRS feed

  • Question

  • Currently, I have a report that takes two parameters:  StartDate and EndDate.  I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well witih StartDate and EndDate because the parameter is static.  What is the most elegant way to implement this change?
    intercrew
    Friday, January 7, 2011 6:11 PM

Answers

  • Hi,

    The startdate and enddate expression need to be set in the report (rdl file). Since you require the startdate and enddate to be dynamic based on the interval type, I suggest you add a parameter to your report named DateIntervalType and add available values D, W, M, Y that stands for daily, weekly, monthly and yearly respectively.

    In the fromdate and todate expression, set the values based on the DateIntervalType. A sample expression for start is shown below

    =Switch(Parameters!DateIntervalType.Value="D",dateadd(DateInterval.Day,-1,today()),Parameters!DateIntervalType.Value="W",dateadd(DateInterval.Day,-7,today()),Parameters!dateIntervalType.Value="M",
    dateadd(DateInterval.Month,-1,today()),Parameters!DateIntervalType.Value="Y",dateadd(DateInterval.Year,-1,today()))
    

    I guess the todate would remain always to the current date. If not use a logic similar to above to fit your needs.

    While setting up schedules in subscriptions set the paramter DateIntervalType that relates to the schedule. Ex : Your daily schedule sould set parameter to value "D", weekly schedule to "W" etc.

    Hope this helps.

     


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    • Proposed as answer by Tony Chain Monday, January 10, 2011 8:43 AM
    • Marked as answer by Tony Chain Sunday, January 16, 2011 2:54 PM
    Saturday, January 8, 2011 9:26 AM

All replies

  • in the subscription.. use the report expression for the date parameters.  For example.. your "daily" subscription (assuming it would be yesterday's info), have the dates set accordingly.. startdate = dateadd("d", -1, today())  and if you needed a 24 hour format, then use:  startdate = =CDate(DateValue(dateadd("d",-1, Globals!ExecutionTime))  +  " 00:00:01") and the enddate = same thing but make the time 23:59:59.  This will ensure you get the whole day.

    For a weekly, just use the expression to set it for Sun through Sat and use the times again.  Looks like you'll have at least 4 subscriptions to set up.  Let me know if you would like the expressions for the dates.

    Friday, January 7, 2011 6:27 PM
  • Hi pmp07,

     

    Thanks for the quick response.  I'm a newbie to SSRS.  How do I set the startdate =  dateadd("d",-1,today())?  Am I setting this in the Report Manager?  Am I setting this in the parameter in the RDL file?


    intercrew
    Friday, January 7, 2011 7:41 PM
  • Hi,

    The startdate and enddate expression need to be set in the report (rdl file). Since you require the startdate and enddate to be dynamic based on the interval type, I suggest you add a parameter to your report named DateIntervalType and add available values D, W, M, Y that stands for daily, weekly, monthly and yearly respectively.

    In the fromdate and todate expression, set the values based on the DateIntervalType. A sample expression for start is shown below

    =Switch(Parameters!DateIntervalType.Value="D",dateadd(DateInterval.Day,-1,today()),Parameters!DateIntervalType.Value="W",dateadd(DateInterval.Day,-7,today()),Parameters!dateIntervalType.Value="M",
    dateadd(DateInterval.Month,-1,today()),Parameters!DateIntervalType.Value="Y",dateadd(DateInterval.Year,-1,today()))
    

    I guess the todate would remain always to the current date. If not use a logic similar to above to fit your needs.

    While setting up schedules in subscriptions set the paramter DateIntervalType that relates to the schedule. Ex : Your daily schedule sould set parameter to value "D", weekly schedule to "W" etc.

    Hope this helps.

     


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    • Proposed as answer by Tony Chain Monday, January 10, 2011 8:43 AM
    • Marked as answer by Tony Chain Sunday, January 16, 2011 2:54 PM
    Saturday, January 8, 2011 9:26 AM
  • This Works perfectly, There is a minor typo where some one might get issue with the Startdate parameter expression: Please change the case of dateIntervalType to DateIntervalType and this should fix it. 

    ""Parameters!dateIntervalType.Value="M" "" to Parameters!DateIntervalType.Value="M"

    Monday, November 9, 2015 12:18 PM
  • Hi,

    I didn't know whether to ask a brand new question or to carry on this one because its so recent. Apologies for hijacking this thread.

    I have the same issue as this one, however, when the first parameter is selected is doesn't work correctly the second parameter then displays a drop down with <Select a value> or the date value. I've played around and just can't seem to get the correct set up to auto populate the date.

    If I add them a 'Available values' they refresh but the problem is above. If I give them as Default Values then they don't refresh.

    Can anyone advise if I am missing something here? I am also implementing this so that I can get a subscription working properly.

    Regards,

    Dave.

    Friday, November 20, 2015 2:25 PM
  • What about if you want to display weekly report SSRS expression only from a date range. So that you can select any month and be able to view week1, week2, week3, week4. data for specific month.

    For example: Date range January 5th 2008 to July 7th 2009 

    January
    week1 week2 week3 week4
    55 23 4 58
    8 44 55 5
    41 65 4 58
    22 86 5 8

    Thursday, February 18, 2016 3:51 PM