SSRS - Highlight column where date is current?

Question

Is it possible to write a query for an SSRS report which highlights a column where date is current?

My table contains start(monday) and end date(friday) for each week and week(the week number). For example:

Monday: 2014-04-07, Friday: 2014-04-11, Week: 32

Monday: 2014-04-14, Friday: 2014-04-18, Week: 33

Monday: 2014-04-21, Friday: 2014-04-25, Week: 34

For week 32 (current week) I would like the column to be highlighted in SSRS, then for week 33 it will automatically change and so on....

SSRS report:

32(week) |33(week) |34(week)

1        |23       |6

3        |65       |19

45       |12       |0

JesalynOlson JesalynOlson · Accepted Answer · 2014-04-09T14:31:17

What you can do is add a column to your dataset called IsCurrentWeek which could be a bit. Then in the format of the text box you will add an expression for the Fill property stating something along the lines of:

=IIF(Fields!IsCurrentWeek.Value=1,"Green","Transparent")

You can choose whatever colors you need but this will then be dependent upon whenever the report is run.

You can determine in SQL if it is the current week with a case statement like:

SELECT GETDATE() AS WeekDate,
    CASE WHEN GETDATE() >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
              AND GETDATE() < DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0))
         THEN 1
         ELSE 0 END AS IsCurrentWeek

You just replace the GETDATE() with your own column name and add the from and other column information necessary.

SSRS - Highlight column where date is current?

1 Answers