Use Rows & Columns reports in Dashlets

I have been thinking about this dashlet for about 7 years.  Its been on my 'to-do' list for probably 4 years.  It found its way to the top of that list January 1st.  I finally coded it this week.  I have been dreading it, the Reports module can be difficult to decode let alone customize.  But, after all of that it was far easier than I had imagined.  I really only had 3 issues.

Full disclosure:

This dsahlet is not 100% finished, it has the following unfixed bugs

1. The refresh doesnt work right.  Its a Javascript thing but I cant seem to find it.  The spin-y animation goes on forever and the timed refresh never happens.  I am sure all of this is solvable but it will be a version 2.0 thing.
2. I wanted to add Links, but that proved too much.  I could not find a way to retrieve the IDs and relationships from the reports module in a way that was useful.  Again I am sure it is possible but mught be a 2.0 or 3.0 thing at this point.  As a workaround I made it so that each row is a link back to the primary record. So if the report is a Cases report each row of the table links back to the case of that row.
3. I could not get the stock 'listview' to render the reports data.  It would either be garbled or simply blank.  In the end I included the jQuery plugin DataTables into the project and rendered the table that way.  It looks very Sugar-y so I am happy for the moment.  Maybe someone can show me what I am doing wrong so I can move it back to more stock code.

The Code

There are quite a few files to this one but most of them are other packages, as always there is a link to the full code at the end of the post.  There are 2 thord party packages I used to make this happen.  One is the SugarCRM CssLoader available here and the jQuery plugin DataTables available here.  If you dowload DataTables I had it include the stock CSS in the download an dnothing else.  Both of these are going to need a JSGroupings extension described here and a QR&R and a Rebuild JS Groupings to work.

When I copied DataTables into my custom/ directory I set it up with this above file structure.  The CssLoader goes in custom/include/javascript/sugar7 but that comes with a module loader of its own if you are coding this yourself.

As for the code I wrote, it consists of a Dashlet and an API Endpoint.  The Dashlets in SugarCRM 7 consists of 3 files generally.  A PHP meta file, a JS Controller and a Handlebars template.

rcReports.php

First we look at the PHP Meta script.

This file adds the Dashlet to SugarCRM and defines its configuration settings.  In my file we have 2 sections.  The 'Dashlet' section contains the information needed to add the Dashlet to SugarCRM.  This includes the Label and Description for the 'Add a Dashlet' page and arrays for the configuration and preview panels.  The 'Preview' array contains the settings that would allow the dashlet to render in the preview panel. For example I could put a value in there for the Report's ID and a limit like this

and we would see this when we clicked on the preview button on the 'Add a Dashlet' page.

I chose not to fill in this array because I don't know the IDs of the reports on your system and the preview, while OK, doesnt use the CSS or custom JavaScript (DataTables) and hence looks a little rough.  You can see the first column which is noamally hidden by DataTables that contains the URL data for the row links.  There are many Dashlets in SugarCRM that leave this array blank, if it is blank it just shows a blank square in the preview panel.  I would think a better option woold have been to print 'Preview Not Available' but maybe I can get that sorted out in the next version.

The config section outlines the defaults that will populate your configuration screen.  I have left this one blank as well as my configuration screen already defaults properly.

That last array 'dashlet_config_panels' is the definition of my configuration screen.  It defines the fields, their type and any options needed to render them  As far as I can see they follow the standards that vardefs as far as types go.  The name of this array is completly up to you, it is enabled in the controller in the initDashlet() function.  There you will find the this.meta.panels setting pointing to this array.

rcReports.js

Next we look at the controller

This file is the heart of the dashlet.  As the name 'Controller' implies it controls everything the dashlet does.  At the top you will see that it imports the Dashlet plugin and the CssLoader plugin.  The Dashlet plugin provides all the functionality of a standard dashlet, you can add to that functionality or override it in the rest of the file.  The CssLoader plugin allow us to dynamically add CSS to our dashlet.  You'll see the next few lines define the CSS file we want to include.

The next two most important sections are the initialize() and initDashlet() functions.  The difference between these two is that the initialize() function runs when the JS is initialized (once) and the initDashlet() runs every time the dashlet is drawn on or added to the screen.   Then there is the loadData() function.  This is run when the dashlet needs to fill in content.   Those 3 are the basic functions in all dashlets.  The other functions in there are custom ones written just for this dashlet.  The getAllSavedReports() and the parseAllSavedReports() fill in the report name drop-down on the configuration screen.  The handleTable() function puts the data returned from the API on the screen via the handlebars template and instanciates the DataTable plugin.  You will also see in the handleTables() function where I hide the first column in the table as thats where I put the link data for each record and then I add a listener to the <tr> element so when you click on a row it takes you to the record represnted by that row.

rcReports.hbs

Lastly the handlebars template. Not really much to say here, its a standard HTML table in handlebars format.  The classes I added to the table are from the DataTables CSS and made the final look of the table very much like a stock SugarCRM dashlet.  I would have rather used a regulat listview but I could not find a way to format the data from the API to allow it to render.  It would either be garbled, missing data or blank.  For this dashlet, the handlebars template just draws a standard HTML table on the dashlet and then the DataTables plugin converts it in place during the handleTable() call in rcReports.js.  That function renders the table and then runs DataTable on it.

ReportsDashlets2Api.php

This is the API endpoint that provides both the names of the reports for the Report picker drop down and the data from the report chosen. This file follows the standard custom API enpoint covered here.  The function that turns the report data into an array also calculates the column widths as the AutoWidth code in DataTables didnt seem to do the job correctly.  I just count up the number of characters in the longest string in each column and then use that to calculate a percent.  That percentage is passed on to the handlebars template and added to each of the <th> tags.

The files

The files for this project are available on GitHub here.

If you have any questions on this or anything email me at ken.brill@gmail.com and I will try to help.