4

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))
jcsmnt0
  • 973
  • 10
  • 15

2 Answers2

5

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.

Joe
  • 122,218
  • 32
  • 205
  • 338
  • Thanks, that does work but I'm hoping there's an easier way to go about it - the full expression I'm working with at the moment looks like `Count(ITEM_ID) - Sum(IIF(QUALITY<=9.0,1.0,0.0)))/Count(ITEM_ID)` so in order to do it that way I would have to detect all the IIF clauses in the string and substitute them with aggregate columns, which is doable but doesn't seem ideal. I'll accept your answer if a better one doesn't come along soon, I just have a hard time believing that an IIF statement within a Sum function is never valid despite both functions being available in the syntax. – jcsmnt0 Jul 05 '12 at 21:02
2

IIF(condition, value1, value2) returns value1 if condition is true, and value2 otherwise. Therefore, your original statement counts the number of all Quality fields with value <= 9. You can use the overload which includes a filter parameter to achieve this.

dataTable.Compute("Count(QUALITY)","QUALITY <= 9");

MSDN documentation.

Edit: Missed the dynamic requirement on the first go-round.

The error occurs because IIF makes this a two column operation, and Compute does not allow two column operations within aggregate functions. Meaning that Compute("IIF(Quality <= 9, 1, 0)") is valid, but Compute("Sum(IIF(Quality <= 9, 1, 0))") is not. You could be using IIF(Quality <= 9, Quality, SomeOtherColumn) instead of literals as the latter two arguments to IIF, and it seems that the parser doesn't know the difference.

You could use Joe's solution, or add more string processing in order to separate out coniditionals and move them to the filter parameter. Joe's answer is much more sensible than trying to reimplement the string parsing that happens behind the scenes in Compute.

Esoteric Screen Name
  • 6,082
  • 4
  • 29
  • 38
  • Thanks, but that won't work with other expression strings; like I said, I need a fix that my program can apply dynamically, and I can't make those sorts of assumptions. For example, I might have an expression come in that looks like Sum(IIF(QUALITY<=9.0,0.5,0.0)) which wouldn't work with your solution. – jcsmnt0 Jul 05 '12 at 20:44
  • @jcsmnt0 - Apologies, I missed that part of the question. Edit forthcoming. – Esoteric Screen Name Jul 05 '12 at 20:45
  • But according to [the MSDN DataColumn.Expression documentation](http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx) IIF is a valid function in a DataColumn.Expression? – jcsmnt0 Jul 05 '12 at 20:48
  • `IIF` is also a TSQL function. DataColumn.Expression uses SQL syntax and a subset of its functions, so you can include `IIF` as part of the argument to `Compute`, but you can't use it in your C# code. – Esoteric Screen Name Jul 05 '12 at 20:52
  • That's what I'm trying to do though, apologies if that wasn't clear. I'm not using IIF as a function within my C# code, I'm trying to use it within the argument to Compute, like dataTable.Compute("Sum(IIF(QUALITY <= 9.0, 1.0, 0.0))", "") As a side note, how do you format code in comments? The help link says to start it with four spaces but that doesn't seem to be working for me. – jcsmnt0 Jul 05 '12 at 20:55
  • Your question is clear, I just didn't read it carefully enough. You can use the backtick (``` [that's 3 of them], same key as tilde `~`) to escape characters in comments, though it won't give you the color formatting that the code block in the editor will. – Esoteric Screen Name Jul 05 '12 at 21:02
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13485/discussion-between-esoteric-screen-name-and-jcsmnt0) – Esoteric Screen Name Jul 05 '12 at 21:03