1

This is my SQL query result

enter image description here

I am trying to build column chart in C# web form by grouping rooms, as per hotel they belong to, like below chart

enter image description here

Above query result is stored in DataTable dt. Below is rest of code

List<string> rooms = (from p in dt.AsEnumerable() select p.Field<string>("Room")).Distinct().ToList();

string[] x = new string[dt.Rows.Count];
double[] y = new double[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
    string room = dt.Rows[i][1].ToString();

    //Get the Country for each Room.
   x[i] = dt.AsEnumerable().
               Where(row => row.Field<string>("Room") == room.ToString()).
           Select(row => row.Field<string>("Country")).FirstOrDefault();


    //Get the Busy % for each Room.
    y[i] = dt.AsEnumerable().
                Where(row => row.Field<string>("Room") == room.ToString())
                  .Select(m => m.Field<double>("Busy")).FirstOrDefault();

Chart1.Series.Add(new System.Web.UI.DataVisualization.Charting.Series(room));
Chart1.Series[room].IsValueShownAsLabel = true;
Chart1.Series[room].ChartType = System.Web.UI.DataVisualization.Charting.SeriesChartType.Column;
Chart1.Series[room].Points.DataBindXY(x, y);
Array.Clear(x, 0, x.Length);
Array.Clear(y, 0, y.Length);
}
Chart1.Legends[0].Enabled = true;

Upon compilation, i can see below chart. How can i group room series belong to same hotel. Any help on this is high appreciated.

enter image description here

Update: Thanks TAW for helping with original post. I now see issue with Series alignment. Data table now has 4 columns Hotel, Room, Utilization, ID. Rooms belong to same hotel will have same ID and that's how i'm grouping rooms on x-axis basis on ID. Thereafter i'm replacing ID values back to Hotel Name as X-Axis label. But Series with different value is being shifted to +1 or -1 (Logical representation for alignment) each time new series is added to chart. enter image description here

Rajan Gupta
  • 31
  • 2
  • 6
  • 1
    Since you are adding the x-values as strings they actually lose their values, all go to zero and therefore can't be grouped. Add them as numbers and add the names as labels! Do not let the automatic labels fool you! Look at the values in the debugger! – TaW Aug 29 '20 at 22:45
  • 1
    Thanks for tip. I passed number instead of string and it seems to be fine now. Last thing, how can i change/replace number back to Names (String)? If you can enlighten me, it would be of great help! – Rajan Gupta Aug 30 '20 at 00:25
  • 1
    I was able to change labels using Chart1.ChartAreas[0].AxisX.CustomLabels.Add(min, max, "Custom Value"); – Rajan Gupta Aug 30 '20 at 01:07
  • 1
    Yes you can either use CustomLabels or set each or some DataPoint.Labels as needed. Note that to create a 2nd etc row you can use the CustomLabel.RowIndex – TaW Aug 30 '20 at 06:35
  • Series are not aligned to data points. It shifts to +1, -1 value each time new series is added to chart. For example, value of Hotel A is 10 and since it is first series it is correctly aligned where as series Hotel B valued 20 is shifted -1 to 19 and Hotel C with Value 30 is shifted +1 to 31. It will be issue when Hotel have multiple rooms. How can i fix it? – Rajan Gupta Sep 04 '20 at 14:08
  • A group of columns from several series will always be centered around their common value. This is by design so they don't cover each other... Given your x-values do the numbers make any sense anyway? – TaW Sep 04 '20 at 14:14
  • Number don't have any importance here other than grouping. Data table now has 4 columns Hotel, Room, Utilization, ID. Rooms belong to same hotel will have same ID and that's how i'm grouping rooms on x-axis basis on ID. Thereafter i'm replacing ID values back to Hotel Name as X-Axis label.That's okay if series with common value will be centered around their value. But series with different value is being shifted to +1 or -1 each time new series is added to chart. – Rajan Gupta Sep 04 '20 at 14:25
  • Maybe it will get clearer if you add an image or two to the post of how it looks like now. But in general, yes, like I wrote: adding Series will add columns to all groups and therefore shift all groups. – TaW Sep 04 '20 at 14:32
  • 1
    Added image in original post. Thanks – Rajan Gupta Sep 04 '20 at 14:37
  • Well, while this __looks__ a bit jagged, it is actually just right, I'd say. You simply have too few datapoints to make it look more complete. - But looking again at your categories I don't think they really lend themselves to this kind of setup; Instead I think I would use only one Series and use just DataPoint.Color the differentiate between the Hotels. When you now make sure to add all data in order, I guess it would look better. – TaW Sep 04 '20 at 15:28

0 Answers0