RadPivotGrid Percentage of RowsSubTotalsPosition

1 Answer 61 Views
PivotGrid
George
Top achievements
Rank 1
George asked on 08 Apr 2022, 05:19 AM

I'm new to the Telerik/Progressive framework, so please excuse me if I am asking something too obvious.

I have created a RadPivotGrid (code excerpt below and image attached). Whilst this works really well to display an aggregate COUNT and also shows the Row Sub Totals, I am at a loss on how to make the percentages show for the items based on the row sub total.

To give context; this is to display Survey Data. The rows are populated by Question and Answer, and the Columns provides a count on the number of people who answered. e.g.,

What is your favourite colour
   Red                                                     10
   Blue                                                    20
Total People in RowSubTotal             30

What is your favourite shape
  Square                                                 20
  Triangle                                               15
Total People in RowSubTotal              35

What I want to be able to do, is view this by percent based on the row subtotal

What is your favourite colour
   Red                                                        33%
   Blue                                                       66%
Total People in RowSubTotal               100%

Below is the snippet from RadPivotGrid. I have tried to use TotalFormat-TotalFunction="PercentOfRowTotal" in the PivotGridAggregateField, but this provides a percentage of the whole Grid, not the individual question (RowSubTotal).

Appreciate any help!

<telerik:RadPivotGrid runat="server" ID="RadPivotGridCrossTab" DataSourceID="SqlCrossTab" Skin="Bootstrap" Height="100%"
AllowFiltering="False" ShowColumnHeaderZone="False" ShowDataHeaderZone="False" ShowRowHeaderZone="False" ShowFilterHeaderZone="false" ColumnHeaderTableLayout="Fixed" RowHeaderTableLayout="Fixed" AllowNaturalSort="true">
<SortExpressions>
<telerik:PivotGridSortExpression FieldName="Filters" SortOrder="None" />
</SortExpressions>
<PagerStyle Mode="Slider" ChangePageSizeButtonToolTip="Change Page Size" AlwaysVisible="True" PageSizeControlType="RadComboBox"></PagerStyle>
<ClientSettings EnableFieldsDragDrop="true">
<Scrolling AllowVerticalScroll="true"></Scrolling>
</ClientSettings>
<ConfigurationPanelSettings EnableDragDrop="False" EnableFieldsContextMenu="False"></ConfigurationPanelSettings>
<RowHeaderCellStyle  Font-Names="Rubik" BackColor="#3D1A6F" ForeColor="White"></RowHeaderCellStyle>
<ColumnHeaderCellStyle Font-Bold="True" Font-Names="Rubik" BackColor="#3D1A6F" ForeColor="White"></ColumnHeaderCellStyle>
<DataCellStyle Width="250px" Font-Names="Rubik" CssClass="text-center" />
<TotalsSettings RowsSubTotalsPosition="First" RowGrandTotalsPosition="None" ColumnsSubTotalsPosition="None" ColumnGrandTotalsPosition="None"></TotalsSettings>
<Fields>
<telerik:PivotGridColumnField UniqueName="Filters" DataField="Filters"></telerik:PivotGridColumnField>
<telerik:PivotGridRowField UniqueName="Question" DataField="Question" CellStyle-Width="350px"></telerik:PivotGridRowField>
<telerik:PivotGridRowField UniqueName="Answer" DataField="Answer" CellStyle-Width="350px"></telerik:PivotGridRowField>
<telerik:PivotGridAggregateField DataField="Respondent" Aggregate="Count" ></telerik:PivotGridAggregateField>
</Fields>
</telerik:RadPivotGrid>

1 Answer, 1 is accepted

Sort by
0
Attila Antal
Telerik team
answered on 12 Apr 2022, 01:30 PM

Hi George,

If you'd like to format the values, you can use the DataFormatString property of the Fields in combination with the Standard numeric format strings.

For example, to format a value as a percentage which to display a percentage sign you can try the following:

<telerik:PivotGridAggregateField DataField="Respondent" Aggregate="Count" DataFormatString="{0:P}"></telerik:PivotGridAggregateField>

 

This, however, requires that the values are already calculated into percentage values.

For example:

  • If the value is 1, the Formatting will change it to 100%
  • In the case of 0.1, it will be changed to 10%
  • Finally, the 0.01 will represent 1%

You can test this out with the following code:

<asp:Label ID="Label1" runat="server"></asp:Label>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Label1.Text += String.Format("Value of 1 equals to: {0}<br/>", 1.ToString("P"));
            Label1.Text += String.Format("Value of 0.1 equals to: {0}<br/>", 0.1.ToString("P"));
            Label1.Text += String.Format("Value of 0.01 equals to: {0}<br/>", 0.01.ToString("P"));
        }
    }
</script>

 

Result

You can perhaps, divide the values by 100 and get the results needed for the Standard Formatting.

 

If the values already represent the final percentage, you can concatenate the percentage symbol like this:

<telerik:PivotGridAggregateField DataField="Respondent" Aggregate="Count" DataFormatString="{0}%"></telerik:PivotGridAggregateField>

 

To format the value as a currency, you can use the Currency format string.

<telerik:PivotGridAggregateField DataField="Respondent" Aggregate="Count" DataFormatString="{0:C}"></telerik:PivotGridAggregateField>

Example

<asp:Label ID="Label1" runat="server"></asp:Label>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Label1.Text += String.Format("Currency 34.78 equals to: {0}<br/>", 34.78.ToString("C"));
            Label1.Text += String.Format("Currency 1000.00 equals to: {0}<br/>", 1000.00.ToString("C"));
        }
    }
</script>

 

Result

 

I hope this will prove helpful.

 

Regards,
Attila Antal
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

George
Top achievements
Rank 1
commented on 04 May 2022, 05:43 AM

Thanks for the response. Unfortunately, that doesn't actually answer the question - that just formats the data.

I have since resolved this issue by doing the calculations within a coded SQL query to pass the data back. It's a shame that this isn't part of the control.

Attila Antal
Telerik team
commented on 04 May 2022, 11:03 AM

Hi George,

I am sorry to hear my answer did not answer the question.

I may have misunderstood your request. In that case, could you please elaborate on the required behavior so that I can get a better understanding?

Telerik Components include a wide variety of functionalities, and over the past decade we have covered the most common scenarios, but if you have an idea for functionality that you think would be a good addition, I kindly ask you to submit a Feature Request in our Feedback Portal at Progress® Telerik® UI for ASP.NET AJAX Feedback Portal and share your idea, or example that you have.

By doing that, the Item will have the chance to become public and get popular eventually.

Tags
PivotGrid
Asked by
George
Top achievements
Rank 1
Answers by
Attila Antal
Telerik team
Share this question
or