by sapiens.bi

Sapiens.BI helps you produce effective reports with charts in a fast and easy way. It is a Sugar and SuiteCRM BI plug-in with powerful reporting capabilities, ease of use, elegant design, and a quick and easy setup. This Reporting Tool is easy to use, there is no need for technical knowledge or SQL. The Sapiens.BI tool comes with over 100 pre-built reports.

Free 30 day trial
Try it Now

#1145 - Reports do not work and Bad Data received

Closed Bug? created by stephentew 5 years ago

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')

  1. sapiens-bi member avatar

    sapiens.bi Provider

    5 years ago

    Hello Stephen,

    Thank you for detailed description of the issue! Can you please also tell us, just to be sure - is this issue observed on the PRO version? Or is it also on the Basic? (We can see, you are trying both).

    Thank you!

    Best Regards, IT Sapiens Team

    • stephentew member avatar

      stephentew

      5 years ago

      Yes it is in the PRO version. That is the only version we are using now

    • sapiens-bi member avatar

      sapiens.bi Provider

      5 years ago

      Quick update: Our tool is not very happy with the digit and underscore prefixes because we use something similar in our internal logic. The issue is quite clear and we should have a fix soon and will keep you updated.

  2. sapiens-bi member avatar

    sapiens.bi Provider

    5 years ago

    Hello Stephen,

    Few days ago the upgrade with a fix was sent to you (now also available in SuiteCRM staore), - hoping, you have received it and the issue is solved now?

    Thank you!

    Best Regards, IT Sapiens Team

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
  • "Comprehensive tool that allows all types of advanced reports including audit tables, custom fields and combined reports."

    Read More Reviews