SSRS Parameter Expression: Previous Workday

Depends on whether your company works on a Saturday, but one of these SSRS Expressions will do the trick:

Basically WEEKDAY() will return the day number of the week, which will return:
– Sunday = 1
– Monday = 2
– etc.
(Doesn’t matter which Language (e.g. en-GB, etc.) you’ve set in the report)

So if you would like to select the previous workday (including Saturdays):

=IIF(WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY())),TODAY()),
DATEADD("d",-1,TODAY())
)

‘In case it’s Monday, select Today’s date minus 2 days. Otherwise return yesterday’
If you would like to exclude the entire weekend:

=IIF(WEEKDAY(TODAY())=1 OR WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY()))-1,TODAY()),
DATEADD("d",-1,TODAY())
)


SSRS Default Date Parameter values (cookbook special)

Today I caught myself re-typing these so very common pieces of code…but there won’t be a next time!
CTRL+A…CTRL+C…TAB…CTRL+V…CTRL+S 😀

Current date:

=CDate(
CSTR(YEAR(NOW()))+"-"+
CSTR(MONTH(NOW()))+"-"+
CSTR(DAY(NOW()))
)

Previous week (-7 days):

=DateAdd(DateInterval.Day,-7,
CDate(
CSTR(YEAR(NOW()))+"-"+
CSTR(MONTH(NOW()))+"-"+
CSTR(DAY(NOW()))
)
)


First day of the current month:
=CDate(
CSTR(YEAR(NOW()))+"-"+
CSTR(MONTH(NOW()))+"-01"
)

First day of the previous month:
=DateAdd(DateInterval.Month,-1,
CDate(
CSTR(YEAR(NOW()))+"-"+
CSTR(MONTH(NOW()))+"-01"
)
)

Last day of the previous month:
=DateAdd(DateInterval.Day,-1,
CDate(
CSTR(YEAR(NOW()))+"-"+
CSTR(MONTH(NOW()))+"-01"
)
)

SSRS InScope() function: The key to format your matrix!

The InScope() function checks to see if the current item is in the specified scope.
For a basic matrix report with the use of ‘Totals’ on both axes:

The Green cells fall out-of-scope of the ColumnGroup, and In-Scope for the RowGroup
The Grey cells fall out-of-scope of the RowGroup, and In-Scope for the ColumnGroup
The Light blue cells fall In-Scope of both the ColumnGroup and the RowGroup.
The Dark blue cell falls out-of-scope of both the ColumnGroup and the RowGroup.

Expression example code:

=IIf(InScope(“ColumnGroup”),
IIf(InScope(“RowGroup”),”LightBlue”, ”Grey”),
IIf(InScope(“RowGroup”),”DarkGreen”, “DarkBlue”)
)

Thanks to Luke Hayler for his great example.

SSRS month labeling (MDX dataset)

SSRS Switch Example (within expression) using MDX dataset.

In my case, the parameter dataset (MDX) doesn’t have a nice label for @Month:
@Month.Value = [Time].[Fiscal Month].&[2010-01-01T00:00:00]
@Month.Label = 2010-01-01 00:00:00

So I’m using a switch to project the name of the month on my report.

=Switch(
MONTH(CDATE(Parameters!Month.Label))=1,”January”,
MONTH(CDATE(Parameters!Month.Label))=2,”February”,
MONTH(CDATE(Parameters!Month.Label))=3,”March”,
MONTH(CDATE(Parameters!Month.Label))=4,”April”,
MONTH(CDATE(Parameters!Month.Label))=5,”May”,
MONTH(CDATE(Parameters!Month.Label))=6,”June”,
MONTH(CDATE(Parameters!Month.Label))=7,”July”,
MONTH(CDATE(Parameters!Month.Label))=8,”August”,
MONTH(CDATE(Parameters!Month.Label))=9,”September”,
MONTH(CDATE(Parameters!Month.Label))=10,”October”,
MONTH(CDATE(Parameters!Month.Label))=11,”November”,
MONTH(CDATE(Parameters!Month.Label))=12,”December”
)

For “January 2010”, just use:
MONTH(CDATE(Parameters!Month.Label))=1,
(“January “+CSTR(YEAR(CDATE(Parameters!Month.Label)))),