Sapiens.BI PRO - Analytic Reporting

After setting up reports and starting to use them we noticed that they are rendering incorrectly. Below are the details

Analytic Reporting renders incorrect SQL for droplist values that have a single leading digit followed by an underscore

t0.sales_stage IN('m1_Lead' , 'm2_Discovery' , 'm3_Qualified' , 'm4_Proposal' , 'm5_Contract' , 'Closed_No_Oppty' , 'Closed_Nurturing' , 'Closed_Won' , 'Closed_Won_not_in_Pipeline' , 'Closed_Inactive' , 'Closed_Lost' , 'Pilot')

So the issue initially appeared to be caused by leading numeric values since it does not affect ‘Closed_Won’ or any of the other non-numeric entries.

The droplist in SuiteCRM for this field is defined as this:

$GLOBALS['app_list_strings']['sales_stage_dom']=array ( '' => '', '1_Lead' => '1. Lead', '2_Discovery' => '2. Discovery', '3_Qualified' => '3. Qualified', '4_Proposal' => '4. Proposal', '5_Contract' => '5. Contract', 'Closed_No_Oppty' => 'Closed - No Oppty', 'Closed_Nurturing' => 'Closed - Nuturing', 'Closed_Won' => 'Closed - Won', 'Closed_Won_not_in_Pipeline' => 'Closed - Won (not in Pipeline)', 'Closed_Inactive' => 'Closed Inactive', 'Closed_Lost' => 'Closed Lost', 'Pilot' => 'Pilot', );

So the “m” being prepended is not coming from there. I’ve found leading numerics on a couple of other droplists, but I finally discovered that the magic is when the database key looks like this: #_ABC (meaning a single digit, followed by a single underscore, followed by any string). When this occurs, the prefix “m” is prepended. See the fragment below from where I selected these values:

t0.sales_stage IN('m1_Lead' , 'm2_Discovery' , 'm3_Qualified' , 'm4_Proposal' , 'm5_Contract' , 'Closed_No_Oppty' , 'Closed_Nurturing' , 'Closed_Won' , 'Closed_Won_not_in_Pipeline' , 'Closed_Inactive' , 'Closed_Lost' , 'Pilot')

<...>

AND t3.ehr_percent_c IN('m0_25' , '26_50' , '51_75' , '76_100')