Third Party Report Writer Compatibility

[Andrew_C]

Hello,

Anyone that has worked with ERP solutions i.e. SAP and or equivalents soon find out that the core platform does not have all the standard reports an end user may need. Or the user needs ad-hoc reports which are not provided by the platform. Asking a developer to produce those reports is painfully slow and generally expensive in my experience.

The generally accepted solution is to use an independent report writing tool such as SAP Crystal Reports which connects to the platform database and can produce complex ad-hoc reports created by end users using a GUI. I have successfully used crystal reports with a number of different database platforms.

Has anyone in the community successfully connected a third party report writer to the SuiteCRM 8 database ? Would you please share your experiences in terms of the software used and any issues encountered, specifically database dead lock when both SuiteCRM and the report writer access the database simultaneously ?

A question for the developers:

SuiteCRM has it own database user and password logon. Do you see any issues with creating another user logon to be used specifically by a report writer ? Does SuiteCRM assume it has exclusive access to the database or has it been design for multi user access ? If SuiteCRM was actively being used and a report was reading data from the database would SuiteCRM continue working or crash ? I’m assuming the platform has enough processing power and performance is not an issue.

Thanks.

[pgr]

SuiteCRM Analytics - Solution, Bug Tracker, Feedback!

SalesAgility are excited to announce the new, ready-to-use business analytics platform, SuiteCRM Analytics. This Open Source and free to download solution was built using Pentaho BI Suite Community Edition and will provide SuiteCRM users with the ability to launch, administer and customise their own BI platform. You can check out more information here on our blog post: https://suitecrm.com/suitecrm-analytics-open-source-bi-platform-built-with-pentaho-ce/ If you have any issues installing or ru…

[Andrew_C]

Hello @pgr,

Thanks for the link to SuiteCRM Analytics. I would ask anyone in the community who is using this software to provide some feedback on its capabilities and find out if the project is being actively developed. The link page was created in 2019. There is little mention on it in this forum.

I would also ask @pgr to remove the solution check box from his reply as your answer is an option and does not answer the other questions in my thread.

I’m still trying to determine if SuiteCRM and a third party report writer would work together as described in my original thread. Any help from the community would be welcomed.

Thanks.

[pgr]

It’s basically a third party reporting tool, and somebody integrated it with SuiteCRM. The tool itself evolves, but the integration probably doesn’t need much novelty, I guess.

About your other questions regarding database concurrent use, don’t worry. Yes, these database are all multi-user, and allow concurrent access. Anyway, the problem is much simpler to manage with users who are only reading from the DB, like this kind of tool does.

[Andrew_C]

Hello @pgr,

Thank you for confirming that the SuiteCRM database is multi-user and supports concurrency.

Yes report writers mainly do reads.

With regards to how widely used SuiteCRM Analytics is by the community well that is a mystery. No one has replied to this thread so i’m assuming few are using it at the moment.

SuiteCRM Analytics worth Installing?

I’ve had my eye on SuiteCRM Analytics for a while now and just never got around to installing it. I’m starting to get more and more clients demand more complex BI from SutieCRM than standard reports can provide. Can anyone who’s installed it and using it give me a review? Is it worth the time to setup and install? Does it work well? Is it robust enough for a wide range of reporting and analytics? Would appreciate any feedback.

I will stick with a report writer for now until SuiteCRM Analytics becomes more widely used by the community or until someone writes a review on the technology.

If anyone is successfully using SuiteCRM Analytics please write and publish a review.

Thanks.

[pstevens]

Hey @Andrew_C I ended up just using Google Charts to write my own graphs and reports for users to display on the dashboard (with some help from one of @pgr 's posts.

[rsp]

Could you please share your code and config for Sankey chart?

[pstevens]

@pgr gets credit for this one. I think I had to make a few tweaks but like 99% @pgr.

<html>
  <head>
       <?php
  global $current_user;
  global $db;
  $rows=array();

  $query = "SELECT lead_source, sales_stage as stage, amount as total, user_name as user
            FROM opportunities
            LEFT JOIN users ON users.id = opportunities.assigned_user_id
            WHERE opportunities.deleted=0
            ";
  $result=$db->query($query,true);
  while( $row = $db->fetchByAssoc($result)) {
     $rows[]= $row;
   //  $rows[]= array($row['lead_source'], $row['stage'], $row['total'], $row['user']);
   //echo $row['stage'], ": ", $row['total'], '<br/>';
  }
//  if (count($rows)==0) echo 'Query returned no rows.';
//  var_dump($rows);
//  foreach($rows as $key => $value) {
//     var_dump($key);
//     var_dump($value);
//   }
?>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      // Load the Visualization API and the corechart package.
//      google.charts.load('current', {'packages':['corechart']});
      google.charts.load('current', {'packages':['sankey']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChart);
      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'From');
        data.addColumn('string', 'To');
        data.addColumn('number', 'Amount');
        data.addRows([
            <?php foreach ($rows as $r) {
               echo "['", $r['lead_source'], "', '", $r['user'], "', ", $r['total'], "],", PHP_EOL;
               echo "['", $r['user'], "', '", $r['stage'], "', ", $r['total'], "],", PHP_EOL;
             }
            ?>
        ]);

        // Set chart options
        var colors = ['#a6cee3', '#b2df8a', '#fb9a99', '#fdbf6f',
                  '#cab2d6', '#ffff99', '#1f78b4', '#33a02c'];
        var options = {'title': 'Opportunities', // Source, Employee, Outcome, with Amount shown as Width',
                       'width': 400,
                       'height':400,
        sankey: {
          node: {
      //      colors: colors
          },
          link: {
            colorMode: 'gradient',
      //      colors: colors
          }
        }

        };

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.Sankey(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
   </script>

<body>
<div style="zoom: 1.0; -moz-transform: scale(1.0); transform-origin: 0 0; -moz-transform-origin: 0 0">
    <!--Table and divs that hold the pie charts-->
    <table class="columns">
      <tr>
        <td><div id="chart_div" style="border: 1px solid #ccc;"></div></td>
      </tr>
      <td><div id="barchart_div" style="border: 1px solid #ccc;"></div></td>
      </tr>
    </table>
</div>
  </body>

</html>

[pstevens]

@rsp the cool one is the line chart where I have a drop down so the user can change from “all users” to “current user” I did that as proof of concept, but you could have the user customize the report and thus modify the query based on what they choose. It’ super versatile!

[rsp]

But, where I have to create this file and paste code? I guess, I need your code for line chart too then.
It is really cool dashlet to have on the home page.

If you want, I could create a separate topic on this forum.

[pstevens]

Here’s the complete discussion on Google Charts:

How to add other charts in Report Charts, For ex. Line Chart, Bubble Chart, Gauge chart, Funnel Chart etc.

Hello, There are some errors in: $query = “SELECT sales_stage as stage, SUM(amount) as total FROM opportunities where assigned_user_id=’”.$current_use$"’ GROUP BY sales_stage"; Thanks.

[rsp]

I clicked on Add Dashlets and paste my URL. But, I am getting an error.

Incorrect website location is specified

Console error:

MySugar.js?v=6WY0eIRvQx-xSuYs2iwxMg:49 Uncaught ReferenceError: newTop is not defined
    at Object.fillInConfigureDiv (MySugar.js?v=6WY0eIRvQx-xSuYs2iwxMg:49:481)
    at Object.handleTransactionResponse (sugar_grp1_yui.js?v=6WY0eIRvQx-xSuYs2iwxMg:31:4422)
    at sugar_grp1_yui.js?v=6WY0eIRvQx-xSuYs2iwxMg:31:3935

[pstevens]

Here is the fix for that problem.

Recent Code Addition Causes internal iFrame URL's to stop working

[rsp]

Hello,

I’ve added below code to end of my utils.php file under include/. But when I save custom entry point link to website tab under Add Dashlets. I am getting same error - Incorrect website location is specified.

What I am missing here? Do I have to repair from admin panel or delete something form cache folder?

From line 1620

/**
 * @param $endpoint
 * @return bool
 */
function isSelfRequest($endpoint) : bool {
    $domain = 'localhost';
    if (isset($_SERVER["HTTP_HOST"])) {
        $domain = $_SERVER["HTTP_HOST"];
    }

    $siteUrl = SugarConfig::getInstance()->get('site_url');
    if (empty($siteUrl)){
        $siteUrl = '';
    }
     // Get the list of valid custom entry points
     $customEntryPoints = getCustomEntryPoints();
     // Check if the endpoint matches a URL of the form "$siteURL/?entryPoint={entry_point_name}"
     $entryPointPattern = "/^" . preg_quote($siteUrl, '/') . "\/\?entryPoint=(" . implode('|', $customEntryPoints) . ")$/i";
     if (preg_match($entryPointPattern, $endpoint)) {
         return false; // It matches a valid custom entry point URL
     }
     // Check if the endpoint contains the domain or site URL
    return stripos($endpoint, $domain) !== false || stripos($endpoint, $siteUrl) !== false;
}


function getCustomEntryPoints() {
    $customEntryPoints = [];
    // Specify the path to the entry_point_registry.ext.php file
    $entryPointRegistryPath = 'custom/application/Ext/EntryPointRegistry/entry_point_registry.ext.php';
    if (file_exists($entryPointRegistryPath)) {
        // Include the file
        include($entryPointRegistryPath);
        // Check if $entry_point_registry is an array
        if (isset($entry_point_registry) && is_array($entry_point_registry)) {
            // Iterate through each entry point definition
            foreach ($entry_point_registry as $entryPointName => $entryPointData) {
                // Add the entry point name to the custom entry points list
                $customEntryPoints[] = $entryPointName;
            }
        }
    }
    return $customEntryPoints;
}

[pstevens]

Did you do a repair and rebuild? If that does not work try rebuild all the JS stuff. Also check file permissions of the new files you created. It does work, I’ve tested on a few different installations and others have reached out to me saying it worked for them.

[rsp]

I tried it. So, when I click on pencil icon on newly added dashlet. I can see Website Location field is blank.

Also, I have below error in the browser’s console:

MySugar.js?v=6WY0eIRvQx-xSuYs2iwxMg:203 Uncaught ReferenceError: newTop is not defined
    at Object.fillInConfigureDiv (MySugar.js?v=6WY0eIRvQx-xSuYs2iwxMg:203:24)
    at Object.handleTransactionResponse (sugar_grp1_yui.js?v=6WY0eIRvQx-xSuYs2iwxMg:31:4422)
    at sugar_grp1_yui.js?v=6WY0eIRvQx-xSuYs2iwxMg:31:3935

I think it is not able to save that web url. What’s missing?

p.s. - I have http local host url for that sankey chart

[pstevens]

I think you’re going to want to confirm your hostname in your config. I’m thinking the localhost configuration you have setup is the problem.

[rsp]

@pstevens

hostname in your config

Are you talking about the below field:

‘site_url’ => ‘http://example.com/suitecrm’,

[pstevens]

yes, it has to match the actual URL you are hosting locally or this won’t work because it checks this hostname against the url of the link you are trying to add.

[pstevens]

Maybe I’m not understanding fully, are BOTH your suitecrm install AND the sandkey chart file locally on your PC? Or is suitecrm hosted on the web and you’re trying to use a localhost url to embed?