Change RadPivotGrid header information when data binding information

[reguapo]

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??

[Konstantin Dikov]

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