CommonValue "numeric" rendered as string

[berchierm]

Hi,

I'm migrating from an old version to the newest. Things have changed, but up until now I've figured everything to update my config/settings to the new way of things.

However, I'm hitting a wall with one of my report. That report is using a {CommonValue_XYZ} to select one or more element from the restriction drop down list. That CommonValue is populated dynamically with a SQL query. The list renders as expected. But when I select more that one value and execute the report, it throws an error.

A quick investigation in the logs showed that the list is rendered in the SQL as

fieldname in '10,8'

Which of course fail because "fieldname" is a field of numeric type.
The rendered SQL should be

fieldname in (10,8)

I've look these aspects to make sure the type is okay :

• The query that builds the enum, use a numeric field for the id (first column used for the "identifier")
• The Data Type is set as numeric in the Restriction Collection Editor
• Those quote are not present in my source SQL, they are added during report execution
• If I run manually the rendered SQL, but first correcting by hand the aforementioned condition, it works
• The ID themselves (10 and 8 in the examples) are the good ones, so the dropdown works fine, it's just the type

Maybe I'm just missing a simple config somewhere.
Any help would be greatly appreciated !

Have a nice day

[berchierm]

Hi again,

After a bit of local debugging the solution, I found that a line of the file "ReportRestriction.cs" has changed from

_SQLText = string.Format("({0})", (HasValue ? EnumSQLValue : "NULL"));

to

_SQLText = string.Format("{0}", (HasValue ? EnumSQLValue : "NULL"));

commit adfdb4a

And then from

_SQLText = string.Format("{0}", (HasValue ? EnumSQLValue : "NULL"));

to

_SQLText = HasValue ? "'" + string.Join(",", EnumValues.Select(s => s.Replace("'", "''")).ToList()) + "'" : "NULL";

commit 391374e#diff-f00206604f1cc94abf1d54a393c51c772901c16c764263ba9edbf02fc9033869

Given the last line, no matter the type of data, the values are joined with a , then single quotes are added around. So it seems with an enum, values are always added as string.

To fix this, I have changed that line (ReportRestriction.cs line 1669) with :

if (IsNumeric)
{
    _SQLText = HasValue ? "(" + string.Join(",", EnumValues.ToList()) + ")" : "NULL";
}
else if (IsText)
{
    _SQLText = HasValue ? "(" + string.Join(",", EnumValues.Select(s => "'" + s.Replace("'", "''") + "'").ToList()) + ")" : "NULL";
}
else
{
    _SQLText = HasValue ? "'" + string.Join(",", EnumValues.Select(s => s.Replace("'", "''")).ToList()) + "'" : "NULL";
}

With the modified code above, the generated sql is :

fieldname in (10,8)

And in case of a text enum :

fieldname in ('abc', 'def')

That's a bit quick & dirty, but it seems to works for what I need.

The case of dates should probably be taken in account too, but I think it's not as common as this to have dropdowns with dates. At least I don't have any in my reports, in the majority of the time I only use IDs so numeric data to use in a "where field in (1,2,3)" clause.

Have a nice day!

[ariacom]

Thanks for the fix, I guess the original change was made having in mind that a CommonValue is just a value...
I did not check it so far, but just few remarks:

1. You can use HelperQuoteSingle(s) instead of "'" + s.Replace("'", "''") + "'"
2. We may have Dates in drop down, I have some in my reports
3. Could be great to have an official Pull Request to integrate this in the product.

Thanks again

[berchierm]

Marked as resolved because PR #120 was merged.
Have a nice day!