Make Gadgets Work

Posts tagged "Date Function"

I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere. Problem Statement: Find out the age of an incident in working minutes, given the following: Time and Date of when an incident was logged Time and date of when the same incident was closed Opening time of the site for which the incident was logged Closing Time of the site for which incident was logged Country of the site for which incident has been logged Assumption: It is assumed that opening and closing times are same on all working days and that all the sites are closed on holidays and weekends Function should take all the above five "given" as parameter and then calculate age of the incident. Example of problem Let's say an incident was logged on "Friday 10th June 2016 at 12:00" for a site in the "UK" which opens between 09:00 to 16:00. This incident was then closed on "Tuesday 14th June 2016 at 14:00". For the above incident function should calculate the age as 960 minutes = 16 hours = [4 hours on Friday (12:00 to 16:00) + 7 hours on Monday (09:00 to 16:00) + 5 hours on Tuesday (09:00 to 14:00)] Pre-requisites: A holiday table for the "Country" of the site for which incident is being provided should already be created on the database with the name holiday_table. Created using code below: CREATE TABLE `holiday_table` ( `holiday_table_id` INT(11) NOT NULL, `holiday_date` DATETIME NULL DEFAULT NULL, `week_day` VARCHAR(12) NULL DEFAULT NULL, `holiday_name` VARCHAR(45) NULL DEFAULT NULL, `Country_codes` VARCHAR(45) NOT NULL DEFAULT 'ALL', PRIMARY KEY (`holiday_table_id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; Sample sql-insert Data for holiday_table: INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (2, '2016-03-25 00:00:00', 'Friday', 'Good Friday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (3, '2016-03-28 00:00:00', 'Monday', 'Easter Monday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (4, '2016-05-02 00:00:00', 'Monday', 'Early May bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (5, '2016-05-30 00:00:00', 'Monday', 'Spring bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (6, '2016-08-29 00:00:00', 'Monday', 'Summer bank holiday', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (7, '2016-12-26 00:00:00', 'Monday', 'Boxing Day', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (8, '2016-12-27 00:00:00', 'Tuesday', 'Christmas Day (substitute day)', 'ALL'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (9, '2016-01-01 00:00:00', 'Friday', 'New Year’s Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (10, '2016-02-08 00:00:00', 'Monday', 'Chinese New Year', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (11, '2016-02-09 00:00:00', 'Tuesday', 'Chinese New Year', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (12, '2016-05-21 00:00:00', 'Saturday', 'Vesak Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (13, '2016-07-06 00:00:00', 'Wednesday', 'Hari Raya Puasa', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (14, '2016-08-09 00:00:00', 'Tuesday', 'National Day', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (15, '2016-09-12 00:00:00', 'Monday', 'Hari Raya Haji', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (16, '2016-10-29 00:00:00', 'Saturday', 'Deepavali', 'SG'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (17, '2016-01-01 00:00:00', 'Friday', 'New Year’s Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (18, '2016-01-26 00:00:00', 'Tuesday', 'Republic Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (19, '2016-07-06 00:00:00', 'Wednesday', 'Idul Fitr', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (20, '2016-08-15 00:00:00', 'Monday', 'Independence Day', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (21, '2016-10-11 00:00:00', 'Tuesday', 'Dussehra/Durga Puja', 'IN'); INSERT INTO `holiday_table` (`holiday_table_id`, `holiday_date`, `week_day`, `holiday_name`, `Country_codes`) VALUES (22, '2016-10-31 00:00:00', 'Monday', 'Diwali Privilege Holiday/Gobardhan Puja', 'IN'); Function code and explanation CREATE DEFINER=`root`@`localhost` FUNCTION `workday_time_diff_holiday_table`( `param_country` varchar(10), `assigneddatetime` varchar(20), `closeddatetime` varchar(20), `starttime` varchar(20), `endtime` varchar(20) ) RETURNS int(11) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Set @starttime = starttime; Set @endtime = endtime; Select time_to_sec(timediff(@endtime,@starttime))/3600 into @maxhoursaday; Set @assigneddate = assigneddatetime; Set @closeddate = closeddatetime; Set @timecount = 0; Set @timevar1 = @assigneddate; Set @nextdate = @assigneddate; Set @timevar2 = null; Set @param_country = param_country; ############ /*Check if the assigned time was before the starttime or closed time was after the endtime provided*/ ############# Set @checkstart = null; Set @checkend = null; Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@closeddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@assigneddate > @checkstart) then if (@closeddate<@checkend) then Set @assigneddate = @assigneddate; Set @closeddate = @closeddate; else Set @assigneddate = @assigneddate; Set @closeddate = @checkend; end if; else if (@closeddate<@checkend) then SET @assigneddate = @checkstart; Set @closeddate = @closeddate; else SET @assigneddate = @checkstart; Set @closeddate = @checkend; end if; end if; #################### /*After above check, the assigneddate and closeddate variables will be reset in accordance with the checks.*/ #################################### SELECT DATEDIFF(@closeddate, @assigneddate) INTO @fixcount; # check the difference between assigned date and closed date. Set @count = @fixcount; # allocate the difference between closed date and assigned date to a counter If @fixcount > 0 then # true if line 57 resulted in more than 1 then run the while loop on next line while @count>=0 do # run the while loop until the count which is right now difference between closed and assigned becomes zero select weekday(@nextdate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday /*Check if the date stored in nextdate (which is assigneddate on first run of while loop and closeddate on last run) is a holiday and set the holiday flag*/ Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in nextdate variable is neither weekend nor a holiday if (@count = @fixcount) then #Check if it is first run.ie. if nextdate is assigneddate Set @timevar1 = @assigneddate; #assign assigndate to variable timevar1 SELECT CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@endtime) INTO @timevar2;#get site closing time on assigned date and store it on to timevar2 elseif (@count = 0) then #if the date in nextdate variable is closeddate then do the following otherwise proceed Select concat(substring_index(@closeddate,' ',1),' ',@starttime) into @timevar1; # Set @timevar2 = @closeddate; else Select concat(@nextdate,' ',@starttime) into @timevar1; SELECT CONCAT(@nextdate, ' ', @endtime) INTO @timevar2; end if; SELECT LEAST(Greatest(((TIME_TO_SEC(TIMEDIFF(@timevar2, @timevar1))) / 3600),0),@maxhoursaday) INTO @timecounttemp; Set @timecount = @timecounttemp + @timecount; end if; Set @timevar1 = @nextdate; SELECT ADDDATE(SUBSTRING_INDEX(@timevar1, ' ', 1),1) INTO @nextdate; Set @count = @count - 1; end while; else #check if the assigned date / closed date is a holiday or weekend select weekday(@assigneddate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@assigneddate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; #Check if the date stored in assigneddate is a holiday and set the holiday flag if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in assigneddate variable is neither weekend nor a holiday SELECT Least(Greatest(((TIME_TO_SEC(TIMEDIFF(@closeddate, @assigneddate))) / 3600),0),@maxhoursaday) INTO @timecount; else Set @timecount = 0; end if; end if; RETURN @timecount*60; END Calling the function Function will expect 5 parameters and with specific format as explained below: param_country - This is the country code as specified in holiday table assigneddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2016-06-10 12:00:00 closeddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2016-06-14 14:00:00 starttime - This must be in the format %H:%i. So for our example it will be 09:00 endtime - This must be in the format %H:%i. So for our example it will be 16:00 The call for this function will be as below: ## To get number of minutes Select `WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00'); ## To get number of hours Select `WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00')/60; ## To get in number of working days Select (`WORKDAY_TIME_DIFF_HOLIDAY_TABLE`('UK','2016-06-10 12:00:00','2016-06-14 14:00:00','09:00','16:00')/60)/(substring_index('16:00',':',1)-substring_index('09:00',':',1)); Complete Flowchart This chart was made using PlantText Chart Code @startuml scale 0.8 'skinparam monochrome true skinparam handwritten true skinparam shadowing false 'title Workday Time Difference Function start :Get the parameter values from calling function - assigneddatetime in 'YYYY-MM-DD hh:mm' format, - closeddatetime in 'YYYY-MM-DD hh:mm' format, - starttime in 'hh:mm' format, - endtime in 'hh:mm' format; :Set @starttime = starttime Set @endtime = endtime Set @assigneddate = assigneddatetime Set @closeddate = closeddatetime Set @timecount = 0 Set @timevar1 = @assigneddate Set @nextdate = @assigneddate Set @timevar2 = null; :Select time_to_sec(timediff (@endtime,@starttime))/3600 into @maxhoursaday; note left #TIMEDIFF calculates difference between two times #Time_to_sec function converts data into seconds #Dividing by 3600 converts the time into hours end note :Set @checkstart = null Set @checkend = null; :Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@closeddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@assigneddate > @checkstart) then (yes) if (@closeddate0?) then (yes) while (count>=0?) is (true) :calculate weekday for nextdate select weekday(@nextdate) into @weekday; :Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; note left Set the holiday flag to anything other than zero if the value in nextdate for this loop is a holiday end note if ( @weekday0 into @holidayflag; note left Set the holiday flag to anything other than zero if the value in nextdate for this loop is a holiday end note if ( @weekday

MySQL function to calculate elapsed working time

I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere. Problem Statement: Find out the age of an incident in working minutes, given the
My requirement was to get the data in a format that google chart can use to draw the chart I want. Now gogle chart accepts data in json format where all column names separated with comma are in first square bracket set followed by values in rest of the square bracket sets and each square bracket set is separated by comma as well. Having searched on good old google, there did not appear to be any quick way of doing it without getting hands dirty with likes of php and as I was to plug this into a BIRT report where a simple html would do the trick, I really just needed the data-set to be returned in format that google-chart understood. I figured it can be easily done using a MySQL stored procedure and can be a repeatable process which resulted in creation of stored procedure presented below. The code CREATE DEFINER=`root`@`localhost` PROCEDURE `json_builder_multiple_string`(IN `var1` varchar(10000), IN `tab_name` text, IN `int_col_as_str` int) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN /* - This procedure will take columns and tablename as parameter. - Third parameter is to tell the procedure how many columns are to be returned as string - surrounded by quotes - Third parameter must always be less than the total columns being requested. - Rest of the columns must have numerical values as they won't be surrounded with quotes - Procedure returns 0 for each null value. ​ SAMPLE PROCEDURE CALL CALL `json_builder_multiple_string`('Year,Month,Region,Sales,Expenses', 'myjsonexample',4); ​ SAMPLE QUERY GENERATED: SELECT CONCAT('[\'Year\',\'Month\',\'Region\',\'Sales\',\'Expenses\'],', GROUP_CONCAT('[', CONCAT_WS(',', CONCAT('\'', IFNULL(`Year`, 0), '\',\'', IFNULL(`Month`, 0), '\',\'', IFNULL(`Region`, 0), '\',\'', IFNULL(`Sales`, 0), '\''), IFNULL(`Expenses`, 0)) SEPARATOR '],'), ']') AS data_set FROM myjsonexample ​ SAMPLE OUTPUT: ['Year','Month','Region','Sales','Expenses'],['2004','JAN','NW','1000',400],['2005','Feb','SW','1170',460],['2006','Mar','NE','2000',1210],['2007','Apr','SE','650',540],['2008','May','EC','0',0] ​ SAMPLE TABLE USED: Drop Table if exists `myjsonexample`; CREATE TABLE `myjsonexample` ( `Year` int(11) NOT NULL, `Month` varchar(25), `Region` varchar(25), `Sales` int(11) , `Expenses` int(11) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `myjsonexample` (`Year`, `Month`, `Region`, `Sales`, `Expenses`) VALUES (2004,'JAN','NW',1000,400), (2005,'Feb','SW',1170,460), (2006,'Mar','NE',2000,1210), (2007,'Apr','SE',650,540), (2008,'May','EC',null,null); */ SET SESSION group_concat_max_len = 1000000; Set @stmt1 = null; select concat('Select concat(\'',concat('[\\\'',replace(replace(var1,'_',' '),',','\\\',\\\''),'\\\'],'),'\'', ', group_concat(\'[\', concat_ws(\',\', CONCAT(\'\\\'\', ifnull(`',replace(substring_index(var1,',',int_col_as_str),',','`,0),\'\\\',\\\'\',ifnull(`'), '`,0), \'\\\'\'),', (concat('ifnull(`', replace( SUBSTRING(var1,LENGTH(SUBSTRING_INDEX(var1, ',', int_col_as_str)) + 2,LENGTH(var1)), ',', '`,0),ifnull(`'), '`,0)') ) ,') SEPARATOR \'],\'),\']\') as data_set from ', tab_name) into @stmt1; Prepare stmt2 from @stmt1; Execute stmt2; END The alternative code Another variation of above stored procedure where the output is returned in an output parameter can be created simply by adding two more lines to above code after Execute stmt2 and also including the output parameter in first line. Complete code below: CREATE DEFINER=`root`@`localhost` PROCEDURE `json_builder_outparam`(IN `var1` varchar(10000), IN `tab_name` text, OUT `varout` text) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN /* - This procedure will take columns and tablename as parameter. - Third parameter is to tell the procedure how many columns are to be returned as string - surrounded by quotes - Third parameter must always be less than the total columns being requested. - Rest of the columns must have numerical values as they won't be surrounded with quotes - Procedure returns 0 for each null value. ​ SAMPLE PROCEDURE CALL CALL `json_builder_multiple_string`('Year,Month,Region,Sales,Expenses', 'myjsonexample',4); ​ SAMPLE QUERY GENERATED: SELECT CONCAT('[\'Year\',\'Month\',\'Region\',\'Sales\',\'Expenses\'],', GROUP_CONCAT('[', CONCAT_WS(',', CONCAT('\'', IFNULL(`Year`, 0), '\',\'', IFNULL(`Month`, 0), '\',\'', IFNULL(`Region`, 0), '\',\'', IFNULL(`Sales`, 0), '\''), IFNULL(`Expenses`, 0)) SEPARATOR '],'), ']') AS data_set FROM myjsonexample ​ SAMPLE OUTPUT: ['Year','Month','Region','Sales','Expenses'],['2004','JAN','NW','1000',400],['2005','Feb','SW','1170',460],['2006','Mar','NE','2000',1210],['2007','Apr','SE','650',540],['2008','May','EC','0',0] ​ SAMPLE TABLE USED: Drop Table if exists `myjsonexample`; CREATE TABLE `myjsonexample` ( `Year` int(11) NOT NULL, `Month` varchar(25), `Region` varchar(25), `Sales` int(11) , `Expenses` int(11) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `myjsonexample` (`Year`, `Month`, `Region`, `Sales`, `Expenses`) VALUES (2004,'JAN','NW',1000,400), (2005,'Feb','SW',1170,460), (2006,'Mar','NE',2000,1210), (2007,'Apr','SE',650,540), (2008,'May','EC',null,null); */ SET SESSION group_concat_max_len = 1000000; Set @stmt1 = null; select concat('Select concat(\'',concat('[\\\'',replace(replace(var1,'_',' '),',','\\\',\\\''),'\\\'],'),'\'', ', group_concat(\'[\', concat_ws(\',\', CONCAT(\'\\\'\', ifnull(`',replace(substring_index(var1,',',int_col_as_str),',','`,0),\'\\\',\\\'\',ifnull(`'), '`,0), \'\\\'\'),', (concat('ifnull(`', replace( SUBSTRING(var1,LENGTH(SUBSTRING_INDEX(var1, ',', int_col_as_str)) + 2,LENGTH(var1)), ',', '`,0),ifnull(`'), '`,0)') ) ,') SEPARATOR \'],\'),\']\') as data_set from ', tab_name) into @stmt1; Prepare stmt2 from @stmt1; Execute stmt2; Set varout = @varouttemp; Select varout; END The BIRT usage explained My sample BIRT reports are available for download HERE. Enabling the google chart on BIRT couldn't be simpler. Create a new report Add a text field, open it and change it to HTML as shown in the image below Copy the html[1] (modify as required - I used jsfiddle for checking the code and making changes) and paste in to the text field. Run Once the BIRT preview is working fine, create a dataset by calling the stored-procedure above and bind it to this text field. To bind a dataset, in "Report Design" perspective, click on the field and then in "Property Editor" select "Binding" as shown below: Then right click on the text field and click on edit and then click on the "Fx" symbol next to where field was changed to HTML on top of the window. This will open javascript editor. On javascript editor select the static dataset and replace it by selecting the dataset like so: That should change the HTML to dynamically get the data from json builder. <html> <head> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.charts.load('current', { 'packages': ['geochart', 'corechart', 'table', 'controls'] }); google.charts.setOnLoadCallback(drawRegionsMap); function drawRegionsMap() { // Create our data table. var data = google.visualization.arrayToDataTable([ ['Country', 'Period', 'RMPV', 'TARGET', 'Average'], ['Germany', 'Nov-2015', 75, 120, 97.5], ['Germany', 'Dec-2015', 55, 65, 60], ['Canada', 'Nov-2015', 200, 85,42.5], ['Canada', 'Dec-2015', 55, 65, 60], ['IN', 'Nov-2015', 328, 30, 179], ['IN', 'Dec-2015', 55, 65, 60], ['France', 'Dec-2015', 30, 100, 65], ['France', 'Nov-2015', 75, 120, 97.5], ['Brazil', 'Dec-2015', 50, 100, 75], ['Brazil', 'Nov-2015', 200, 85, 142.5], ['United States', 'Dec-2015', 55, 65, 60], ['United States', 'Nov-2015', 328, 30, 179] ]); var data1 = google.visualization.data.group(data,[0], [{'column': 4, 'aggregation': google.visualization.data.avg, 'type': 'number'}] ); var options = { colorAxis: { // values: [0,30,90,150], colors: ['#F40EF4','#FCFC2A','#7BFC2A', '#FC4A2A'] }, backgroundColor: '#F1F7FD', datalessRegionColor: '#8B8E91', defaultColor: '#f5f5f5' }; var chart = new google.visualization.GeoChart(document.getElementById('regions_div')); function selectHandler() { var selectedItem = chart.getSelection()[0]; if (selectedItem) { var message = data1.getValue(selectedItem.row, 0); // alert(message) //if(message=='Canada'){ var display_value = [message]; //}; }; /* Start of testing*/ // Create a dashboard. var dashboard = new google.visualization.Dashboard( document.getElementById('dashboard_div')); // Create a range slider, passing some options var donutRangeSlider = new google.visualization.ControlWrapper({ 'controlType': 'NumberRangeFilter', 'containerId': 'filter_div', 'options': { //'filterColumnLabel': 'M602' 'filterColumnIndex': [2] } }); // Create a range slider, passing some options var donutRangeSlider1 = new google.visualization.ControlWrapper({ 'controlType': 'NumberRangeFilter', 'containerId': 'filter_div1', 'options': { //'filterColumnLabel': 'M525' 'filterColumnIndex': [3] } }); var categoryPicker = new google.visualization.ControlWrapper({ controlType: 'CategoryFilter', containerId: 'control3', state: { selectedValues: display_value }, options: { //filterColumnLabel: 'Location Type', filterColumnIndex: [0], ui: { labelStacking: 'vertical', allowTyping: false, allowMultiple: false, allowNone: false } } }); // Create a column chart, passing some options var ColumnChart = new google.visualization.ChartWrapper({ 'chartType': 'ColumnChart', 'containerId': 'chart1', 'options': { 'width': '900px', 'legend': 'bottom', 'vAxes': { 0: { title: 'Average Utilisation/Target Utilisation' }, 1: { title: 'RMPV Utilisation' } }, 'series': { 1: { targetAxisIndex: 1 } } }, view: { columns: [1, 2, 3, 4] } }); // Create a table chart, passing some options var tableChart = new google.visualization.ChartWrapper({ 'chartType': 'Table', 'containerId': 'chart2', }); var pie = new google.visualization.ChartWrapper({ chartType: 'PieChart', containerId: 'chart3', options: { legend: 'bottom', title: 'Average Utilisation per month', pieSliceText: 'value' }, // Instruct the piechart to use colums 0 (Name) and 3 (Donuts Eaten) // from the 'data' DataTable. view: { columns: [1, 4] } }); // Establish dependencies, declaring that 'filter' drives 'pieChart', // so that the pie chart will only display entries that are let through // given the chosen slider range. dashboard.bind([donutRangeSlider, donutRangeSlider1, categoryPicker], [ColumnChart, tableChart, pie]); // Draw the dashboard. dashboard.draw(data); //redraw the main chart so selection doesn't retun empty chart.draw(data1, options); // End of testing*/ }; google.visualization.events.addListener(chart, 'select', selectHandler); chart.draw(data1, options); } </script> </head> <body> <div id="regions_div" style="width: 900px; height: 500px;"></div> <!--Div that will hold the dashboard--> <div id="dashboard_div"> <!--Divs that will hold each control and chart--> <table> <tr> <td> <div id="control3" align="center"></div> </td> </tr> <tr> <td> <div id="filter_div"></div> </td> <td> <div id="filter_div1"></div> </td> </tr> </table> <div id="chart1"></div> <div id="chart3"></div> <div id="chart2" align="center"></div> </div> </body> </html> HTML to be pasted in text field: ↩︎

MySQL Stored Procedure to return JSON for google charts on BIRT

My requirement was to get the data in a format that google chart can use to draw the chart I want. Now gogle chart accepts data in json format where all column names separated with comma are in first square bracket set followed by values in rest of the square