Skype for Business SQL Reporting - Part III - Call Qualtiy Ratings

Last week we continued our look how to report the data Skype for Business and Lync gather about call quality with their ratings survey.

This week we will continue with our web based report, and talk about the pie chart located in the top right corner of the graphics below.

That graph displays the percentage of audio quality determined by the users number of stars selected in their surveys.

The 5 options you will see are;

• Excellent
• Good
• Fair
• Poor
• and Really Poor

Bridge Web Dashboard

So to begin reporting on this first we need the data.  I use a set of chart tools from Telerik to display the data, here is the html.

 <telerik:RadHtmlChart ID="RadHtmlChart2" runat="server" Height="320px" Width="700px" Transitions="True">
                <ChartTitle Text="Call Rating Audio Quality">
                        <Appearance Align="Center" Position="Top"></Appearance>
                    </ChartTitle>
                     <PlotArea>
                        <Series>
                            <telerik:DonutSeries DataFieldY="Val" NameField="Name" ExplodeField="IsExploded">
                            </telerik:DonutSeries>
                        </Series>
                         <XAxis>
                         </XAxis>
                <YAxis>
             </YAxis>
    </PlotArea>
                
                </telerik:RadHtmlChart>

So let's take a look at the code that populates the doughnut graph.

RadHtmlChart2.DataSource = _GraphRating;

Then we can call this procedure to fill the _GraphRating object.

private void getRatingStars()
    {
        SqlConnection cn = new SqlConnection(QOEString);

        string name = "";
        double val = 0;

        int _excellent = 0;
        int _good = 0;
        int _fair = 0;
        int _poor = 0; 
        int _bad = 0;
        int _total = 0;

        try
        {
            cn.Open();
            SqlCommand mycom = new SqlCommand();
            mycom.Connection = cn;

            string t = "";

            t = query;
            t = t.Replace("#Temp", tname);
            //Response.Write(t + "\r\n");
            mycom.CommandText = t;
            mycom.ExecuteNonQuery();


            mycom.CommandText = "select Rating, count(*)/5 as cnt from " + tname + " group by Rating order by count(*) desc";

            SqlDataReader tmpreader = default(SqlDataReader);
            tmpreader = mycom.ExecuteReader();
            while (tmpreader.Read())
            {
                try
                {
                    val = Convert.ToDouble(tmpreader.GetInt32(1));
                    _total = _total + (int)val;

                    if (tmpreader.GetInt32(0).ToString() == "5")
                    {
                        _excellent = _excellent+(int)val;
                        name = "Excellent";
                    }

                    if (tmpreader.GetInt32(0).ToString() == "4")
                    {
                        _good = _good + (int)val;
                        name = "Good";
                    }

                    if (tmpreader.GetInt32(0).ToString() == "3")
                    {
                        _fair = _fair + (int)val;
                        name = "Fair";
                    }

                    if (tmpreader.GetInt32(0).ToString() == "2")
                    {
                        _poor = _poor + (int)val;
                        name = "Poor";
                    }

                    if (tmpreader.GetInt32(0).ToString() == "1")
                    {
                        _bad = _bad + (int)val;
                        name = "Really Bad";
                    }


                    _GraphRating.Add(new GraphClass(name, val, false));
                }
                catch { }

            }

            int e = (int)(100 * _excellent / _total);
            Label10.Text = e.ToString() +"%";

            int g = (int)(100 * _good / _total);
            Label12.Text = g.ToString() + "%";

            int f = (int)(100 * _fair / _total);
            Label14.Text = f.ToString() + "%";

            int p = (int)(100 * _poor / _total);
            Label16.Text = p.ToString() + "%";

            int r = (int)(100 * _bad / _total);
            Label18.Text = r.ToString() + "%";


            cn.Close();
        }
        catch (Exception ex1)
        {
            Response.Write(ex1.Message);
        }
        finally
        {
            if (cn != null)
                cn.Dispose();

        }

       
    }

From here we just bind the data to the control and it does the rest.

RadHtmlChart2.DataBind();


Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC Developer  BridgeOC
Twitter - @droutledge @ndbridge