Search code examples
sql-serverdatetimessrs-2012subscription

How to set SSRS subscription time as an expression

I have a report and the report StartDate and EndDate parameters are using the expression as a default value below.

=DateAdd(DateInterval.Minute,0,DateAdd("h",7,DateAdd("h",-24,Today())))

=DateAdd(DateInterval.Minute,0,DateAdd("h",7,Today()))

When I execute the report, the report is starting from the day before at 7 AM to today 7 AM. enter image description here

I would like to keep the report Start time and End time like this(07:00). I also want to send the report to customer every day 7:30 AM but the report needs to be executed according to start date and end date paramaters.

Example: today 12.12.2019

Subscription time will be 07:30 AM

report needs to be running this time: StartDate : 11/12/2019 07:00:00 EndDate : 12/12/2019 07:00:00

But when I schedule subscription every day and 7:30 AM, I received report from one day before 7:30 AM and today 7:30 AM. enter image description here

enter image description here

I just want to see report from 7:00am to 7 am. Even if I change schedule time.

Could you please help me about this problem. How can I edit my subscription? Is it possible to write an expression in "date/time from - date/time to" fields in subscription?

Solution

  • I resolved my issue. There are 2 solutions for it.

    Option 1 :

    In the report design If it is must to have those date parameters must be DATTEIME and to allow TIME factor as well then and if you want to run the report which is subscribed always for Yesterday 7:00 to today 7:00 am then I would not rely on sending any parameter values based on expressions …I would set up Date/Time Parameter in report design to allow null values and send null values as default from the subscription settings.

    Then In report SP you can always add a clause at the TOP like 

    if @startDateTime is null AND @endDateTime is null
begin
set @startDateTime =CONVERT(VARCHAR(10), getdate(), 111);
set @startDateTime =dateadd(hh,7,( dateadd(d,-1,@startDateTime)))         
set @endDateTime =dateadd(d,1,@startDateTime)
end

    and let the rest SP be same

    Option 2 :

    If you can change the report parameters to be a type only DATE then its easy always send =Today() in your subscription parameter for both Start & End

    Then In report SP you can always add a clause at the TOP like

    if @startDateTime = @endDateTime
begin
set @endDateTime =CONVERT(VARCHAR(10), @endDateTime, 111);
set @endDateTime =dateadd(hh,7,@endDateTime)
set @startDateTime =dateadd(d,-1,@startDateTime)
end

    and let the rest SP be same

    Option 2 is better if they are ok to have Start & End date parameter as just DATE instead of DATETIME.

    Any way Using any of these options do handle this in SP… you can always have control in future if they want to change time form 7:00 am to any other time …no need to change report design just update SP…2 minutes

    You can schedule this report for any time of the day and it will always send them a report for Yesterday 7:00 to Today 7:00