Widget Google Chart Example - SQL Query

[Andy_Sismey]

Here is a simple example of a Google Pie Chart displaying Windows 10 version, I use this to report on SQL Views UNIONS, I would download loader.js and run locally from your Server.

<%@ Page Language="C#" AutoEventWireup="true" Inherits="LS.BaseControl" %>
<%@ Import Namespace="Newtonsoft.Json" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Collections.Generic" %>

<%@ Import Namespace="LS.Enums" %>

<%@ Import Namespace="LS" %>

<% Response.CacheControl = "no-cache";%>

<% Response.AddHeader("Pragma", "no-cache"); %>

<% Response.Expires = -1; %>

<%  LS.User.Current().CheckUserWebsiteAccess(); %>

<%

List<string> rowsList = new List<string>();


var dsGChart2 = DB.ExecuteDataset("Select Count(tblAssets.AssetID) As Count,  tsysOS.OSname,  Case tblOperatingsystem.Version When '10.0.10240' Then '1507'    When '10.0.10586' Then '1511'    When '10.0.14393' Then '1607'    When '10.0.15063' Then '1703'    When '10.0.16299' Then '1709'    When '10.0.17134' Then '1803'    When '10.0.17763' Then '1809'    When '10.0.18362' Then '1903'    When '10.0.18363' Then '1909'    When '10.0.19041' Then '20H1'    When '10.0.19042' Then '20H2'    When '10.0.20241' Then '20241 - Windows Insiders in the Dev Channel'    When '6.1.7601' Then '7601'    When '6.1.7600' Then '7600'    When '6.3.9600' Then '9600'    Else '?'  End As Version,  tblOperatingsystem.Caption As Caption From tblAssets  Inner Join tblOperatingsystem On     tblAssets.AssetID = tblOperatingsystem.AssetID  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID   Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1 Group By tsysOS.OSname,tblOperatingsystem.Version,tblOperatingsystem.Caption");


if (dsGChart2.Rows.Count != 0)




    rowsList.Add("['Version','Count']");
                    
            
              foreach(DataRow row in dsGChart2.Rows)
            
                {
     rowsList.Add("['" + row["Version"] +   "', " + row["Count"] + "]");
     
                }
                String rows = String.Join(", ", rowsList);

String GoogleChart= "[" + rows + "]" ;

string GoogleChartData= GoogleChart;


{%>  
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">


google.charts.load('current', {
  callback: function () {


GoogleData = "<%=GoogleChartData%>";
GoogleData = GoogleData .trim();
            GoogleData = GoogleData .replace(/'/g, '"');
            GoogleData = jQuery.parseJSON(GoogleData);
            var data = google.visualization.arrayToDataTable(GoogleData);

    var options = {
      title: "Windows 10 Breakdown",
 is3D: true,
width: 800, 
height: 500,


    };
 
var lineChart = new google.visualization.PieChart(document.getElementById('chart_div'))
    lineChart.draw(data, options);
  },
  packages:['corechart']
});


</script>



 <div id="chart_div" style="width: 900px; height: 500px;"></div>





<%if (LS.User.Current().IsInRole(Permission.EditConfiguration)){%><div style="float:right"><a href="<%=ResolveUrl("~/")%>configuration/AssetGroups/" class="sml">config</a></div><% } %>

</div>

<%}%>


<script type="text/javascript">

    $('#WTitle<%=TabControlID %>', window.top.document).text("Windows 10 Version" );

</script>