SSRS IIF efficiency

Question

I am using the following expression to tier the sales figures.

=sum(iif(Fields!InitialValue.Value>=500000 and Fields!InitialValue.Value<1000000,Fields!InitialValue.Value,nothing))

Basically, I just change the greater than and less than values for each cell. We have 4 tiers.

From what I understand, the IIF statement will go through each line and evaluate it before returning anything. I am also averaging the size of each new account, so I have 8 cells that evaluate the data each time. I will also need to add how many accounts are in each tier, which means 12 passes at the same data. It takes some time to generate this report.

Is this the most efficient method?

Thanks in advance for all your help!

Answer 1

One way you could increase the efficiency at this, from what I can tell is one of two way, the way I would do it is add a column to your query that labels each row by Tier, this would mean when the data gets to SSRS it is already set and never needs to be evaluated. My theory is SSRS is not as smart as a query optimizer. Another way to do it, that may or may not speed it up, is add a calculated field to your data set that especially does the same thing. I believe this would have SSRS calculate it once and that is is.