HTML Static Months - SQLDataSource

4 posts, 0 answers
  1. Skip
    Skip avatar
    14 posts
    Member since:
    Mar 2013

    Posted 19 Feb 2015 Link to this post

    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>
  2. Skip
    Skip avatar
    14 posts
    Member since:
    Mar 2013

    Posted 19 Feb 2015 in reply to Skip Link to this post

    "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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Danail Vasilev
    Admin
    Danail Vasilev avatar
    1492 posts

    Posted 23 Feb 2015 Link to this post

    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.

     
  5. Skip
    Skip avatar
    14 posts
    Member since:
    Mar 2013

    Posted 24 Feb 2015 Link to this post

    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>
Back to Top