Showing rows without the selected values

[Jeremy]

I'm working with the pivot grid trying to have it show sub-groups that do not have a certain status as well as only showing two particular values "Pending" & "Granted"

Dataset:

DataTable table = new DataTable();
table.Columns.Add("Product And Business Group");
table.Columns.Add("Platform Or SubGroup");
table.Columns.Add("Status");

table.Rows.Add("Core", "Core Sub Group", "Granted");
table.Rows.Add("Core", "Core Sub Group", "Pending");
table.Rows.Add("Core", "Sub Group should be shown in Core", "Some Other Value");

table.Rows.Add("Non-Core", "Non-Core Sub Group", "Granted");
table.Rows.Add("Non-Core", "Non-Core Sub Group", "Pending");
table.Rows.Add("Non-Core", "Sub Group should be shown in Non-Core", "zAnother value");

Grid:

<telerik:RadPivotGrid ID="RadPivotGrid1"
                    PageSize="20" runat="server"
                    AllowFiltering="true" TotalsSettings-GrandTotalsVisibility="None" RowHeaderZoneText="Business Group" DataSourceID="ObjectDataSource1" Skin="Windows7" EmptyValue="0">
                    <TotalsSettings GrandTotalsVisibility="RowsOnly" ColumnsSubTotalsPosition="Last" RowsSubTotalsPosition="Last" />
                    <ConfigurationPanelSettings/>
                    <RowHeaderCellStyle Width="200px" />
                    <Fields>
                        <telerik:PivotGridRowField Caption="Product And Business Group" DataField="Product And Business Group" >
                        </telerik:PivotGridRowField>
                        <telerik:PivotGridRowField Caption="Platform or sub-group" DataField="Platform Or SubGroup" ShowGroupsWhenNoData="false">
                        </telerik:PivotGridRowField>
                        <telerik:PivotGridColumnField Caption="Status" DataField="Status" UniqueName="Status">
                        </telerik:PivotGridColumnField>
                        <telerik:PivotGridAggregateField Aggregate="Count" DataField="Status" DataFormatString="{0:D0}" UniqueName="StatusCount" >
                            <TotalFormat Axis="Rows" Level="0" SortOrder="Ascending" TotalFunction="NoCalculation" />
                        </telerik:PivotGridAggregateField>
                    </Fields>
                    <ClientSettings>
                        <Scrolling AllowVerticalScroll="false" ScrollHeight="600px" />
                    </ClientSettings>

                </telerik:RadPivotGrid>

Filter:

RadPivotGrid1.SetFilterIncludes("Status", new string[] { "Pending", "Granted" }, true);

I've tried playing around with the ShowGroupsWhenNoData property but that adds the sub groups to all of the groups, which I do not want (Pivot_Filtered_ShowGroupsWhenNoData.PNG)


Expected outcome: Pivot_Filtered_Expected.PNG (Attached)
Actual outcome: Pivot_Filtered.PNG (Attached)

To summarize
I'm trying to achieve the view in Pivot_Filtered_Expected.PNG which is only showing the "Pending" and "Granted" columns. I also want to display sub-groups that do not have one of those status (so their values would be 0 and 0 respectively).

The problems I'm having now is it either shows the values that I do not want (if I don't have a filter) or it includes the sub-groups in the wrong business group (ShowGroupsWhenNoData).

Any advice on accomplishing the view I'm looking for?

[Marin]

Hi,

The ShowGroupsWhenNoData property will affect all the values in a field, so when set to true, it will also show Non-core subgroups in the Core group and Core sub groups in Non-core group. Unfortunately the grid does not support at the moment such type of selective filtering of the data where only core sub groups will be shown in the core group and non-core sub groups in the Non-Core group.

Regards,
Marin
Telerik

 

 

[Jeremy]

Thanks for the information. Any tips on maybe how to hack in the functionality I'm looking for? Like hiding columns or injecting rows manually?

[Marin]

Hello,

We have replied to your support ticket on the same issue. Basically you can try the CellDataBound event to hide cells from the pivot grid based on your custom criteria:

protected void RadPivotGrid1_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
    {
        var rowHeaderCell = e.Cell as PivotGridHeaderCell;
        var dataCell = e.Cell as PivotGridDataCell;
        if (rowHeaderCell != null)
        {
            if (rowHeaderCell.ParentIndexes != null && rowHeaderCell.ParentIndexes.Length > 0)
            {
                if ((rowHeaderCell.Text == "Core Sub Group" || rowHeaderCell.Text == "Sub Group should be shown in Core") &&
                    rowHeaderCell.ParentIndexes[0] != "Core")
                {
                    rowHeaderCell.Style["display"] = "none";
                }

                if ((rowHeaderCell.Text == "Non-Core Sub Group" || rowHeaderCell.Text.Contains("Sub Group should be shown in Non-Core")) &&
                    rowHeaderCell.ParentIndexes[0] != "Non-Core")
                {
                    rowHeaderCell.Style["display"] = "none";
                }

            }
        }

        if (dataCell != null)
        {
            if (dataCell.Text == "0" && dataCell.ParentRowIndexes.Length == 2 &&
                (dataCell.ParentRowIndexes[0] == "Core" && (dataCell.ParentRowIndexes[1] == "Non-Core Sub Group" || dataCell.ParentRowIndexes[1] == "Sub Group should be shown in Non-Core") ||
                 dataCell.ParentRowIndexes[0] == "Non-Core" && (dataCell.ParentRowIndexes[1] == "Core Sub Group" || dataCell.ParentRowIndexes[1] == "Sub Group should be shown in Core")))
            {
                dataCell.Style["display"] = "none";
            }
        }
    }

We can continue any further communication on the problem in the support ticket in order to avoid duplicate posts here.

Kind Regards,
Marin
Telerik

 

 

[Tom]

Is it possible to get at the filter items the user has selected? I see Telerik.Web.UI.PivotGrid.Core.SetConditionHashCollection and the items selected but I can't seem to get at them. Code sample?

[Marin Bratanov]

Hello Tom,

I have answered your support ticket with this question and I am pasting my answer here for anyone else having the same query.

---

Custom filtering feature is not available in the pivot grid, and there simply is no API that returns the currently chosen filters. I can suggest you add your request in the Feedback portal: https://feedback.telerik.com/project/108. Providing an explanation of the use case, how you expect the filters to be exposed and whether any additional configuration is expected will help both our management and other clients to evaluate the idea.

In the meantime I can suggest downloading the Telerik.Web.UI source code from your account and examining the internal implementation of the FiltersPersistence property in RadPivotGrid.cs. It is used by RadPersistenceFramework to store the pivot grid settings and uses string serialization. The SerializePivotFilter method in PivotGridFilterPersistenceHelper.cs may give you some pointers and ideas to try. To be honest, I have not tried extracting it out of the project to see if and how it can be run outside of its current scope of internal classes.

I can also offer two ideas you can try, but I must note they are not tested and I cannot guarantee the aproach will even be feasible:

• try looping through the controls collection in the filter window on the server to see if you can get to the controls you seek and extract data from them, something like PivotGridInstance.FilterWindow.ContentContainer.Controls
• try accessing controls in the filter window on the client, something like this can get you the main wrapper 
  var wnd = $find("<%=RadPivotGrid1.ClientID%>" + "_FilterWindow");
  if (wnd && wnd.isVisible()) { 
    var contentWrapper = $telerik.$(wnd.get_contentElement()).find(".rpgFilterWindowContainer").first();
  }

Regards,
Marin Bratanov
Progress Telerik