CODEWITHSUNDEEP
reporting-services
DMAC
DMAC

Reputation: 17

SSRS Expression syntax for choosing a second field if the first one is null

I am trying to add an expression where one field (userdefined1) is selected unless it is null in which case a second field (accountname_calculated) is selected. They are both text fields. I've tried multiple permutations of the syntax below and am running into aggregate errors. Both Fields I want to select are in the same dataset (Dataset1).

=First(IIF(IsNothing(Fields!userdefined1.value,Fields!accountName_calculated.Value),"DataSet1")) The value expression for the text box uses an aggregate expression without a scope

=iif(isNothing(Fields!userdefined1.Value), Fields!accountname_calculated.Value, Fields!userdefined1.Value)     the value expression for the text box refers directly to userdefined1 without specifying a dataset aggregate

=iif(isNothing(first(Fields!userdefined1.Value), Fields!accountname_calculated.Value, Fields!userdefined1.Value), "dataset1") the value expression for the text box refers directly to accountname_calculated without specifying a dataset aggregate

I tried various other combinations and got similar errors. Can some one please let me know how to correct this syntax. Much appreciated.

Upvotes: 0

Views: 95

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

It seems you are using a text box that's not part of a dataset which gives the first error. The method you are using will select the First of the values in the dataset. This should really only be used if your data only has one row or all the values for these fields are the same for each record.

That being said, I believe it would work if you wrapped the IIF in the FIRST with the scope:

=FIRST(IIF(isNothing(Fields!userdefined1.Value), Fields!accountname_calculated.Value, Fields!userdefined1.Value), "dataset1")

Upvotes: 0

Related Questions