timereporting-servicesexpressionhidevisibility

SSRS Hide Columns Based on Time Expression

I have a 2 sheet report and want SSRS to hide columns from sheet1 and sheet2 based on the time of day.

Steps and Issue: I added an expression to hide columns and data in sheet2. If the report runs after 12:00:00 PM all columns are displayed. The issue is when I schedule before 12:00:00 PM the columns are not hidden.

I added the following expression where if report is run prior to 12:00:00 PM local time hide else show.
I left out the date parameter since the report is only run 1x/month.

Sheet1: Hide columns

  1. Selecting column from tablix
  2. Right-Click choose 'Column Properties'
  3. Select 'Visiblity'
  4. Select 'Show or hide based on expression
  5. Added expression: =IIF(Globals!ExecutionTime <"12:00:00:00 PM",true,false)

Sheet2: Hide sheet

  1. Select rectangle where the tablix is placed
  2. Right-Click and select 'Rectangle Properties'
  3. Select 'Visiblity'
  4. Select 'Show or hide based on expression
  5. Added expression: =IIF(Globals!ExecutionTime <"12:00:00:00 PM",true,false)
Solution

  • Globals!ExecutionTime will return the date and time that the report started execution, not just the time. Your expression compares with just a time so it will compare the default system date (or whatever it's called, it'll be something 1900-01-01) at 12:00:00 to your execution date/time so it will always appear as 'after' the time you specified.

    You can solve this very easily by using something like

    =HOUR(Globals!ExecutionTime)<12

    Here we just extract the hour from the execution datetime and check if it's less than 12

    Also note that as this will return a boolean, there is no need for the IIF()