PivotGrid - Chart Integration

2 posts, 0 answers
  1. Andreas
    Andreas avatar
    1 posts
    Member since:
    May 2015

    Posted 27 Mar 2017 Link to this post

    Hi all,

    I´ve made a PivotGrid with Chart integration like the example on the demo page.
    My output is a little bit different as the demo. See attached file. As you can see in the picture the chart was generated twice.

    Do you know why?

    Here is my code aspx:

    <%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage/BdVNet.Master" AutoEventWireup="true" CodeBehind="Telefondaten.aspx.cs"
        Inherits="BdVNet.Content.Reporting.Telefondaten" %>
     
    <asp:Content ID="ReportTelefonContent" ContentPlaceHolderID="BdVMasterContentPlaceHolder" runat="server">
     
        <telerik:RadAjaxManagerProxy ID="ReportTelefonAjaxManagerProxy" runat="server">
            <AjaxSettings>
                <telerik:AjaxSetting AjaxControlID="ReportTelefonPivotGrid">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="ReportTelefonPivotGrid" />
                        <telerik:AjaxUpdatedControl ControlID="A1HtmlChart" />
                    </UpdatedControls>
                </telerik:AjaxSetting>
                <telerik:AjaxSetting AjaxControlID="ReportTelefonZeitenPivotGrid">
                    <UpdatedControls>
                        <telerik:AjaxUpdatedControl ControlID="ReportTelefonZeitenPivotGrid" />
                        <telerik:AjaxUpdatedControl ControlID="A2HtmlChart" />
                    </UpdatedControls>
                </telerik:AjaxSetting>
            </AjaxSettings>
        </telerik:RadAjaxManagerProxy>
     
        <div class="box">
            <telerik:RadTabStrip ID="ReportTelefonTabStrip" runat="server" MultiPageID="ReportTelefonMultiPage" SelectedIndex="0" Enabled="true"
                RenderMode="<%$ Resources:Einstellungen, RenderMode %>">
                <Tabs>
                    <telerik:RadTab runat="server" Text="Auswertung 1" Selected="true" />
                    <telerik:RadTab runat="server" Text="Auswertung 2" />
                </Tabs>
            </telerik:RadTabStrip>
     
            <telerik:RadMultiPage ID="ReportTelefonMultiPage" runat="server" SelectedIndex="0" Enabled="true" RenderMode="<%$ Resources:Einstellungen, RenderMode %>">
     
                <telerik:RadPageView ID="ReportTelefonA1PageView" runat="server">
                    <telerik:RadPivotGrid ID="ReportTelefonPivotGrid" runat="server" OnNeedDataSource="ReportTelefonPivotGrid_NeedDataSource"
                        ShowDataHeaderZone="false" ShowFilterHeaderZone="false" TotalsSettings-ColumnGrandTotalsPosition="None" Culture="de-DE"
                        RenderMode="<%$ Resources:Einstellungen, RenderMode %>" ColumnGroupsDefaultExpanded="false" OnCellDataBound="ReportTelefonPivotGrid_CellDataBound"
                        EnableConfigurationPanel="false">
                        <Fields>
                            <telerik:PivotGridRowField DataField="MitarbeiterDIMENSION_LONG" Caption="Abteilung" CellStyle-Width="150" ZoneIndex="0" />
                            <telerik:PivotGridRowField DataField="Name" Caption="Mitarbeiter" CellStyle-Width="150" />
                            <telerik:PivotGridColumnField DataField="Jahr" Caption="Jahr" />
                            <telerik:PivotGridColumnField DataField="Monat" Caption="Monat" />
                            <telerik:PivotGridColumnField DataField="Art" Caption="Art" />
                            <telerik:PivotGridAggregateField DataField="Dauer" DataFormatString="{0:N0}" Aggregate="Count">
                                <HeaderCellTemplate>
                                    Anzahl
                                </HeaderCellTemplate>
                            </telerik:PivotGridAggregateField>
                            <telerik:PivotGridAggregateField DataField="DauerSek" DataFormatString="{0:N0}">
                                <HeaderCellTemplate>
                                    Dauer
                                </HeaderCellTemplate>
                                <CellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </CellTemplate>
                                <RowTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </RowTotalCellTemplate>
                                <RowGrandTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </RowGrandTotalCellTemplate>
                                <ColumnTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </ColumnTotalCellTemplate>
                                <ColumnGrandTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </ColumnGrandTotalCellTemplate>
                            </telerik:PivotGridAggregateField>
                        </Fields>
                        <ConfigurationPanelSettings Position="Left" DefaultDeferedLayoutUpdate="true" />
                    </telerik:RadPivotGrid>
     
                    <telerik:RadHtmlChart ID="A1HtmlChart" runat="server" RenderMode="<%$ Resources:Einstellungen, RenderMode %>" Legend-Appearance-Position="Top"
                        PlotArea-YAxis-LabelsAppearance-DataFormatString="N0" PlotArea-XAxis-LabelsAppearance-RotationAngle="45">
                        <Appearance>
                            <FillStyle BackgroundColor="Transparent" />
                        </Appearance>
                        <ChartTitle Text="Telefondaten">
                            <Appearance Align="Center" BackgroundColor="Transparent" Position="Top" />
                        </ChartTitle>
                    </telerik:RadHtmlChart>
                </telerik:RadPageView>
     
                <telerik:RadPageView ID="ReportTelefonA2PageView" runat="server">
                    <telerik:RadPivotGrid ID="ReportTelefonZeitenPivotGrid" runat="server" OnNeedDataSource="ReportTelefonZeitenPivotGrid_NeedDataSource"
                        ShowDataHeaderZone="false" ShowFilterHeaderZone="false" TotalsSettings-ColumnGrandTotalsPosition="None" Culture="de-DE"
                        RenderMode="<%$ Resources:Einstellungen, RenderMode %>" ColumnGroupsDefaultExpanded="false" OnCellDataBound="ReportTelefonZeitenPivotGrid_CellDataBound"
                        EnableConfigurationPanel="false">
                        <Fields>
                            <telerik:PivotGridRowField DataField="Wochentag" Caption="Wochentag" CellStyle-Width="100" />
                            <telerik:PivotGridRowField DataField="Stunde" Caption="Stunde" CellStyle-Width="100" ZoneIndex="0" />
                            <telerik:PivotGridRowField DataField="BN_Telefon_Nebenstelle_1" Caption="Nebenstelle" CellStyle-Width="150" ZoneIndex="0" />
                            <telerik:PivotGridColumnField DataField="Jahr" Caption="Jahr" />
                            <telerik:PivotGridColumnField DataField="Monat" Caption="Monat" />
                            <telerik:PivotGridColumnField DataField="Art" Caption="Art" />
                            <telerik:PivotGridAggregateField DataField="Dauer" DataFormatString="{0:N0}" Aggregate="Count">
                                <HeaderCellTemplate>
                                    Anzahl
                                </HeaderCellTemplate>
                            </telerik:PivotGridAggregateField>
                            <telerik:PivotGridAggregateField DataField="DauerSek" DataFormatString="{0:N0}">
                                <HeaderCellTemplate>
                                    Dauer
                                </HeaderCellTemplate>
                                <CellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </CellTemplate>
                                <RowTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </RowTotalCellTemplate>
                                <RowGrandTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </RowGrandTotalCellTemplate>
                                <ColumnTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </ColumnTotalCellTemplate>
                                <ColumnGrandTotalCellTemplate>
                                    <%# (Convert.ToInt64(Container.DataItem) == 0) ? "" : string.Format("{0:d2}:{1:d2}:{2:d2}", (Convert.ToInt64(Container.DataItem)/3600) , (Convert.ToInt64(Container.DataItem)%3600)/60 , (Convert.ToInt64(Container.DataItem)%3600)%60) %>
                                </ColumnGrandTotalCellTemplate>
                            </telerik:PivotGridAggregateField>
                        </Fields>
                        <ConfigurationPanelSettings Position="Left" DefaultDeferedLayoutUpdate="true" />
                    </telerik:RadPivotGrid>
     
                    <telerik:RadHtmlChart ID="A2HtmlChart" runat="server" RenderMode="<%$ Resources:Einstellungen, RenderMode %>" Legend-Appearance-Position="Top"
                        PlotArea-YAxis-LabelsAppearance-DataFormatString="N0" PlotArea-XAxis-LabelsAppearance-RotationAngle="45">
                        <Appearance>
                            <FillStyle BackgroundColor="Transparent" />
                        </Appearance>
                        <ChartTitle Text="Telefondaten">
                            <Appearance Align="Center" BackgroundColor="Transparent" Position="Top" />
                        </ChartTitle>
                    </telerik:RadHtmlChart>
                </telerik:RadPageView>
     
            </telerik:RadMultiPage>
     
        </div>
     
    </asp:Content>

     

    and c#

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Telerik.Web.UI;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Text;
     
    namespace BdVNet.Content.Reporting
    {
        public partial class Telefondaten : System.Web.UI.Page
        {
            protected override void OnPreRender(EventArgs e)
            {
                base.OnPreRender(e);
                ReportTelefonPivotGrid.ColumnGroupsDefaultExpanded = false;
                ReportTelefonPivotGrid.RowGroupsDefaultExpanded = false;
                ReportTelefonPivotGrid.Rebind();
     
                base.OnPreRender(e);
                ReportTelefonZeitenPivotGrid.ColumnGroupsDefaultExpanded = false;
                ReportTelefonZeitenPivotGrid.RowGroupsDefaultExpanded = false;
                ReportTelefonZeitenPivotGrid.Rebind();
            }
     
            protected void Page_Load(object sender, EventArgs e)
            {
                string currentYear = DateTime.Now.Year.ToString();
                string currentMonth = String.Format(format: "{0:MM}", arg0: DateTime.Now);
     
                if (Code.AccessControl.UserAdminAccessIsValid == true || Code.AccessControl.UserMgmAccessIsValid == true)
                {
                    var menuSelect = (RadMenu)Master.FindControl(id: "BdVMasterMenu");
                    menuSelect.Items[2].Items[1].Selected = true;
                }
                else
                {
                    Response.Redirect(url: "../Geschaeft.aspx");
                }
     
                Label masterPageTextLabel;
                masterPageTextLabel = (Label)Master.FindControl("BdVMasterTextLabel");
                masterPageTextLabel.Text = "Reporting | Telefonanlage";
     
                if (!IsPostBack)
                {
                    ReportTelefonPivotGrid.CollapsedColumnIndexes.Add(new object[] { currentYear });
                    ReportTelefonZeitenPivotGrid.CollapsedColumnIndexes.Add(new object[] { currentYear });
                    //ReportTelefonPivotGrid.CollapsedColumnIndexes.Add(new object[] { currentMonth });
                }
            }
     
            protected void ReportTelefonPivotGrid_NeedDataSource(object sender, Telerik.Web.UI.PivotGridNeedDataSourceEventArgs e)
            {
                string sqlSelectCommand = "SELECT * FROM [BN_R_Telefonanlage] WHERE Name != ''";
     
                var adapter = new SqlDataAdapter(sqlSelectCommand, ConfigurationManager.ConnectionStrings["BdVNet"].ConnectionString);
     
                DataTable myDataTable = new DataTable();
     
                adapter.Fill(myDataTable);
                ReportTelefonPivotGrid.DataSource = myDataTable;
            }
     
            HashSet<string> columnNames = new HashSet<string>();
            Dictionary<string, ColumnSeries> columnSeriesByRowName = new Dictionary<string, ColumnSeries>();
            protected void ReportTelefonPivotGrid_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
            {
                PivotGridDataCell dataCell = e.Cell as PivotGridDataCell;
     
                if (columnNames.Count == 0)
                {
                    this.A1HtmlChart.PlotArea.XAxis.Items.Clear();
                    this.A1HtmlChart.PlotArea.Series.Clear();
                }
     
                if (dataCell != null && dataCell.CellType == PivotGridDataCellType.DataCell)
                {
                    string rowName = GetName(dataCell.ParentRowIndexes);
                    string columnName = GetName(dataCell.ParentColumnIndexes);
                    columnName = columnName.Replace('\'', ' ');
     
                    if (columnNames.Add(columnName))
                    {
                        AxisItem axisItem = new AxisItem(columnName);
                        this.A1HtmlChart.PlotArea.XAxis.Items.Add(axisItem);
                    }
     
                    ColumnSeries columnSeries = null;
     
                    if (columnSeriesByRowName.ContainsKey(rowName))
                    {
                        columnSeries = columnSeriesByRowName[rowName];
                        columnSeries.LabelsAppearance.DataFormatString = "N2";
                        //columnSeries.LabelsAppearance.ClientTemplate = "test #=value#";
                    }
                    else
                    {
                        columnSeries = new ColumnSeries();
                        columnSeriesByRowName.Add(rowName, columnSeries);
                        columnSeries.Name = rowName;
                        columnSeries.LabelsAppearance.DataFormatString = "N0";
                        this.A1HtmlChart.PlotArea.Series.Add(columnSeries);
                    }
                     
                    CategorySeriesItem item = new CategorySeriesItem();
                     
                    decimal value = 0;
     
                    if (e.Cell.DataItem != null && decimal.TryParse(e.Cell.DataItem.ToString(), out value))
                    {
                        if (value != 0)
                        {
                            if (columnName.Contains("Dauer"))
                            {
                                item.Y = value / 3600;
                            }
                            else
                            {
                                item.Y = value;
                            }
                        }
                    }
                    else
                    {
                        item.Y = null;
                    }
                     
                    columnSeries.SeriesItems.Add(item);
                }
            }
     
            private string GetName(object[] indexes)
            {
                StringBuilder builder = new StringBuilder();
     
                foreach (object index in indexes)
                {
                    builder.Append(index.ToString());
                    builder.Append(" | ");
                }
     
                builder.Remove(builder.Length -3, 3);
                builder.Replace("Count of Dauer", "Anzahl");
                builder.Replace("Sum of DauerSek", "Dauer");
     
                return builder.ToString();
            }
     
            protected void ReportTelefonZeitenPivotGrid_NeedDataSource(object sender, PivotGridNeedDataSourceEventArgs e)
            {
                string sqlSelectCommand = "SELECT * FROM [BN_R_Telefonanlage] WHERE Name IS NULL";
     
                var adapter = new SqlDataAdapter(sqlSelectCommand, ConfigurationManager.ConnectionStrings["BdVNet"].ConnectionString);
     
                DataTable myDataTable = new DataTable();
     
                adapter.Fill(myDataTable);
                ReportTelefonZeitenPivotGrid.DataSource = myDataTable;
            }
     
            protected void ReportTelefonZeitenPivotGrid_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
            {
                PivotGridDataCell dataCell = e.Cell as PivotGridDataCell;
     
                if (columnNames.Count == 0)
                {
                    this.A2HtmlChart.PlotArea.XAxis.Items.Clear();
                    this.A2HtmlChart.PlotArea.Series.Clear();
                }
     
                if (dataCell != null && dataCell.CellType == PivotGridDataCellType.DataCell)
                {
                    string rowName = GetName(dataCell.ParentRowIndexes);
                    string columnName = GetName(dataCell.ParentColumnIndexes);
                    columnName = columnName.Replace('\'', ' ');
     
                    if (columnNames.Add(columnName))
                    {
                        AxisItem axisItem = new AxisItem(columnName);
                        this.A2HtmlChart.PlotArea.XAxis.Items.Add(axisItem);
                    }
     
                    ColumnSeries columnSeries = null;
     
                    if (columnSeriesByRowName.ContainsKey(rowName))
                    {
                        columnSeries = columnSeriesByRowName[rowName];
                        columnSeries.LabelsAppearance.DataFormatString = "N2";
                        //columnSeries.LabelsAppearance.ClientTemplate = "test #=value#";
                    }
                    else
                    {
                        columnSeries = new ColumnSeries();
                        columnSeriesByRowName.Add(rowName, columnSeries);
                        columnSeries.Name = rowName;
                        columnSeries.LabelsAppearance.DataFormatString = "N0";
                        this.A2HtmlChart.PlotArea.Series.Add(columnSeries);
                    }
     
                    CategorySeriesItem item = new CategorySeriesItem();
     
                    decimal value = 0;
     
                    if (e.Cell.DataItem != null && decimal.TryParse(e.Cell.DataItem.ToString(), out value))
                    {
                        if (value != 0)
                        {
                            if (columnName.Contains("Dauer"))
                            {
                                item.Y = value / 3600;
                            }
                            else
                            {
                                item.Y = value;
                            }
                        }
                    }
                    else
                    {
                        item.Y = null;
                    }
     
                    columnSeries.SeriesItems.Add(item);
                }
            }
        }
    }

     

    Many thanks in advance

    Andreas

     

  2. Peter Milchev
    Admin
    Peter Milchev avatar
    467 posts

    Posted 30 Mar 2017 Link to this post

    Hello Andreas,

    We have answered in your support ticket and we will share the answer here for convenience and better visibility from the community.  

    The issue seems to be caused by the Rebinding in the PreRender event. 

    Reinitializing the columnNames and columnSeriesByRowName collections before rebinding the PivotGrids should resolve the issue. 

    protected override void OnPreRender(EventArgs e)
    {
        base.OnPreRender(e);
        ReportTelefonPivotGrid.ColumnGroupsDefaultExpanded = false;
        ReportTelefonPivotGrid.RowGroupsDefaultExpanded = false;
        columnNames = new HashSet<string>();
        columnSeriesByRowName = new Dictionary<string, ColumnSeries>();
        ReportTelefonPivotGrid.Rebind();
     
        base.OnPreRender(e);
        ReportTelefonZeitenPivotGrid.ColumnGroupsDefaultExpanded = false;
        ReportTelefonZeitenPivotGrid.RowGroupsDefaultExpanded = false;
        columnNames = new HashSet<string>();
        columnSeriesByRowName = new Dictionary<string, ColumnSeries>();
        ReportTelefonZeitenPivotGrid.Rebind();
    }

    Regards,
    Peter Milchev
    Telerik by Progress
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top