Hiding empty columns / Difference calculation

7 posts, 0 answers
  1. Tayonee
    Tayonee avatar
    52 posts
    Member since:
    Nov 2006

    Posted 12 Jun 2014 Link to this post

    I have been trying to find the simplest method for creating a column in my PivotGrid that displays the difference between the only other two columns being displayed in the PivotGrid. I thought this would be a 5 minute exercise that has taken me all afternoon to get to *sort of* work. I am wondering if I am going about this all wrong... let me know before you mentally get into all the details below.

    What I ended up with was using a PivotGridAggregateField field, "DifferenceInHoursFromCMS", with a TotalFunction of "DifferenceFrom". The problem I am having with this is that it leaves this empty column in the output (seen in attached screenshot). Is there any way to hide that empty column?

    More importantly, all I really want to see is this table structure:

    Row 1, Column 1: CMS
    Row 1, Column 2: G2
    Row 1, Column 3: Difference

    So, a sample might look like this:

    15     10     5
    25     22     3

    It doesn't seem like this should be that hard. What is the easiest way to get that output?

    Here is my grid:

        <telerik:RadPivotGrid ID="pvtCMStoG2" runat="server" DataSourceID="sqlCMStoG2TimesheetValidation" AllowPaging="True" PageSize="15" AggregatesLevel="1">
            <PagerStyle ChangePageSizeButtonToolTip="Change Page Size" PageSizeControlType="RadComboBox" AlwaysVisible="True"></PagerStyle>
            <Fields>
                <telerik:PivotGridColumnField Caption="Source" DataField="Source" UniqueName="Source">
                </telerik:PivotGridColumnField>
                <telerik:PivotGridRowField Caption="Customer" DataField="CustomerIdentityString" UniqueName="CustomerIdentityString">
                </telerik:PivotGridRowField>
                <telerik:PivotGridAggregateField Aggregate="Sum" DataField="Hours" GrandTotalAggregateFormatString="" UniqueName="SumOfHours">
                    <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" />
                    <HeaderCellTemplate>
                        <asp:Label ID="lblHeader" runat="server" Text="Total Hours"></asp:Label>
                    </HeaderCellTemplate>
                </telerik:PivotGridAggregateField>
                <telerik:PivotGridAggregateField Aggregate="Count" DataField="Hours" GrandTotalAggregateFormatString="" UniqueName="CountOfVisits">
                    <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" />
                    <HeaderCellTemplate>
                        <asp:Label ID="lblHeader" runat="server" Text="Service Count"></asp:Label>
                    </HeaderCellTemplate>
                </telerik:PivotGridAggregateField>
                <telerik:PivotGridAggregateField Aggregate="Sum" DataField="Hours" GrandTotalAggregateFormatString="" UniqueName="DifferenceInHoursFromCMS">
                    <HeaderCellTemplate>
                        <asp:Label ID="lblHeader" runat="server" Text="Difference in Hours"></asp:Label>
                    </HeaderCellTemplate>
                    <TotalFormat Axis="Columns" Level="0" SortOrder="Ascending" TotalFunction="DifferenceFrom" GroupName="CMS" />
                </telerik:PivotGridAggregateField>
            </Fields>
            <TotalsSettings RowGrandTotalsPosition="First" ColumnGrandTotalsPosition="None" />
            <ConfigurationPanelSettings EnableOlapTreeViewLoadOnDemand="True"></ConfigurationPanelSettings>
        </telerik:RadPivotGrid>
  2. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 18 Jun 2014 Link to this post

    Hello Tayonee,

    If I understand correctly your requirements you actually need additional item, not extra field.
    Check this example, and look the code for the calculated item ("Forecast for 1999")
    http://demos.telerik.com/aspnet-ajax/pivotgrid/examples/calculations/defaultcs.aspx

    In your case you will have some code like:
    if (e.GroupName.ToString() == "DiffItem")
    {
     double val1 = double.Parse(e.GetAggregateSummaryValue("CMS").GetValue().ToString());
     double val2 = double.Parse(e.GetAggregateSummaryValue("G2").GetValue().ToString());
     e.CalculatedValue = new DoubleAggregateValue(val1 - val2);
    }
    For such markup:
    <telerik:PivotGridColumnField Caption="Source" DataField="Source" UniqueName="Source">
       <CalculatedItems>
          <telerik:PivotGridCalculatedItem GroupName="DiffItem"></telerik:PivotGridCalculatedItem>
       </CalculatedItems>
    </telerik:PivotGridColumnField>

    I hope this helps.


    Regards,
    Vasil
    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
  4. Tayonee
    Tayonee avatar
    52 posts
    Member since:
    Nov 2006

    Posted 18 Jun 2014 in reply to Vasil Link to this post

    Thank you for your response. Something is still not right - I am not sure if I should change the existing "Source" column to the definition you provided, or add what you provided as a separate column. Either way caused exceptions. Perhaps I should also give a little more detail about the format of my stored procedure result sets as well.

    Here is the current definition of the PivotGrid:

    <telerik:RadPivotGrid ID="pvtCMStoG2" runat="server" EnableCaching="false" DataSourceID="sqlCMStoG2TimesheetValidation" AllowPaging="True" PageSize="15" AggregatesLevel="1" RowGroupsDefaultExpanded="False">
                      <PagerStyle ChangePageSizeButtonToolTip="Change Page Size" PageSizeControlType="RadComboBox" AlwaysVisible="True"></PagerStyle>
                      <Fields>
                           <telerik:PivotGridColumnField Caption="Source" DataField="Source" UniqueName="Source">
                             <CalculatedItems>
                                <telerik:PivotGridCalculatedItem GroupName="Difference"></telerik:PivotGridCalculatedItem>
                             </CalculatedItems>
                          </telerik:PivotGridColumnField>
                          <telerik:PivotGridRowField Caption="Customer" DataField="Customer" UniqueName="Customer">
                          </telerik:PivotGridRowField>
                          <telerik:PivotGridAggregateField Aggregate="Sum" DataField="Hours" GrandTotalAggregateFormatString="" UniqueName="SumOfHours">
                              <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" />
                              <HeaderCellTemplate>
                                  <asp:Label ID="lblHeader" runat="server" Text="Total Hours"></asp:Label>
                              </HeaderCellTemplate>
                          </telerik:PivotGridAggregateField>
                          <telerik:PivotGridAggregateField Aggregate="Count" DataField="Hours" GrandTotalAggregateFormatString="" UniqueName="CountOfVisits">
                              <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" />
                              <HeaderCellTemplate>
                                  <asp:Label ID="lblHeader" runat="server" Text="Service Count"></asp:Label>
                              </HeaderCellTemplate>
                          </telerik:PivotGridAggregateField>
                      </Fields>
                      <TotalsSettings RowGrandTotalsPosition="First" ColumnGrandTotalsPosition="None" />
                      <ConfigurationPanelSettings EnableOlapTreeViewLoadOnDemand="True"></ConfigurationPanelSettings>
                  </telerik:RadPivotGrid>
     
    and here is the full text of the event handler I am currently using:

    Private Sub pvtCMStoG2_ItemNeedCalculation(sender As Object, e As PivotGridCalculationEventArgs) Handles pvtCMStoG2.ItemNeedCalculation
         'Calculate the difference between G2 and CMS hours.
         If e.GroupName.ToString() = "Difference" Then
             'Declare local variables.
             Dim m_dblCMSValue, m_dblG2Value As Double
     
             'Get the current CMS value.
             If String.IsNullOrEmpty(e.GetAggregateSummaryValue("CMS").GetValue().ToString()) Then
                 m_dblCMSValue = 0D
             Else
                 m_dblCMSValue = Double.Parse(e.GetAggregateSummaryValue("CMS").GetValue().ToString())
             End If
     
             'Get the current G2 value.
             If String.IsNullOrEmpty(e.GetAggregateSummaryValue("G2").GetValue().ToString()) Then
                 m_dblG2Value = 0D
             Else
                 m_dblG2Value = Double.Parse(e.GetAggregateSummaryValue("G2").GetValue().ToString())
             End If
     
             'Calculate the final difference value.
             e.CalculatedValue = New DoubleAggregateValue(m_dblCMSValue - m_dblG2Value)
         End If
     End Sub


    I am getting the exception "An exception of type 'System.NullReferenceException' occurred.... Object reference not set to an instance of an object' on this line:

    If String.IsNullOrEmpty(e.GetAggregateSummaryValue("G2").GetValue().ToString()) Then

    The basic format of the results is shown in the attached screenshot. The first column, "Source" can have any of the values "CMS", "G2" or "Billing File", and that field will never be empty.

    Note: I am renamed the groups from the code sample you provided above for clarity on my end. What am I doing wrong?

    Off the top of my head I am not sure how the code would know which of the two columns that are present under each Source heading the calculation done in the code-behind would refer to. There are two levels of column output, as there should be... one on Source and the other are the different kinds of aggregate calculations on those unique groups.

    With the code-behind method disabled I have also provided a screenshot of the PivotGrid in action to make sure nothing is being missed here.

    Please let me know how to resolve this issue.
  5. Tayonee
    Tayonee avatar
    52 posts
    Member since:
    Nov 2006

    Posted 18 Jun 2014 in reply to Tayonee Link to this post

    I have added an existence test to the same single line I called out above and so it never throws an exception, but it never finds a value either...

    If e.GetAggregateSummaryValue("G2") IsNot Nothing AndAlso String.IsNullOrEmpty(e.GetAggregateSummaryValue("G2").GetValue().ToString() = False) Then

    So, there is something else going on here it would seem...
  6. Tayonee
    Tayonee avatar
    52 posts
    Member since:
    Nov 2006

    Posted 18 Jun 2014 in reply to Tayonee Link to this post

    OK, I found out that the existence test was improperly formatted (a parens in the wrong place).

    The line I proposed above:

    If e.GetAggregateSummaryValue("G2") IsNot Nothing AndAlso String.IsNullOrEmpty(e.GetAggregateSummaryValue("G2").GetValue().ToString() = False) Then

    was supposed to be:

    If e.GetAggregateSummaryValue("G2") IsNot Nothing AndAlso String.IsNullOrEmpty(e.GetAggregateSummaryValue("G2").GetValue().ToString()) = False Then


    The problem I am running into now is that the "Difference" column formatting logic that I used before is no longer "picking up". In the CellDataBound event handler I have pulled the formatting code sample from the demos, and got it to work with the previous design of my PivotGrid, but it doesn't work properly with this new way of doing things you prescribed above.

    In particular, I can only seem to get the name of the current field, but not the name of the parent group is part of (i.e. "CMS", "G2" of "Billing File"). Here is the part of the event handler in question:

    ElseIf cell.CellType = PivotGridDataCellType.RowGrandTotalDataCell OrElse cell.CellType = PivotGridDataCellType.ColumnGrandTotalDataCell Then
                Select Case TryCast(cell.Field, PivotGridAggregateField).UniqueName
                    Case "Difference"
                        'Get the current value.
                        Dim quantity As Double = Convert.ToDouble(cell.DataItem)
     
                        'Conditionally set the format.
                        If quantity <> 0 Then
                            cell.BackColor = Color.Red
                            cell.ForeColor = Color.White
                            cell.Font.Bold = True
                        End If
                        Exit Select
                End Select

    Based on the current format, in the attached screenshot, how do I find out which group the current field is in? Note; I have added back in a few of the other row fields that I use - my initial example was simplified for your evaluation.

    What I am ultimately trying to accomplish is to change the foreground and background color of ANY fields in the various "Difference" columns that are not a value of zero - to indicate a problem.

    When I run the debugger on this line of code:

    Select Case TryCast(cell.Field, PivotGridAggregateField).UniqueName

    I hit the line 6 times - 3 times for each visible group. What I see for "UniqueName" is "SumOfHours", then "CountOfVisits", three times. I understand why I see those values but need to "look" one column group higher to find out if these pairs of fields are in the "CMS", "G2" or "Billing File" top level column.

     Thanks in advance for the clarification...
  7. Vasil
    Admin
    Vasil avatar
    1547 posts

    Posted 23 Jun 2014 Link to this post

    Hi,

    Did you tried to use the cell.ParentColumnIndexes collection to check if the current cell have particular parent?

    If cell.ParentColumnIndexes(1).ToString() = "Service Count"  And cell.ParentColumnIndexes(0).ToString() = "CMS"
    Then


    Regards,
    Vasil
    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.

     
  8. Tayonee
    Tayonee avatar
    52 posts
    Member since:
    Nov 2006

    Posted 24 Jun 2014 in reply to Vasil Link to this post

    Thank you Vasil - that did the trick. I had not tried it because I somehow missed documentation on those methods of navigating the heirarchy of the PivotGrid, but it is now working correctly!
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017