Wednesday, March 23, 2016

How-to add Date filters to a report

This is a quick post to answer a question i got in the comments of the "add collection selection" post.
The question was ,how to add start date and end date, start time and end time?

It's fairly easy to do ,all you need is a date field and the Between clause.
In this demo i'm going to use the client activity field as an example.

SELECT TOP 100 A.name0,
               A.user_name0,
               B.lastactivetime,
               B.lastddr,
               lasthw,
               lastsw
FROM   v_r_system A
       JOIN v_ch_clientsummary B
         ON A.resourceid = B.resourceid
WHERE  lastactivetime BETWEEN '2016-03-20' AND '2016-03-23' 


In SSRS you might want to use parameters as the date values ,in my example i create a "From" and "To" Parameters and set their default values ,making the report more user friendly

The From Parameters default value is set to "=DateAdd("d",-30,Today())" that sets the default to 30 days back.



The To Parameters default value is set to "=Today()" ,setting it's default to today.
Change the last line in the query to use the parameters

WHERE  lastactivetime BETWEEN @From AND @To 

when i run the report the two parameters should be set by default to display clients that have been active in the last month.



Hope you found this usefull.

No comments:

Post a Comment