Incorrect information displayed on chart (X-Axis which contains dates)

[Navnit]

Hi
I'm having this issue where the information I have in my SQL database is not being reflected on the chart. 

My SQL query returns this data below:

raisedBy               counter     createdon
Risha Wadhwani     13        2010.06.14
Risha Wadhwani     19         2010.06.15
Tufail Khan              4        2010.06.15
Risha Wadhwani     13        2010.06.16

Also see screenshot 'sql.png'

This is the code that I am using to generate the Chart :

private void chart1_NeedDataSource(object sender, EventArgs e)
{
            var dataset = new DataSet();
            var adapter = new SqlDataAdapter();
            var conn =
                new SqlConnection("Initial Catalog=****;Data Source=****;User Id=****;Password=****;");
            var cmd = new SqlCommand
                          {
                              Connection = conn,
                              CommandType = CommandType.Text,
                              CommandText =
                                  @"SELECT  raisedBy,
        COUNT(id) as counter,
        createdon FROM
        (
            SELECT
                    da.name as raisedBy,
                    defects.id,
                    convert(VARCHAR, defects.created_on, 102)
                        AS
                            createdon
                        FROM
                            defects
                        LEFT JOIN
                            [user] da
                        ON
                            (defects.raised_by = da.id)
                        WHERE
                            defects.id IN(488,490,491,492,493,494,495,496,497,498,499,500,501,502,504,505,506,508,509,511,512,513,514,515,516,517,518,519,520,521,523,524,526,528,529,530,535,536,537,538,539,540,541,542,543,544,545,546,547)

        )
                        table1
        GROUP BY
                raisedBy,
                createdon
                ORDER BY createdon ASC"
                          };

            adapter.SelectCommand = cmd;
            adapter.Fill(dataset);
            DataView view = dataset.Tables[0].DefaultView;

            //var chart = sender as Chart;

            chart1.PlotArea.XAxis.Appearance.LabelAppearance.RotationAngle = -320;
            chart1.PlotArea.XAxis.Appearance.TextAppearance.TextProperties.Color = Color.BlueViolet;
            chart1.PlotArea.XAxis.DataLabelsColumn = "createdon";

            chart1.DataGroupColumn = "raisedBy";

            chart1.PlotArea.XAxis.IsZeroBased = false;
            chart1.PlotArea.XAxis.Appearance.ValueFormat = Telerik.Reporting.Charting.Styles.ChartValueFormat.ShortDate;
            chart1.PlotArea.XAxis.Appearance.CustomFormat = "MM/dd/yyyy";
            chart1.PlotArea.XAxis.LayoutMode = Telerik.Reporting.Charting.Styles.ChartAxisLayoutMode.Between;
            chart1.PlotArea.XAxis.LabelStep = 1;
            chart1.PlotArea.XAxis.MaxItemsCount = view.Count;

            foreach (DataRowView rowView in view)
            {
                var datedate = DateTime.Parse(rowView.Row.ItemArray[2].ToString());
                rowView.Row.ItemArray[2] = datedate;
            }


            if (chart1 != null) chart1.DataSource = view;
}

And finally, attached is the screenshot to see the Chart that is being rendered.

The problem is that is 'Tufail Khan' column(on the chart) is being displayed under the date '2010.06.14', instead of '2010.06.15' where it should have been, if you look at the result from the SQL result..

Any help on this would be very much appreciated.
Cheers Telerik!

Navnit 

[Steve]

Hi Navnit,

There are two ways to make the data appear correctly. The first one is to modify the data source so that it includes records for the missing values, i.e. instead of missing records there should be records with value 0 for the respective X axis positions. The other way is to create and add the chart series items programmatically as explained in the following thread: Graph for each group.

Hope these help.

Kind regards,
Steve
the Telerik team

[Navnit]

Hi Steve,

Thanks a lot for the prompt support.

I finally opted for the first option, that is, filling up the missing data with ZEROs and that worked marvellously. Now the other issue which follows is how to get rid of those unwanted 0's on the StackedBar chart as they tend to overlap with non-zero counts. See screenshot.

Thanks for your help! 

Regards,
Navnit

[Steve]

Hello Navnit,

The only way to remove the item label only for the zero values is to create the series items programmatically. Otherwise setting the LabelAppearance -> Visible = false for the serie would hide the labels for all items. If that is ok for your scenario, then you can do this.
If you however need them for the non zero values and would go the programmatic way, it would be best to use the second suggestion from my previous post, namely set the XValue property for each series item.

Best wishes,
Steve
the Telerik team

[Navnit]

Thanks for helping out Steve! Will give it a try..

Warm Regards, 
Navnit