Flexible / Generic Chart Dashlet

[mvngti]

Hi,

I have created a flexible chart dashlet (called FlexibileCharDashlet) that can be used to add chart onto the home page for anything that can be extracted with a sql query.

Simply unzip the file in your sugar_root - it will not overwrite any existing files - and rebuild the Dashlets from the repair menu.
Then you can create new charts by just adding their metadata to custom/Charts/chartDefs.ext.php with two extra things in the metadata (compared to modules/Charts/chartdefs.php)

1) 'query' => contains the sql query that should run to get the chart data - it may contain smarty style variables that will get replaced by the dashlet options.
2) 'searchFields; => can define additional searchFields that will appear on the dashlet's options

There are two working samples in custom/Charts/chartDefs.ext.php in the archive. The first sample simply replicates the built in "Closed by Month" chart in sugar and the second example creates a chart called "New Opportunities by Month".

When you add the Flexible Chart dashlet it will allow you to choose one the charts you have defined in the options screen.

Right now it is just a zip file but if I get good feedback I will make it into a fully fledged project on sugarforge.

M

[mvngti]

PS: May I ask everybody to post working samples of the metadata here if you build a useful chart using this.
That way we build up an easy to use and reference set of custom charts :-)

[ckd1987]

i will test your plugin at this weekend and reporting sunday/monday.

thx 4 your work

[ckd1987]

Greetings,

since a few hours I'm woring at your custon chart, here some breaks...

 $chartDefs['Agenten-Abfrage'] = array(
                'type' => 'code',
                'id' => 'Chart_Agenten_Abfrage',
                'label' => 'Agenten Abfrage',
                'chartUnits' => 'Leads Size in 1',                
                'chartType' => 'stacked group by chart',
                'groupBy' => array( 'm', 'cc_nummer_c', ),
                'base_url'=> 
                    array(     'module' => 'Leads',
                            'action' => 'index',
                            'query' => 'true',
                            'searchFormTab' => 'advanced_search',
                         ),
                'url_params' => array( 'date_entered', ),
                'query' => 'SELECT leads_cstm.cc_nummer_c,  count(leads_cstm.lead_cnt) as m 
FROM leads,leads_cstm WHERE leads.id = leads_cstm.id_c AND 
leads.date_entered >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") 
AND leads.date_entered <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s")
AND leads.deleted=0
GROUP by leads_cstm.cc_nummer_c ORDER BY `m` DESC'
             ); 
  

if i use the generated code from sugarcrm.log in phpmyadmin, i will get the right answer something like this:

cc_nummer_c 	m
501 	62
164 	59
502 	45
147 	38
163 	34
162 	31
148 	18
128 	16
129 	16
  	2
999 	1

but if i on the Dashlets view I see something like this



I think its really easy to fix. but I dont know the right part...

[vishwasrao]

Hi ,
this seems to be great idea.
I am going through it.
will be back with feedback.

[mvngti]

You query has to return two group columns and the total column must be called "total".
That is built into the sugar code unfortunately.

Hi ,
this seems to be great idea.
I am going through it.
will be back with feedback.

[vishwasrao]

Hi,
I think this is thing i am watching for ,
I have done as u said unziped ur zip in my sugar root .
& created custom/Charts/chartdefs.ext.php

Can u tell me further steps todo so that i can create my own custom dashlet.
Thanx in advance.

[ckd1987]

In witch part i have to change this ?

[mvngti]

Hi,
Can u tell me further steps todo so that i can create my own custom dashlet.
Thanx in advance.

Test if one of the examples works and then start edting the metadata of the example step by step until you get the chart you want. Test the query in mysql to make sure the query works first!

M

[ckd1987]

okay, my first own chart was build like this code

$chartDefs['Agenten-Abfrage'] = array(
				'type' => 'code',
				'id' => 'Chart_Agenten_Abfrage',
				'label' => 'Agenten Abfrage',
				'chartUnits' => '',				
				'chartType' => 'stacked group by chart',
				'groupBy' => array( 'm', 'cc_nummer_c', ),
				'base_url'=> 
					array( 	'module' => 'Leads',
							'action' => 'index',
							'query' => 'true',
							'searchFormTab' => 'advanced_search',
						 ),
				'url_params' => array( 'date_entered', ),
				'query' => 'SELECT leads_cstm.cc_nummer_c,sum(leads_cstm.lead_cnt) as total,  count(leads_cstm.lead_cnt) as m 
FROM leads,leads_cstm WHERE leads.id = leads_cstm.id_c AND 
leads.date_entered >= DATE_FORMAT("{$fcd_date_start}", "%Y-%m-%d %H:%i:%s") 
AND leads.date_entered <= DATE_FORMAT("{$fcd_date_end}", "%Y-%m-%d %H:%i:%s")
AND leads.deleted=0
AND leads_cstm.lead_berechnen_c=0
GROUP by leads_cstm.cc_nummer_c ORDER BY `m` DESC'
			 );

THX