ASE Home Page Products Download Purchase Support About ASE
ChartDirector Support
 Forum HomeForum Home   SearchSearch

Message ListMessage List     Post MessagePost Message

  Multi bar chart with database
Posted by Kevin on Mar-28-2012 21:59
| Reply | Monitor
Hi,

I have a sql query which has the following result (this is putted in a DBTable:

Amount             Name                Type
1                      Asse                 Incident
3                      Ninove              Incident
3                      Aalst                 Request
1                      Ninove              Information


Now I try this to split the data into diffrent arrays:
            int x = tableType1.getCol(0).Length;

            double[] data0 = new double[x];
            double[] data1 = new double[x];
            double[] data2 = new double[x];

            for (int i = 0; i < tableType1.getCol(0).Length; i++)
            {

                if (tableType1.getColAsString(2)[i] == "Incident")
                    data0.SetValue(tableType1.getCol(0)[i], i);

                if (tableType1.getColAsString(2)[i] == "Request")
                    data1.SetValue(tableType1.getCol(0)[i], i);

                if (tableType1.getColAsString(2)[i] == "Information")
                    data2.SetValue(tableType1.getCol(0)[i], i);

            }
            string[] labels = tableType1.getColAsString(1);


but i get only the incidents of Ninove (only 1 bar instead of 2)



How cvan I get this to work?


Thanks in advance,
Kevin

  Re: Multi bar chart with database
Posted by Kevin on Mar-28-2012 22:36
| Reply | Monitor
Update: all the bars are on the chart but they aren't grouped by name. Each bar has his own label --> duplixate labels.
Here is my code:

            string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["DolphinTest"].ConnectionString;
            SqlConnection dbconn = new System.Data.SqlClient.SqlConnection(connectionstring);

            //incident
            dbconn.Open();
            string SQLType1 = "SELECT COUNT(DISTINCT t.TicketID),ta.Name, tt.Name FROM Tickets t INNER JOIN TicketAssignees ta ON t.AssignedToID = ta.TicketAssigneeID INNER JOIN TicketHistory th ON t.TicketID = th.TicketID INNER JOIN TicketTypes tt ON t.TypeID = tt.TicketTypeID WHERE t.Status IN(1,2,3,5) GROUP BY ta.Name,tt.Name HAVING COUNT( th.TicketHistoryID) > 1";
            System.Data.IDbCommand sqlCmdType1 = dbconn.CreateCommand();
            sqlCmdType1.CommandText = SQLType1;
            DBTable tableType1 = new DBTable(sqlCmdType1.ExecuteReader());
            dbconn.Close();

            int x = tableType1.getCol(0).Length;

            double[] data0 = new double[x];
            double[] data1 = new double[x];
            double[] data2 = new double[x];

            for (int i = 0; i < tableType1.getCol(0).Length; i++)
            {
                if (tableType1.getColAsString(2)[i] == "Incident")
                    data0.SetValue(tableType1.getCol(0)[i], i);

                if (tableType1.getColAsString(2)[i] == "Request")
                    data1.SetValue(tableType1.getCol(0)[i], i);

                if (tableType1.getColAsString(2)[i] == "Information")
                    data2.SetValue(tableType1.getCol(0)[i], i);
            }



            string[] labels = tableType1.getColAsString(1);

            // Create a XYChart object of size 580 x 280 pixels
            XYChart c = new XYChart(580, 280);

            // Add a title to the chart using 14 pts Arial Bold Italic font
            c.addTitle("Product Revenue For Last 3 Years", "Arial Bold Italic", 14);

            // Set the plot area at (50, 50) and of size 500 x 200. Use two alternative
            // background colors (f8f8f8 and ffffff)
            c.setPlotArea(50, 50, 500, 200, 0xf8f8f8, 0xffffff);

            // Add a legend box at (50, 25) using horizontal layout. Use 8pts Arial as font,
            // with transparent background.
            c.addLegend(50, 25, false, "Arial", 8).setBackground(Chart.Transparent);

            // Set the x axis labels
            c.xAxis().setLabels(labels);
            c.setBgImage("~/images/A2Z.JPG", 3);

            // Draw the ticks between label positions (instead of at label positions)
            //c.xAxis().setTickOffset(0.5);

            // Add a multi-bar layer with 3 data sets
            BarLayer layer = c.addBarLayer2(Chart.Side);
            layer.addDataSet(data0, 0xff8080, "Incident");
            layer.addDataSet(data1, 0x80ff80, "Request");
            layer.addDataSet(data2, 0x8080ff, "Information");

            // Set 50% overlap between bars
            //layer.setOverlapRatio();

            // Add a title to the y-axis
            c.yAxis().setTitle("Revenue (USD in millions)");

            // Output the chart
            WebChartViewer1.Image = c.makeWebImage(Chart.PNG);

            //include tool tip for the chart
            WebChartViewer1.ImageMap = c.getHTMLImageMap("", "", "title='{xLabel} Revenue on {dataSetName}: {value} millions'");

  Re: Multi bar chart with database
Posted by Peter Kwan on Mar-29-2012 00:39
| Reply | Monitor
Hi Kevin,

The labels are duplicated because your current SQL query returns duplicated labels.

One method to solve the problem is to redesign the SQL so that it returns 4 columns. The first one is the x-axis labels, which should not be duplicated. The next 3 columns are the data for Incident, Request and Information. Creating this output from your existing data structure, you may use some kind of cross tabulation query. With a suitable query, there is no need to use C# code to separate the data into arrays.

If you do not want to use cross tabulation query, you would need to write C# code to separate your data into arrays. The code must count only distinct x-axis labels (which means the array index should increment on distinct x-axis labels). An example is:

double[] col0 = tableType1.getCol(0);
string[] col1 = tableType1.getCol(1);
string[] col2 = tableType1.getCol(2);

//count distinct labels
int count = 1;
for (int i = 1; i < col1.Length; ++i)
   if (col1[i] != col1[i - 1]) ++count;

double[] data0 = new double[count];
double[] data1 = new double[count];
double[] data2 = new double[count];
string[] labels = new string[count];

int pos = 0;
for (int i = 0; i < col.Length; ++i)
{
   //update the array index only on distinct labels
   if ((i != 0) && (col1[i] != labels[pos]))
      ++pos;
   labels[pos] = col1[i];

   if (col2[i] == "Incident")
      data0[pos] = col0[i];
   else if (col2[i] == "Request")
      data1[pos] = col0[i];
   else if (col2[i] == "Information")
      data2[pos] = col0[i];
}

Hope this can help.

Regards
Peter Kwan

  Re: Multi bar chart with database
Posted by Kevin on Mar-29-2012 14:46
| Reply | Monitor
Ok, I used the c# example, thanks a lot Peter!