I am currently wrestling with a generally simple “IIf” evaluation on an SSRS report. The evaluation takes a calculated field from an underlying BAQ (because this is configured as a “BAQ Report”) and evaluates “greater than or equal to” a value derived from a report option field passed along to the report:
I can return both the calculated field and the report option values on the report in a simple text concatenation:
I can perform a successful mathematical operation using both of the passed values:
I can perform a comparison evaluation using the two values and return a logical result (here, we’re saying “0 >= 14 is false”):
As soon as I wrap that into a “COUNT” function, the “First aggregates” used in my statement violate the suited purpose of “counting” and cannot be used as nested aggregates:
… so, I try to insert dataset scope back into the function:
At this point, I’m not sure what my syntax should be. Might anyone have any ideas? Can I not perform evaluations on two values derived from two distinct datasets?
I read through this a couple of times and I think I see where you’re going with it.
First of all, you’ll want to use SUM instead of COUNT in this case. The IIF statement is handling the conditional part of it so the count of rows matching that condition will just be the total of all the ones.
Next, there needs to be some sort of iteration over the dataset. Using FIRST for the Calculated_DaysIdle will only ever return one value, so your count will always either be 1 or 0. Presumably you’d be doing this in some sort of table as you display the data from the rows to generate the report. However, if the whole report is just an aggregate (this scenario would probably be better handled by adjusting the BAQ) you can always add a hidden table, iterate through, sum the total, and pull the value by referencing the value of the textbox containing the sum (ReportItems!TextboxName.Value).
And finally, I usually get around the pesky syntax issues like that with the use of an internal parameter (SSRS, not Epicor). Just add a report parameter, set the visibility to Internal, and give it a default value from the desired dataset. This basically acts like a global variable within the report and you can reference it from any expression. The formula =Sum(iif(Fields!Num.Value >= Parameters!ReportParameter1.Value, 1, 0)) outside the detail row in the table then returns the total count. I suppose you could always make a table that only has one cell and no details row. As long as it’s pointing to the dataset, it should work.
Here’s a report that runs through some examples of the above. Just change the DataSource to anything in your environment - the dataset doesn’t actually query anything, it just generates a list of numbers 0-12. ParamTest.rdl (29.1 KB)
