locked
SSRS Expression: Using IIF / OR / AND RRS feed

  • Question

  • I am working with a report where I want to use an expression to control row visibility in Report Builder. The expression will determine if a name appears on a list or not. The conditions are as follows:

    If the student received a Grade F they do not appear on the list. This part is easy:  =IIF(Fields!Grade.Value = "F", True, False)

    However the list conditions are more complicated.

    If the student receives a Grade F they do not appear on the list. 

    However, if the student receives an F (no matter how many) and they get either an A, B, C, D grade they appear on the list. 

    So my IIF statement is as follows:

    =IIF(Fields!Grade.Value = "F", True, IIF(Fields!Grade.Value = "A" OR Fields!Grade.Value = "B" OR Fields!Grade.Value = "C" OR Fields!Grade.Value = "D" AND Fields!Grade.Value = "F",  False, False))

    When I run the report, I get no errors and the report opens. However, the expression does not seem to be working. Does anyone have any suggestions as to why my logic is not working, but not erroring?

    Friday, August 14, 2020 9:34 PM

Answers

  • I have resolved my issue. However it was not with the expression as I had hoped. I broke the results into two tablix's that were stacked. 

    I created a second tablix which was placed under the original one on the body of the report. The dataset "Grades", which originally was being used to feed this, I modified so it only returned results where the grades were not F's. I created a second dataset "GradesF" using this base query but this time it only returned those with F's. 

    Tablix A shows all results that were not F's. Tablix B shows all F grades.

    Using row visibility on Tablix B I set the following condition. =IIF(Count("Grades") < 1, True, False)

    I have tested this with several variants of the students results and it works as I had hoped. wish I could have worked it out using the expression. I tried CASE statements too, but now have a working solution. Thanks for reading. 


    • Marked as answer by IrishPod Saturday, August 15, 2020 2:38 AM
    Saturday, August 15, 2020 2:38 AM