1

This works:

=IIf(
    Fields!EntityID.Value Is Nothing,
    Fields!Foo.Value,
    Join(
        LookupSet(
            Fields!EntityID.Value,
            Fields!EntityID.Value,
            Fields!Bar.Value,
            "dsMain"
        )
    , ",")
)

But this doesn't:

=IIf(
    Fields!EntityID.Value Is Nothing,
    Fields!Foo.Value,
    Count(
        LookupSet(
            Fields!EntityID.Value,
            Fields!EntityID.Value,
            Fields!Bar.Value,
            "dsMain"
        )
    )
)

I get #Error, like if the formula is broken. What's wrong with it?

MMalke
  • 1,857
  • 1
  • 24
  • 35
  • The syntax is correct but the LookupSet doesn't produce the table array that COUNT is expecting. The `.Length` looks easier but I've used VB for SUMming. https://stackoverflow.com/questions/36131860/need-help-in-calculation-using-two-datasets-using-expression-ssrs/ – Hannover Fist Sep 10 '21 at 23:45
  • @HannoverFist I don't know why, but the LookupSet result doesn't have a .Length property, at least for me. Intellisense doesn't suggest it, and it is underlined in red as incorrect syntax. – MMalke Sep 12 '21 at 04:25
  • If you want to try the VB solution you can use the above link and just change the `suma += Convert.ToDecimal(item)` line to `suma += 1` to change it from summing to Counting. You might need to change the type of your column `(ByVal items As Object()) As Decimal`. Maybe change the function name from SumLookup to CountLookup. – Hannover Fist Sep 12 '21 at 17:50

1 Answers1

1

You can use the Length property of an array to get the element count

=IIf(
    Fields!EntityId.Value Is Nothing,
    Fields!Foo.Value,
        LookupSet(
            Fields!EntityId.Value,
            Fields!EntityId.Value,
            Fields!Bar.Value,
            "dsMain"
        ).Length
)

enter image description here

enter image description here

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • I had already seen that suggested in other threads, but at least for me, intellisense doesn't show a .Length property for my LookupSet, and it is underlined in red as syntax error when I type it in. – MMalke Sep 12 '21 at 04:27
  • 1
    I don't know why you have intellisense issues. Sure thing is that it works, I can provide screenshots. – niktrs Sep 12 '21 at 04:53
  • You are right. It does work even with the error indication and intellisense not working. Thank you. – MMalke Sep 13 '21 at 13:36