Group projects by Fiscal Year Date expression based on one field value, if field value is null then group projects based on another field value

Question

I am running a report where the projects are grouped by FY based on Fields!EstSubstantial_Completion.Value. Expression below:

=IIf(Month(Fields!EstSubstantial_Completion.Value)=10,
year(Fields!EstSubstantial_Completion.Value)+1,
IIf(Month(Fields!EstSubstantial_Completion.Value)=11,
year(Fields!EstSubstantial_Completion.Value)+1,
IIf(Month(Fields!EstSubstantial_Completion.Value)=12,
year(Fields!EstSubstantial_Completion.Value)+1,
year(Fields!EstSubstantial_Completion.Value))))

The expression is working, but my supervisor would like the projects to first be grouped into a FY based on another date field Fields!Savings_Report_Date.Value first and then if the field is blank (null) reference the Fields!EstSubtantial_Completion.Value field as the date to determine FY grouping.

I am new to SSRS reports so I am unsure if there is a way to write this type of expression.

Thanks!

Answer 1

The easiest way to do this would be to add a calculated field to your dataset.

Go to the dataset properties, then the "Fields" tab, then add a new field, select calculated when prompted.

Give the field a name such as FYCalc and set the expression to

=IIF(Fields!Savings_Report_Date.Value = Nothing, Fields!EstSubtantial_Completion.Value, Fields!Savings_Report_Date.Value)

Now all you need to do is swap out Fields!EstSubtantial_Completion.Value in your current expression and use Fields!FYCalc.Value instead.

---

Optional :

When you have nested IIF statements, it's often easier to use the SWITCH function instead. It's much easier to read.

=SWITCH(
    Month(Fields!FYCalc.Value)=10, year(Fields!FYCalc.Value)+1,
    Month(Fields!FYCalc.Value)=11, year(Fields!FYCalc.Value)+1,
    Month(Fields!FYCalc.Value)=12, year(Fields!FYCalc.Value)+1,
    True, year(Fields!FYCalc.Value)
    )

The final True acts like an Else

You could simplify this further like this

=IIF(
    Month(Fields!FYCalc.Value) >= 10 and Month(Fields!FYCalc.Value) <= 12, year(Fields!FYCalc.Value)+1,
    , year(Fields!FYCalc.Value)
    )