Change RadPivotGrid header information when data binding information

2 posts, 0 answers
  1. reguapo
    reguapo avatar
    16 posts
    Member since:
    Jul 2009

    Posted 05 May 2014 Link to this post

    I have a pivot grid to show some information, kind of a report, where the user can select a dates, From and To, and have to show a master header with the year, and right below the months, depending on what the user select. Now I have the years, and the months showing as I want except that I am showing the month number, not the month year (ex: 1 instead of January). I want to show January instead of 1. Here is the code and I attach some pictures.

    <telerik:RadPivotGrid ID="RadPivotGrid1" runat="server" Skin="Glow" OnNeedDataSource="RadPivotGrid1_OnNeedDataSource"
                    AllowFiltering="False" RowTableLayout="Tabular" AggregatesPosition="Columns" ShowColumnHeaderZone="False"
                    ShowDataHeaderZone="False" ShowRowHeaderZone="False" AllowPaging="True" PageSize="50" ShowFilterHeaderZone="false"
                    AggregatesLevel="2" TotalsSettings-ColumnGrandTotalsPosition="None" TotalsSettings-RowGrandTotalsPosition="Last" >
                    <PagerStyle ChangePageSizeButtonToolTip="Change Page Size" PageSizeControlType="RadComboBox"></PagerStyle>
     
                    <Fields>
                        <telerik:PivotGridColumnField DataField="Year" UniqueName="Year" Caption="Year">
                        </telerik:PivotGridColumnField>
                        <telerik:PivotGridColumnField DataField="Month" UniqueName="Month" Caption="Month">
                        </telerik:PivotGridColumnField>
                        <telerik:PivotGridRowField DataField="DestinationName" UniqueName="DestinationName" Caption="Destination">
                        </telerik:PivotGridRowField>
                        <telerik:PivotGridAggregateField DataFormatString="{0:N0}" DataField="Teus" UniqueName="Teus" Caption="Teus">
                            <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" TotalFunction="NoCalculation" />
                            <HeaderCellTemplate>
                                <asp:Label ID="lblAggregateCellTEUS" Text="Calculated TEUS" runat="server" />
                            </HeaderCellTemplate>
                        </telerik:PivotGridAggregateField>
                        <telerik:PivotGridAggregateField DataFormatString="{0:C}" DataField="TotalCharge" UniqueName="TotalCharge" Caption="TotalCharge">
                            <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" TotalFunction="NoCalculation" />
                            <HeaderCellTemplate>
                                <asp:Label ID="lblAggregateCellTotalCharge" Text="Total Charges" runat="server" />
                            </HeaderCellTemplate>
                        </telerik:PivotGridAggregateField>
                    </Fields>
                </telerik:RadPivotGrid>

    Code behind:
    private List<Result> LoadGridData()
        {
            var result = new List<Result>();
            result.Add(new Result()
            {
                Year = 2014,
                Month = 1,
                DestinationName = "Miami, FL",
                DestinationShortName = "MIA",
                OriginName = "Miami, FL",
                OriginShortName = "MIA",
                TotalCharge = 100,
                Teus = (decimal) 1.5
            });
     
            result.Add(new Result()
            {
                Year = 2013,
                Month = 1,
                DestinationName = "Miami, FL",
                DestinationShortName = "MIA",
                OriginName = "Miami, FL",
                OriginShortName = "MIA",
                TotalCharge = 100,
                Teus = (decimal)1.5
            });
     
            result.Add(new Result()
            {
                Year = 2014,
                Month = 2,
                DestinationName = "Houston, Tx",
                DestinationShortName = "Hou",
                OriginName = "Miami, FL",
                OriginShortName = "MIA",
                TotalCharge = 80,
                Teus = 2
            });
     
            result.Add(new Result()
            {
                Year = 2014,
                Month = 1,
                DestinationName = "Houston, Tx",
                DestinationShortName = "Hou",
                OriginName = "Miami, FL",
                OriginShortName = "MIA",
                TotalCharge = 80,
                Teus = 2
            });
     
            return result;
        }
     
        protected void RadPivotGrid1_OnNeedDataSource(object sender, PivotGridNeedDataSourceEventArgs e)
        {
            RadPivotGrid1.DataSource = LoadGridData();
        }


    Any Help??
  2. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    1800 posts

    Posted 08 May 2014 Link to this post

    Hello Daniel,

    The easiest way for having such result would be to have a DateTime object and use the GroupInterval property for multiple Column/Row fields as following:
    <telerik:PivotGridColumnField Caption="date" DataField="date" UniqueName="year" GroupInterval="Year">
    </telerik:PivotGridColumnField>
    <telerik:PivotGridColumnField Caption="date" DataField="date" UniqueName="month" GroupInterval="Month">
    </telerik:PivotGridColumnField>

    In the above example, "date" is a DateTime object.

    Nevertheless, for your convenience, following is an example demonstrating how to handle a integer representation of the month and change it to the corresponding month name:
    <telerik:RadPivotGrid ID="RadPivotGrid1" TotalsSettings-GrandTotalsVisibility="None" runat="server"
        OnNeedDataSource="RadPivotGrid1_NeedDataSource" OnCellDataBound="RadPivotGrid1_CellDataBound"
        ShowFilterHeaderZone="false" ShowDataHeaderZone="false" ShowRowHeaderZone="false"
        ShowColumnHeaderZone="false" AllowSorting="false">
        <PagerStyle ChangePageSizeButtonToolTip="Change Page Size" PageSizeControlType="RadComboBox"></PagerStyle>
        <Fields>
            <telerik:PivotGridRowField Caption="zone" DataField="zone" UniqueName="zone">
            </telerik:PivotGridRowField>
            <telerik:PivotGridRowField Caption="country" DataField="country" UniqueName="country">
            </telerik:PivotGridRowField>
     
            <telerik:PivotGridColumnField Caption="year" DataField="year" UniqueName="year">
            </telerik:PivotGridColumnField>
     
            <telerik:PivotGridColumnField Caption="month" DataField="month" UniqueName="month"
                 DataFormatString="&month&{0}">
            </telerik:PivotGridColumnField>
     
            <telerik:PivotGridAggregateField Aggregate="Sum" Caption="# Isolations" DataField="quantitySLA" UniqueName="column2">
                <TotalFormat TotalFunction="NoCalculation" Axis="Columns" Level="1" />
            </telerik:PivotGridAggregateField>
        </Fields>
    </telerik:RadPivotGrid>

    And the code-behind:
    protected void RadPivotGrid1_NeedDataSource(object sender, Telerik.Web.UI.PivotGridNeedDataSourceEventArgs e)
    {
        RadPivotGrid1.DataSource = GetData();
    }
     
    protected void RadPivotGrid1_CellDataBound(object sender, Telerik.Web.UI.PivotGridCellDataBoundEventArgs e)
    {
        //&month& patterns is added in the DataFormatString, so it could be catched and parsed to month name
        if (e.Cell is PivotGridColumnHeaderCell)
        {
            int month = 0;
            PivotGridColumnHeaderCell cell = e.Cell as PivotGridColumnHeaderCell;
            if (cell.Text.IndexOf("&month&") >= 0)
            {
                string cellValue = cell.Text.Replace("&month&", "");
                if (int.TryParse(cellValue, out month))
                {
                    cell.Text = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(int.Parse(cellValue));
                }
            }
        }
    }
     
    private DataTable GetData()
    {
        DataTable table = new DataTable();
        table.Columns.Add("zone", typeof(string));
        table.Columns.Add("country", typeof(string));
        table.Columns.Add("month", typeof(string));
        table.Columns.Add("year", typeof(string));
        table.Columns.Add("quantitySLA", typeof(decimal));
     
        table.Rows.Add("zone 1", "USA", "2","2013", 500);
        table.Rows.Add("zone 1", "USA", "5", "2013", 555);
        table.Rows.Add("zone 1", "USA", "5", "2014", 555);
        table.Rows.Add("zone 1", "USA", "3","2013", 555);
        table.Rows.Add("zone 1", "UK", "6" ,"2013", 600);
        return table;
    }

    As you could notice, we are changing the DataFormatString of the "month" field, so we could easily find the cells on server-side.

    Hope that helps.



    Regards,
    Konstantin Dikov
    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.

     
  3. UI for ASP.NET Ajax is Ready for VS 2017
Back to Top