Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS Date Parameter Default for Subscribed Report

Respondido SSRS Date Parameter Default for Subscribed Report

  • Saturday, January 19, 2013 5:36 PM
     
     
    Sign In to Vote
    0

    Hi All,

    I have a SSRS Report with two parameters.

    1. StartDate

    2. EndDate.

    Now If user want to subscribe for this Report, I know how to subscribe. But what about Start and End Date parameter. I know there is something called Default, but what i want is as below.

    1. Subscription for Daily. So i subscribe to run it for 8 AM daily. For start and end date it should take previous date.

    Ex. If i am subscribing for 20/01/2013 then Start Date would be '19/01/2013 00:00:00' and EndDate should be '19/01/2013 23:59:59'

    2. Subscription for Weekly. So i subscribe to run it for 8 AM Weekly. For start and end date it should take 7 days previous.

    Ex. if i am subscribing for 20/01/2013 then Start Date would be '13/01/2013 00:00:00' and EndDate should be  '19/01/2013 23:59:59'

    3. Subscription for Monthly. So i subscribe to run it for 8 AM Monthly. For Start and End Date it should be take 30 or 31 days accordingly

    Ex. if i am subscribing for 20/01/2013 then Start Date would be '20/01/2012 00:00:00' and EndDate should be  '19/01/2013 23:59:59'

    Note: This is only Single Report, which i want to schedule for Daily, Weekly and Monthly.

    How can achieve this. I think i need to expression in Default value for what expression should i use. Any help is really appreciated,

    Thank you

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

     

All Replies

  • Tuesday, January 22, 2013 7:05 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Manjunath,

    In Reporting Services, if the date time is 19/01/2013 00:00:00, it display as 19/01/2013 12:00:00 AM.

    So if you want to subscribe for daily, the default values for the parameters would like below.
    StartDate =DateAdd("d",-1,today())
    EndDate   =today()
    Then you can use the parameters in the query like:
    where column>=@StartDate and column<EndDate

    If you want to subscribe for Weekly, the default values for the parameters would like below.
    StartDate =DateAdd("d",-7,today())
    EndDate   =today()

    If you want to subscribe for Monthly, the default values for the parameters would like below.
    StartDate =DateAdd("d",-31,today())
    EndDate   =today()

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    Charlie Liao
    TechNet Community Support

     
  • Tuesday, January 22, 2013 10:28 AM
     
     
    Sign In to Vote
    0

    Hi Charlie,

    These default values, should i need to give it as expression. Does one single report accept more than 1 default values. Because i want one single report and three values, daily, monthly and Weekly. Depending on what user opt for it should pick that one. thank you,

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

     
  • Friday, January 25, 2013 5:46 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Manjunath,

    If you create subscription for the report, so the report will execute automatically, hwo can you pick the option to suscribe daily, monthly or Weekly?

    Regards,
    Charlie Liao

    Charlie Liao
    TechNet Community Support

     
  • Monday, January 28, 2013 7:41 AM
     
     
    Sign In to Vote
    0

    Hi Charlie,

    I was able to achieve this thing using the Reply by Bettik, by creating an extra Parameter.

    Thank you all for your Support.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/f3a9cdcb-73f4-47b2-93ff-7e237dc57f1a

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com