Interactive Sorting in a Reporting Services report with multiple groupings and toggles

Question

Using Reporting Services 2008 r2.

In a Reporting Services report I have a table with two groupings: Group A and Group B. To show measures the Detail group is used.

The Group B is set to Toggle on a value in Group A.

Example: Group A is Car Makes and Group B is Car Models. In the details the Profit is shown. Initial only a list of Car Makes are shown with the possibility to expand (Toggle) and show Car Models. Thereby splitting the Profit into Car Models.

And now the problem. On the Profit Column the Interactive Sort feature is used to sort the values in the table with the 2 groups by Profit. So far I have only managed to sort either Group A or Group B. That is not what is wanted. What I want is to sort the whole table by Profit both Group A and Group B still maintaining the groupings.

Is this supported in Reporting Services?

Thanks in advance.

Report (.rdl) code example based on adventure works:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
<ReportItems>
  <Tablix Name="Tablix1">
    <TablixBody>
      <TablixColumns>
        <TablixColumn>
          <Width>2.5cm</Width>
        </TablixColumn>
      </TablixColumns>
      <TablixRows>
        <TablixRow>
          <Height>0.6cm</Height>
          <TablixCells>
            <TablixCell>
              <CellContents>
                <Textbox Name="Textbox1">
                  <CanGrow>true</CanGrow>
                  <UserSort>
                    <SortExpression>=Fields!Gross_Profit.Value</SortExpression>
                    <SortExpressionScope>Subcategory</SortExpressionScope>
                  </UserSort>
                  <KeepTogether>true</KeepTogether>
                  <Paragraphs>
                    <Paragraph>
                      <TextRuns>
                        <TextRun>
                          <Value>Gross Profit</Value>
                          <Style />
                        </TextRun>
                      </TextRuns>
                      <Style />
                    </Paragraph>
                  </Paragraphs>
                  <rd:DefaultName>Textbox1</rd:DefaultName>
                  <Style>
                    <Border>
                      <Color>LightGrey</Color>
                      <Style>Solid</Style>
                    </Border>
                    <PaddingLeft>2pt</PaddingLeft>
                    <PaddingRight>2pt</PaddingRight>
                    <PaddingTop>2pt</PaddingTop>
                    <PaddingBottom>2pt</PaddingBottom>
                  </Style>
                </Textbox>
              </CellContents>
            </TablixCell>
          </TablixCells>
        </TablixRow>
        <TablixRow>
          <Height>0.6cm</Height>
          <TablixCells>
            <TablixCell>
              <CellContents>
                <Textbox Name="Textbox13">
                  <CanGrow>true</CanGrow>
                  <KeepTogether>true</KeepTogether>
                  <Paragraphs>
                    <Paragraph>
                      <TextRuns>
                        <TextRun>
                          <Value>=Sum(Fields!Gross_Profit.Value)</Value>
                          <Style>
                            <Format>#,0</Format>
                          </Style>
                        </TextRun>
                      </TextRuns>
                      <Style />
                    </Paragraph>
                  </Paragraphs>
                  <rd:DefaultName>Textbox13</rd:DefaultName>
                  <Style>
                    <Border>
                      <Color>LightGrey</Color>
                      <Style>Solid</Style>
                    </Border>
                    <PaddingLeft>2pt</PaddingLeft>
                    <PaddingRight>2pt</PaddingRight>
                    <PaddingTop>2pt</PaddingTop>
                    <PaddingBottom>2pt</PaddingBottom>
                  </Style>
                </Textbox>
              </CellContents>
            </TablixCell>
          </TablixCells>
        </TablixRow>
        <TablixRow>
          <Height>0.6cm</Height>
          <TablixCells>
            <TablixCell>
              <CellContents>
                <Textbox Name="Gross_Profit">
                  <CanGrow>true</CanGrow>
                  <KeepTogether>true</KeepTogether>
                  <Paragraphs>
                    <Paragraph>
                      <TextRuns>
                        <TextRun>
                          <Value>=Fields!Gross_Profit.Value</Value>
                          <Style>
                            <Format>#,0</Format>
                          </Style>
                        </TextRun>
                      </TextRuns>
                      <Style />
                    </Paragraph>
                  </Paragraphs>
                  <rd:DefaultName>Gross_Profit</rd:DefaultName>
                  <Style>
                    <Border>
                      <Color>LightGrey</Color>
                      <Style>Solid</Style>
                    </Border>
                    <PaddingLeft>2pt</PaddingLeft>
                    <PaddingRight>2pt</PaddingRight>
                    <PaddingTop>2pt</PaddingTop>
                    <PaddingBottom>2pt</PaddingBottom>
                  </Style>
                </Textbox>
              </CellContents>
            </TablixCell>
          </TablixCells>
        </TablixRow>
      </TablixRows>
    </TablixBody>
    <TablixColumnHierarchy>
      <TablixMembers>
        <TablixMember />
      </TablixMembers>
    </TablixColumnHierarchy>
    <TablixRowHierarchy>
      <TablixMembers>
        <TablixMember>
          <TablixHeader>
            <Size>2.5cm</Size>
            <CellContents>
              <Textbox Name="Textbox7">
                <CanGrow>true</CanGrow>
                <KeepTogether>true</KeepTogether>
                <Paragraphs>
                  <Paragraph>
                    <TextRuns>
                      <TextRun>
                        <Value>Category</Value>
                        <Style />
                      </TextRun>
                    </TextRuns>
                    <Style />
                  </Paragraph>
                </Paragraphs>
                <rd:DefaultName>Textbox7</rd:DefaultName>
                <Style>
                  <Border>
                    <Color>LightGrey</Color>
                    <Style>Solid</Style>
                  </Border>
                  <PaddingLeft>2pt</PaddingLeft>
                  <PaddingRight>2pt</PaddingRight>
                  <PaddingTop>2pt</PaddingTop>
                  <PaddingBottom>2pt</PaddingBottom>
                </Style>
              </Textbox>
            </CellContents>
          </TablixHeader>
          <TablixMembers>
            <TablixMember>
              <TablixHeader>
                <Size>2.5cm</Size>
                <CellContents>
                  <Textbox Name="Textbox9">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>Subcategory</Value>
                            <Style />
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox9</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <TablixMembers>
                <TablixMember />
              </TablixMembers>
            </TablixMember>
          </TablixMembers>
          <KeepWithGroup>After</KeepWithGroup>
        </TablixMember>
        <TablixMember>
          <Group Name="Category">
            <GroupExpressions>
              <GroupExpression>=Fields!Category.Value</GroupExpression>
            </GroupExpressions>
          </Group>
          <SortExpressions>
            <SortExpression>
              <Value>=Fields!Category.Value</Value>
            </SortExpression>
          </SortExpressions>
          <TablixHeader>
            <Size>2.5cm</Size>
            <CellContents>
              <Textbox Name="Category">
                <CanGrow>true</CanGrow>
                <KeepTogether>true</KeepTogether>
                <Paragraphs>
                  <Paragraph>
                    <TextRuns>
                      <TextRun>
                        <Value>=Fields!Category.Value</Value>
                        <Style />
                      </TextRun>
                    </TextRuns>
                    <Style />
                  </Paragraph>
                </Paragraphs>
                <rd:DefaultName>Category</rd:DefaultName>
                <Style>
                  <Border>
                    <Color>LightGrey</Color>
                    <Style>Solid</Style>
                  </Border>
                  <PaddingLeft>2pt</PaddingLeft>
                  <PaddingRight>2pt</PaddingRight>
                  <PaddingTop>2pt</PaddingTop>
                  <PaddingBottom>2pt</PaddingBottom>
                </Style>
              </Textbox>
            </CellContents>
          </TablixHeader>
          <TablixMembers>
            <TablixMember>
              <TablixHeader>
                <Size>2.5cm</Size>
                <CellContents>
                  <Textbox Name="Textbox12">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>Total</Value>
                            <Style />
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Textbox12</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                </CellContents>
              </TablixHeader>
              <KeepWithGroup>After</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <Group Name="Subcategory">
                <GroupExpressions>
                  <GroupExpression>=Fields!Subcategory.Value</GroupExpression>
                </GroupExpressions>
              </Group>
              <SortExpressions>
                <SortExpression>
                  <Value>=Fields!Subcategory.Value</Value>
                </SortExpression>
              </SortExpressions>
              <TablixHeader>
                <Size>2.5cm</Size>
                <CellContents>
                  <Textbox Name="Subcategory">
                    <CanGrow>true</CanGrow>
                    <KeepTogether>true</KeepTogether>
                    <Paragraphs>
                      <Paragraph>
                        <TextRuns>
                          <TextRun>
                            <Value>=Fields!Subcategory.Value</Value>
                            <Style />
                          </TextRun>
                        </TextRuns>
                        <Style />
                      </Paragraph>
                    </Paragraphs>
                    <rd:DefaultName>Subcategory</rd:DefaultName>
                    <Style>
                      <Border>
                        <Color>LightGrey</Color>
                        <Style>Solid</Style>
                      </Border>
                      <PaddingLeft>2pt</PaddingLeft>
                      <PaddingRight>2pt</PaddingRight>
                      <PaddingTop>2pt</PaddingTop>
                      <PaddingBottom>2pt</PaddingBottom>
                    </Style>
                  </Textbox>
                  <rd:Selected>true</rd:Selected>
                </CellContents>
              </TablixHeader>
              <TablixMembers>
                <TablixMember>
                  <Group Name="Details" />
                </TablixMember>
              </TablixMembers>
              <Visibility>
                <Hidden>true</Hidden>
                <ToggleItem>Category</ToggleItem>
              </Visibility>
            </TablixMember>
          </TablixMembers>
        </TablixMember>
      </TablixMembers>
    </TablixRowHierarchy>
    <DataSetName>DataSet1</DataSetName>
    <Height>1.8cm</Height>
    <Width>7.5cm</Width>
    <Style>
      <Border>
        <Style>None</Style>
      </Border>
    </Style>
  </Tablix>
</ReportItems>
<Height>0.70866in</Height>
<Style />
  </Body>
  <Width>2.95276in</Width>
 <Page>
<PageHeight>29.7cm</PageHeight>
<PageWidth>21cm</PageWidth>
<LeftMargin>2cm</LeftMargin>
<RightMargin>2cm</RightMargin>
<TopMargin>2cm</TopMargin>
<BottomMargin>2cm</BottomMargin>
<ColumnSpacing>0.13cm</ColumnSpacing>
<Style />
</Page>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSource1">
  <DataSourceReference>DataSource1</DataSourceReference>
  <rd:SecurityType>None</rd:SecurityType>
  <rd:DataSourceID>9a3c3555-b858-4735-8414-0856ebe26806</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
  <Query>
    <DataSourceName>DataSource1</DataSourceName>
    <CommandText> SELECT NON EMPTY { [Measures].[Gross Profit] } ON COLUMNS, NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS * [Product].[Subcategory].[Subcategory].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>
    <rd:DesignerState>
      <QueryDefinition xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition">
        <CommandType>MDX</CommandType>
        <QuerySpecification xsi:type="MDXQuerySpecification" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <Select>
            <Items>
              <Item>
                <ID xsi:type="Level">
                  <DimensionName>Product</DimensionName>
                  <HierarchyName>Category</HierarchyName>
                  <HierarchyUniqueName>[Product].[Category]</HierarchyUniqueName>
                  <LevelName>Category</LevelName>
                  <UniqueName>[Product].[Category].[Category]</UniqueName>
                </ID>
                <ItemCaption>Category</ItemCaption>
                <UniqueName>true</UniqueName>
              </Item>
              <Item>
                <ID xsi:type="Level">
                  <DimensionName>Product</DimensionName>
                  <HierarchyName>Subcategory</HierarchyName>
                  <HierarchyUniqueName>[Product].[Subcategory]</HierarchyUniqueName>
                  <LevelName>Subcategory</LevelName>
                  <UniqueName>[Product].[Subcategory].[Subcategory]</UniqueName>
                </ID>
                <ItemCaption>Subcategory</ItemCaption>
                <UniqueName>true</UniqueName>
              </Item>
              <Item>
                <ID xsi:type="Measure">
                  <MeasureName>Gross Profit</MeasureName>
                  <UniqueName>[Measures].[Gross Profit]</UniqueName>
                </ID>
                <ItemCaption>Gross Profit</ItemCaption>
                <BackColor>true</BackColor>
                <ForeColor>true</ForeColor>
                <FontFamily>true</FontFamily>
                <FontSize>true</FontSize>
                <FontWeight>true</FontWeight>
                <FontStyle>true</FontStyle>
                <FontDecoration>true</FontDecoration>
                <FormattedValue>true</FormattedValue>
                <FormatString>true</FormatString>
              </Item>
            </Items>
          </Select>
          <From>Adventure Works</From>
          <Filter>
            <FilterItems />
          </Filter>
          <Calculations />
          <Aggregates />
          <QueryProperties />
        </QuerySpecification>
        <Query>
          <Statement> SELECT NON EMPTY { [Measures].[Gross Profit] } ON COLUMNS, NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS * [Product].[Subcategory].[Subcategory].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement>
          <ParameterDefinitions />
        </Query>
      </QueryDefinition>
    </rd:DesignerState>
  </Query>
  <Fields>
    <Field Name="Category">
      <DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Category].[Category]" /&gt;</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="Subcategory">
      <DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Subcategory].[Subcategory]" /&gt;</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="Gross_Profit">
      <DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Gross Profit]" /&gt;</DataField>
      <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
  </Fields>
</DataSet>
</DataSets>
<rd:ReportUnitType>Cm</rd:ReportUnitType>
<rd:ReportID>73334590-ae11-454b-8075-9d8b167ac594</rd:ReportID>
</Report>

You should include your code example. – Miguel-F Jan 24 '13 at 13:21 — Miguel-F, Jan 24 '13 at 13:21

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

Yes, you can do this, you should add one more dummy parent group (parent for GroupA and for GroupB) which will have grouping by group fields from group A and group fields from B. And than in interactive sorting you should choose this group. In you report you have group Category and Subcategory, add also ParentGroup (grouping fields are Category, Subcategory): enter image description here

And delete unnesessary cells which are created by this group.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 12 '13 at 08:08

Roman Badiornyi

1,509
14
28

It can be done using only one group grouping by group fields from both group A and group B. Original groups are not necessary. This solution gives the same result and comes from [Microsoft Docs](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc627509(v=sql.100)?redirectedfrom=MSDN#sorting-rows-based-on-a-complex-group-expression). Both solutions lose advantage of real multiple group levels, e.g. make total within each group level, merge duplicate group values in parent text box. We have to use workarounds to achieve this. – Fenix Feb 24 '20 at 22:05

Interactive Sorting in a Reporting Services report with multiple groupings and toggles

1 Answers1