SQL Query Not generating report from Google Charts

Question

I am trying to display a bar chart (From Google Charts) which should display the Number of Submissions(Count) per Faculty. Within the faculty, this Count is grouped 4 Submission types (Book, Chapter, Journal and Conference). When I run the report< I get an error returning to my query. I cant figure out where I have gone wrong

@using WebMatrix.Data;
@using WebMatrix.WebData;


@{
    ViewBag.Title = "Report_2";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

                    @{
                        var db = Database.Open("RMSContext");

                        String rows = "";



                        var Query = ("SELECT Faculty.FacultyName, Submission.Type, Count(Submission.SubmissionID) AS CountOfSubmissionID FROM Submission INNER JOIN ((Faculty INNER JOIN School ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) ON School.SchoolID = Researcher.SchoolID) ON Submission.SubmissionID = ResearcherSubmission.SubmissionID GROUP BY Faculty.FacultyName, Submission.Type;");

                        var AppQuery = db.Query(Query);


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

                        foreach (var item in AppQuery)
                        {
                            rowsList.Add("['" + item.FacultyName + "', " + item.Type + "', " + item.CountOfSubmissionID + "]");
                        };
                        rows = String.Join(", ", rowsList);

                    }                                        




                    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
                    <script type="text/javascript">

                        google.load("visualization", "1.1", { packages: ["bar"] });
                        google.setOnLoadCallback(drawChart);
                        function drawChart() {

                            var data1 = google.visualization.arrayToDataTable([
                              ['FacultyName', 'Type', 'SubmissionID'],
                               @Html.Raw(rows)]);

                            var options = {
                                title: ''
                            };

                            var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

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


                    <div id="columnchart_material" style="width: 800px; height: 500px;"></div>

Below is a Mock of how my report should look:

Answer 1

That looks like a Material column chart.

You'll need to reformat your data as I have hard-coded here...

  google.load("visualization", "1.1", {packages:["bar"]});
  google.setOnLoadCallback(drawChart);
  function drawChart() {
    var data = google.visualization.arrayToDataTable([
      ['Faculty', 'Journal Articles', 'Books', 'Book Chapters', 'Conference'],
      ['CLM', 3, 5, 1, 2],
      ['EBE', 0, 1, 2, 4],
      ['Health Sciences', 3, 3, 5, 2],
      ['Humanities', 1, 4, 2, 2],
      ['Science', 1, 1, 2, 1]
    ]);

    var options = {
      chart: {
        title: 'Faculty Submissions',
        subtitle: 'Types of research provided'
      }
    };

    var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

    chart.draw(data, options);
  }