CODEWITHSUNDEEP
reporting-servicesexpressionformula
user3306489
user3306489

Reputation: 157

SSRS expression condition using a formula to show dates based on parameter input

I'm struggling to write an SSRS expression to show dates based on a group heading and parameter date. The expression needs to look at the cell value and add 1 & 7 days. The date parameter is called inputdate

so if the cell value = '4) Overdue Week 1 - ' then inputdate + 1 &-& inputerdate +7 as WE1 if the cell value = '5) Overdue Week 2 - ' then inputdate + 8 &-& inputerdate +14 as WE2 if the cell value = '6) Overdue Week 3 - ' then inputdate + 15 &-& inputerdate +21 as WE2

and so on...

The cell is based on a grouped header.

Any help is appreciated. Thanks in advance

This is my attempt:

   =IIf((Fields!Type_of.Value = "4) Overdue Week 1 - '",
DateAdd("d",1,Parameters!inpdate.Value) +-+ DateAdd("d",7,Parameters!inpdate.Value),0)
,IIf((Fields!Type_of.Value = "5) Overdue Week 2 - '"
,DateAdd("d",8,Parameters!inpdate.Value) +-+ DateAdd("d",14,Parameters!inpdate.Value),0)
IIf((Fields!Type_of.Value = "6) Overdue Week 3 - '"
,DateAdd("d",15,Parameters!inpdate.Value) +-+ DateAdd("d",21,Parameters!inpdate.Value),0)
IIf((Fields!Type_of.Value = "7) Overdue Week 4 - '"
,DateAdd("d",22,Parameters!inpdate.Value) +-+ DateAdd("d",28,Parameters!inpdate.Value),0)
IIf((Fields!Type_of.Value = "8) Overdue Week 5 - '"
,DateAdd("d",29,Parameters!inpdate.Value) +-+ DateAdd("d",35,Parameters!inpdate.Value),0)
IIf((Fields!Type_of.Value = "9) Overdue Week 6 - '"
,DateAdd("d",36,Parameters!inpdate.Value) +-+ DateAdd("d",42,Parameters!inpdate.Value),0)

but it returns an error;

Severity Code Description Project File Line Suppression State Error [rsCompilerErrorInExpression] The Value expression for the field 'Date_Type_of' contains an error: [BC30198] ')' expected.

Upvotes: 0

Views: 433

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

In addition to what Harry mentioned, your IIF statement syntax is incorrect. You don't have any closing parenthesis nor a final ELSE.

Here's what should work:

=IIf(Fields!Type_of.Value = "4) Overdue Week 1 - '",
    DATEADD("d", Parameters!inpdate.Value, 1) & "-" & DATEADD("d", Parameters!inpdate.Value,7),
    IIf(Fields!Type_of.Value = "5) Overdue Week 2 - '",
        DATEADD("d", Parameters!inpdate.Value,8) & "-" & DATEADD("d", Parameters!inpdate.Value, 14),
        IIf(Fields!Type_of.Value = "6) Overdue Week 3 - '",
            DATEADD("d", Parameters!inpdate.Value, 15) & "-" & DATEADD("d", Parameters!inpdate.Value, 21),
            Parameters!inpdate.Value)
        )
    )

I indent to keep track of the levels of IIFs. But as Harry also mentioned, a SWITCH is better when you have multiple scenarios.

=SWITCH(    Fields!Type_of.Value = "4) Overdue Week 1 - '", DATEADD("d", Parameters!inpdate.Value, 1) & "-" & DATEADD("d", Parameters!inpdate.Value,7),
            Fields!Type_of.Value = "5) Overdue Week 2 - '", DATEADD("d", Parameters!inpdate.Value,8) & "-" & DATEADD("d", Parameters!inpdate.Value, 14),
            Fields!Type_of.Value = "6) Overdue Week 3 - '", DATEADD("d", Parameters!inpdate.Value, 15) & "-" & DATEADD("d", Parameters!inpdate.Value, 21), 
            True, Parameters!inpdate.Value
        )

Upvotes: 1

Related Questions