Hiding empty columns / Difference calculation

[Tayonee]

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>

[Vasil]

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

 

 

[Tayonee]

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.

[Tayonee]

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

[Tayonee]

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

[Vasil]

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

 

 

[Tayonee]

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!