This is the expression I'm trying to evaluate:
Sum(IIF(QUALITY<=9.0,1.0,0.0))
The problem is that
string expr = "Sum(IIF(QUALITY<=9.0,1.0,0.0))";
dataTable.Compute(expr, "")
throws an error saying
Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.
The main challenge is that I can't rewrite the expression by hand, since it's coming to my program in a string from an RDL file, so it originally looks like
Sum(IIF(Fields!QUALITY.Value<=9.0,1.0,0.0))
and my program needs to automatically convert that into a string that will be a valid expression when passed to dataTable.Compute(). I determined from Microsoft's documentation of the Compute method that C# doesn't like the "Fields!" or ".Value" parts of the syntax, and by removing them I have simpler expressions working fine; for example, these statements both return the expected values:
dataTable.Compute("Sum(QUALITY)");
dataTable.Compute("Count(QUALITY)");
Is it possible to do what I'm trying to do? I feel like I should just need a slight change in syntax somewhere for it to work, but I haven't been able to find an answer on the internet and it's getting pretty frustrating.
Here are some of the things I've tried so far, to no avail:
Sum(IIF([QUALITY]<=9.0,1.0,0.0))
Sum(Convert(IIF(QUALITY<=9.0,1.0,0.0), 'System.Double'))
Sum(IIF(Convert(QUALITY, 'System.Double')<=9.0,1.0,0.0))
One way to do this might be to add an aggregate column to the DataTable with the Expression set to IIF(QUALITY<=9.0,1.0,0.0).
dataTable.Columns.Add("AggregateColumn", typeof(double), "IIF(QUALITY<=9.0,1.0,0.0)");
then sum or count the aggregate column.