raypierce

Hi Jasmine,

I do not want the prior year data (2024) hence the query YEAR(NOW()). At the new year there will be no data. This is as expected. As I mentioned in my original post, I use Google charts on other dashboards to illustrate only the current years data. Those Google charts function by displaying “No Data” at the new year rather than rendering the page as disabled and showing a critical error message. You have told me in the past that yo are also using Google charts for your plugin. You should be able to get the same functionality. Since this is a WordPress site I am unable to implement the Google charts without using a plugin.

I have pasted the code for a pie chart I use on another dashboard using Google Charts. This chart and many others I have work fine at the new year, displaying “No Data” until the database is populated for the new year. I am not showing the database connection for security reason as you no doubt understand.

I hope you can help me. It is an issue that would require me to find another way to get the chart functioning at the new year. I am about to retire and don’t want to leave my company with a dashboard that breaks on the next new year.

I am using a free chart plugin on my wordpress sites that simply shows a zero in bar charts if there is no data for the new year. Or simply a blank chart in the case of Pie charts. But the page is still accessible to view other items of information. I prefer using your plugin as it is more versatile except for this issue. I must manually go into the wordpress dashboard and unpublish the affected charts in order to gain access to any page that has a Chartify chart on it.

Thank you.

    <script type="text/javascript">

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

// Draw the pie chart for Risk Rating when Charts is loaded.
      google.charts.setOnLoadCallback(drawRiskRatePieChart);

// Draw the pie chart for Risk Rating2 when Charts is loaded.
      google.charts.setOnLoadCallback(drawRiskRate2Chart);
	 
// Callback that draws the pie chart for RiskRate1.
      function drawRiskRatePieChart() {
		  
		  

// Create the data table for RiskRate1.
        var data = google.visualization.arrayToDataTable([
          ['risk', 'count'],
         <?php
		   // read all rows from database table
            $sql = "SELECT project, data, rank FROM risk_data WHERE project = $project_id ORDER by rank ASC";
            $result = $connection->query($sql);
            //read data of each row
            WHILE($row = $result->fetch_assoc()){ 
				echo  $row["data"] ;               
            }
		  ?>
        ]);

// Set options for RiskRate1 pie chart.
        var options = {
          title: 'Audit Deficiency Found',
		  colors: ['#31B404', '#f9ab32', '#d332f9', '#FF0000'],
		  backgroundColor: 'transparent',
		  width: 450,
		  height: 350,
		  pieHole: 0.3,
		  is3D: true
        };

// Instantiate and draw the chart for RiskRate1.
        var chart = new google.visualization.PieChart(document.getElementById('risk_rate'));

        chart.draw(data, options);
      }
	  
    </script>

Below is the mySQL view query that pulls the data from the database for the chart above.

SELECT
    haskellc_diary2.audit.project AS project,
    haskellc_diary2.audit.findings AS rank,
    haskellc_diary2.audit.date AS date,
    CONCAT(
        '[',
        '\'',
        haskellc_diary2.audit.risk,
        '\'',
        ',',
        ' ',
        COUNT(haskellc_diary2.audit.project),
        ']',
        ','
    ) AS data,
    COUNT(0) AS sort
FROM
    haskellc_diary2.audit
WHERE
    (
        YEAR(haskellc_diary2.audit.date) = YEAR(NOW()))
    GROUP BY
        haskellc_diary2.audit.risk,
        haskellc_diary2.audit.project
    ORDER BY
        haskellc_diary2.audit.risk