PivotGrid - Chart Integration

[Andreas]

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

 

[Peter Milchev]

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