locked
SSRS Monthly Weekly subscription RRS feed

  • Question

  • Hi,

    I have a report in SSRS and wish to subscribe it monthly (for previous months' data)and fortnightly (for previous 2 weeks' data). How can i set this up?

    Many thanks

    Monday, March 6, 2017 5:41 AM

Answers

  • Hi Zimiso,

    You need to have date filters (Where Clause on Start Date and End date) in SQL query which is used to render the report data. Those date filters should have parameters referenced in the report.

    After that  create data driven subscription , where you need to write SQL query which will give you start date and end date value.

    Those values you can provide as parameters to the report.

    Data driven subscription query would like be like below:-

    select dateadd( month, -1,dateadd(month,datediff(month,0,Getdate()),0)) startdate,dateadd(month,datediff(month,0,Getdate()),-1) enddate
    
    Where day(getdate())=1
    
    UNION
    
    select dateadd( WEEK, -2,dateadd(WEEK,datediff(WEEK,0,Getdate()),0)) startdate,dateadd(WEEK,datediff(WEEK,0,Getdate()),-1) enddate

    Thanks

    Prasad

    • Proposed as answer by Yuliana Gu Monday, March 6, 2017 8:50 AM
    • Marked as answer by RonTech1 Monday, March 6, 2017 9:09 AM
    Monday, March 6, 2017 6:15 AM