Although my need is larger I have skinnied this down to a straight table with 1 dimension and 1 measure, the measure is sum(FIELD).
Reload works fine and on state change I can utilize an ODBC trace I can see that the generated SQL is produced properly and when I execute the generated SQL directly against the database the correct totals are produced.
I can also add a table box that contains only the measure field and I see values returned to the UI. Listboxes with dimensions seem correct. I can replace SUM(FIELD) with MAX(FIELD) and it gives me the largest value from the returned rows but SUM(FIELD) does not total and only produces NULL.
Not sure but can you try to create a straight table with Dimension as FIELD and IsNum(FIELD) as expression and see if any of the value is 0. 0 would mean that one of the values within FIELD is text and that is causing the Sum() function to not work.
I am relatively novice when it comes to Direct Discovery. So pardon me for not understanding some of the terminology here. What does 'double load' mean? Is it Direct Discovery related thing? Also, I am confused: you mentioned that in the table box the Field is left aligned, but in the list box its right aligned??? Not sure I completely understood your findings.
No worries Sunny, first time use case for DD for me as well.
Double load means I brought the same field in as a measure and a dimension.
What I was trying to say by left aligned and right aligned is QV is displaying one as a number and the other as a string. With QV though I know I can't always count on the display, it's just I can't see what type it thinks the measure value is outside of putting it in a listbox or tablebox.
I tried aggr(max(FIELD),DIM) and get the max value, sum(aggr(max(FIELD),DIM)) and get max value. aggr(sum(FIELD),DIM) and I get null.
How's your data model looks like? I am also working on some direct discovery scenarios. If you have a key in the direct table to join to in-memory dimension tables, be careful, as it sums the values in the key not giving the good results.