Search code examples
sql-serverreporting-services

Set local variable in rdlc expression

Is it possible to somehow create variable in rdlc report expression in 'online' manner?

For example, I have following expression:

=IIf(First(Fields!BillingAccount_billtostateprovince.Value, "Invoice") <> "", 
First(Fields!BillingAccount_billtostateprovince.Value, "Invoice") + " ", 
"")

I suppose that I'm evaluating following expression First(Fields!BillingAccount_billtostateprovince.Value, "Invoice") twice. I don't like it and it looks ugly... I would prefer to create variable in the scope of current expression and use it.

Is it possible?

Solution

  • As user3056839 said, Welcome to SSRS!

    Anyway, what you want is not possible since what you are writing right now is not a script but it's just an expression. It's a single statement that returns a value, so you cannot declare variables, use loops or anything that is part of a script.

    You have to use exactly what you are writing.

    Also it's not rare to see an IIF expression like yours. The one I constantly see is IFF( IS NOT NULL, , 'N/A'). The field may actually be evaluated twice, but there's nothing you can do. It's ugly but it's the only way you can do that.

    Just think about the CASE WHEN clause:

    SELECT 
CASE WHEN MyField IS NOT NULL THEN
   MyField ELSE 0
END

    You are evaluating the field twice, but there's nothing you can do :)