SSRS expression, iif statement with date conditions

Question

So my default values for startDate and endDate in SSRS were set up with the following ssrs expressions.

first day of previous month ssrs expression

=DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

last day of previous month ssrs expression

 =DateAdd(DateInterval.Day, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

But that won't work alone in my case unless I want to go in on the 16th of every month and generate this report for the people requesting it for the first 15 days of the current month.

So in my default value expression for start date i am trying this iif statement...

= iif(
    DatePart(DateInterval.Day, Today() <> "20",
    DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1),
    DateInterval.Month, 1, DateSerial(Year(Date.Now), Month(Date.Now), 1)
 )

Not working out so well. So what i'm trying to do is.....

Change the default start and end date based on what day of the current month it is, So if current day of the current month equals 16, make start date 1 of current month and end date 15 of current month, if current day of the month isn’t 16 make start date first of previous month and end date last day of previous month. So then the only thing needed is to get subscription emails and what day to send them out on.

correct! the 16th of the month i want the default values to be from the 1st of the current month to the 15th of the current month, and other day its the first and last of previous month. — KBriz
Why use the same iif() expression for both subscriptions? why not just use the previous month expression on the first subscription, and the current month expressions on the second subscription? — Tab Alleman
you cant do that in sharepoint, it uses seems to use the default values of the ssrs form, so I need those default values to be different based on what day of the current month it is. a share point subscription to this report only lets u select, Report default values or u need to set them yourself in the subscription. So i need those default start and ennd date values to change based on current day (int value) of the month — KBriz
that iif statement above is what i had tried for setting the default value for start date in ssrs report — KBriz
So you can set parameter values in the subscription, but you can't use any kind of expression to make it dynamic? — Tab Alleman

Tab Alleman Tab Alleman · Accepted Answer · 2014-10-24T19:11:17

Untested, but what if you try this? (for your start date parameter):

= iif(
    DatePart(DateInterval.Day, Today()) <>  "16",
    DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)),
    DateSerial(Year(Date.Now), Month(Date.Now), 1)
 )

SSRS expression, iif statement with date conditions

1 Answers