This is a migrated thread and some comments may be shown as answers.

HTML Static Months - SQLDataSource

3 Answers 59 Views
Chart (HTML5)
This is a migrated thread and some comments may be shown as answers.
Skip
Top achievements
Rank 1
Skip asked on 19 Feb 2015, 06:57 PM
I've got a SQLDatasource that pulls back a qty on a monthly basis.  Currently the XAXIS is the Month.  However, If I hard code the month, the data does not correctly line up.  If I don't hard code the Items the data is correct; however the month's are not in order.

I've posted two images.  The first one is how the data looks through SQL.  There are only 4 months, I plan on making a query parameter based on a RadComboBox later.  The second is an image of the two Chart's below, I've been toying with.  I only need one of them to work properly, just included both to show what I've tried thus far.

I really need help displaying all 12 months in order on the left hand side of the chart, and linking the correct value to be displayed..  

01.<table style="width: 100%;">
02.    <tr>
03.        <td style="width: 50%;">
04.            <telerik:RadHtmlChart ID="HardCodedMonthNames" runat="server" DataSourceID="SqlDataSourceNearMissByMonth">
05.                <ChartTitle Text="Near Misses By Month">
06.                </ChartTitle>
07.                <Legend>
08.                    <Appearance Visible="False" />
09.                </Legend>
10.                <PlotArea>
11.                    <XAxis Visible="True" DataLabelsField="Month">
12.                        <Items>
13.                            <telerik:AxisItem LabelText="January" />
14.                            <telerik:AxisItem LabelText="February" />
15.                            <telerik:AxisItem LabelText="March" />
16.                            <telerik:AxisItem LabelText="April" />
17.                            <telerik:AxisItem LabelText="May" />
18.                            <telerik:AxisItem LabelText="Jun" />
19.                            <telerik:AxisItem LabelText="July" />
20.                            <telerik:AxisItem LabelText="Aug" />
21.                            <telerik:AxisItem LabelText="September" />
22.                            <telerik:AxisItem LabelText="October" />
23.                            <telerik:AxisItem LabelText="November" />
24.                            <telerik:AxisItem LabelText="December" />
25.                        </Items>
26.                    </XAxis>
27. 
28.                    <YAxis Visible="True" Name="Number of Assessments">
29.                        <TitleAppearance Position="Center" RotationAngle="0"
30.                                         Text="Near Misses" />
31.                    </YAxis>
32. 
33.                    <Series>
34.                        <telerik:BarSeries DataFieldY="Qty" Name="Month">
35.                        </telerik:BarSeries>
36.                    </Series>
37.                </PlotArea>
38.            </telerik:RadHtmlChart>
39.        </td>
40.        <td style="width: 50%;">
41.            <telerik:RadHtmlChart ID="QueryBasedMonthNames" runat="server" DataSourceID="SqlDataSourceNearMissByMonth">
42.                <ChartTitle Text="Near Misses By Month">
43.                </ChartTitle>
44.                <Legend>
45.                    <Appearance Visible="False" />
46.                </Legend>
47.                <PlotArea>
48.                    <XAxis Visible="True" DataLabelsField="MonthName">
49. 
50.                    </XAxis>
51. 
52.                    <YAxis Visible="True" Name="Number of Assessments">
53.                        <TitleAppearance Position="Center" RotationAngle="0"
54.                                         Text="Near Misses" />
55.                    </YAxis>
56. 
57.                    <Series>
58.                        <telerik:BarSeries DataFieldY="Qty" Name="Month">
59.                        </telerik:BarSeries>
60.                    </Series>
61.                </PlotArea>
62.            </telerik:RadHtmlChart>
63. 
64.        </td>
65.    </tr>
66.</table>
67.<asp:SqlDataSource ID="SqlDataSourceNearMissByMonth" runat="server" ConnectionString="<%$ ConnectionStrings:OSHAConnectionString %>" SelectCommand="
68.                   select 'ABC COMPANY' AS LOCATION,
69.                   MONTH,
70.                   Year,
71.                   DateName( month , DateAdd( month , [MONTH] , 0 ) - 1 ) as MonthName,
72.                   COUNT(ID) as Qty
73.                   from aaa_v_ReportingData
74.                   where IncidentType = 'Near Miss'
75.                   and incidentdate is not null
76.                   group by Location,
77.                   Year,
78.                   MONTH,
79.                   DateName( month , DateAdd( month , [MONTH] , 0 ) - 1 )
80.                   Order by MONTH
81.                   ">
82.</asp:SqlDataSource>

3 Answers, 1 is accepted

Sort by
0
Skip
Top achievements
Rank 1
answered on 19 Feb 2015, 08:57 PM
"I plan on making a query parameter based on a RadComboBox later"  I meant to say ... plan on making the "Years" as... a query parameter based on a radcombobox later.  

Some month's simply will not have any data to display.  Such as Dec of the current year, but I will want the month name "December" displayed on the report.  Thanks in advance.
0
Danail Vasilev
Telerik team
answered on 23 Feb 2015, 11:15 AM
Hi Skip,

In order to associate each item with the corresponding x-axis categories I can suggest the following:
    - Either create programmatically series items and x-axis categories with the same quantity. In that case, however, there is an issue with adding null-able values See the FIX add declaratively/programmatically null-able series items in the RadHtmlChart feedback item for details.
    - Or data bind the chart (i.e., bind the series and x-axis simultaneously)

Regards,
Danail Vasilev
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Skip
Top achievements
Rank 1
answered on 24 Feb 2015, 09:47 PM
Thanks for the help.  I got it working in the following way.  Probably not the best, but it works and hopefully the idea can help someone else.



            <telerik:RadHtmlChart runat="server"  ID="RadHtmlChart1"
                                  DataSourceID="SqlDataSourceNearMissByMonth">
                <PlotArea>
                    <Series>
                        <telerik:ColumnSeries DataFieldY="Value" Name="IncidentByMonth">
                            <TooltipsAppearance Color="White" />
                        </telerik:ColumnSeries>
                    </Series>
                    <XAxis DataLabelsField="MonthName">
                        <LabelsAppearance RotationAngle="300">
                        </LabelsAppearance>
                    </XAxis>
                    <YAxis>
                        <TitleAppearance Text="Incident Count">
                        </TitleAppearance>
                    </YAxis>
                </PlotArea>
                <Legend>
                    <Appearance Visible="false">
                    </Appearance>
                </Legend>
                <ChartTitle Text="Neear Misses By Month">
                </ChartTitle>
            </telerik:RadHtmlChart>
 
<asp:SqlDataSource ID="SqlDataSourceNearMissByMonth" runat="server" ConnectionString="<%$ ConnectionStrings:OSHAConnectionString %>" SelectCommand="
                   SELECT M, MonthName, b.Value as Value
                   from (
                   SELECT 1 AS M, 'January' AS MonthName UNION ALL
                   SELECT 2 AS M, 'February' AS MonthName UNION ALL
                   SELECT 3 AS M, 'March' AS MonthName UNION ALL
                   SELECT 4 AS M, 'April' AS MonthName UNION ALL
                   SELECT 5 AS M, 'May' AS MonthName UNION ALL
                   SELECT 6 AS M, 'June' AS MonthName UNION ALL
                   SELECT 7 AS M, 'July' AS MonthName UNION ALL
                   SELECT 8 AS M, 'August' AS MonthName UNION ALL
                   SELECT 9 AS M, 'September' AS MonthName UNION ALL
                   SELECT 10 AS M, 'October' AS MonthName UNION ALL
                   SELECT 11 AS M, 'November' AS MonthName UNION ALL
                   SELECT 12 AS M, 'December' AS MonthName
                   ) a
                   left outer join (
                   select Location,
                   MONTH,
                   COUNT(ID) as Value
                   from aaa_v_ReportingData
                   where IncidentType = 'Near Miss'
                   and incidentdate is not null
                   and Location = @Location
                   and YEAR = @Year
                   group by Location,
                   MONTH
                   ) b on b.Month = a.M       
                   ">
    <SelectParameters>
        <asp:ControlParameter ControlID="RadComboBox2" Name="Year" PropertyName="SelectedValue" />
        <asp:ControlParameter ControlID="RadComboBox1" Name="Location" PropertyName="SelectedValue" />
    </SelectParameters>
</asp:SqlDataSource>
Tags
Chart (HTML5)
Asked by
Skip
Top achievements
Rank 1
Answers by
Skip
Top achievements
Rank 1
Danail Vasilev
Telerik team
Share this question
or