SSRS date & time parameter based on time of day

Question

I'm trying to write an expression for the value parameter in Microsoft's SSRS but struggling. Basically we have a report that starts at 0800 and runs for 24hrs before it resets again the next 0800hrs the report is extracted at several times over the 24hr period.

I think I understand the logic however I'm not verse enough to transfer that to code. Something like:

If today's time => 0800 Then
Extract values from today's time 0800 onwards
Else If today's time < 0800 Then
Extract values from today + yesterday after 0800

Answer 1

I think you would want something similar to below, but without more information regarding report values, you will have to piece that part in:

=Iif((FormatDateTime(Now(),HH:mm)) >= "0800", 
TodaysTimeOnwards.Value, 
Iif((FormatDateTime(Now(),HH:mm)) < "0800", YesterdayTime.Value, Nothing)

Answer 2

I believe this does what you're describing

Start: =Now().AddHours(-8).Date.AddHours(8)

End: =Now().AddHours(-8).Date.AddHours(8).AddDays(1).AddSeconds(-1)

Answer 3

Do you mean extract values as to get diferent dataset values depending on the reporting execution time? or it is just to print in the report a time value?

If the first, you will organize your code, trought sql inline or store procedure no as ssrs expression I think but as given a @DateFrom and a @DateTo parameters given values depending on the time of the execution.

example:

1-->in the datasource features this will be the sql you need to retrieve your data

SELECT * FROM DataSet WHERE Time BETWEEN @DateFrom AND @DateTo

2-->to give @DateFrom and @DateTo appropiate values depending on the time of the execution.

a) set @DateFrom default values via an expression it within its features menu,

-@DateFrom : "=Iif(DatePart("h",now())>8,DateAdd("hh",+8,DateAdd("dd",DateDiff("dd",0,now()),0)),DateAdd("hh",+8,DateAdd("dd",DateDiff("dd",0,getdate())-1,0)))"

-@DateTo : "=Iif(DatePart("h",now())>8,now(),DateAdd("hh",+8,DateAdd("dd",DateDiff("dd",0,now()),0)))"

b) set @DateFrom default values via a query values configuring it within its features values.

create a new datasource for @DateFrom like this:

IF DatePart(hh,getdate()) >= 8
BEGIN

 SELECT DateAdd(hh,+8,DateAdd(dd,DateDiff(dd,0,getdate()),0))

ELSE 

 SELECT DateAdd(hh,+8,DateAdd(dd,DateDiff(dd,0,getdate())-1,0))

and then create a new datasource for @DateTo like this:

IF DatePart(hh,getdate()) >= 8
BEGIN

 SELECT GetDate()

ELSE 

 SELECT DateAdd(hh,+8,DateAdd(dd,DateDiff(dd,0,getdate()),0))