Create a named set in an OLAP PivotTable report

When you work with an Online Analytical Processing (OLAP) PivotTable in Excel, you can create named sets, a flexible feature you can use to:

  • Group common sets of items that you can re-use, even when those sets aren't present in the data.

  • Combine items from different hierarchies in ways that weren't possible in earlier versions of Excel, often referred to as asymmetric reporting.

  • Create a named set by using custom Multidimensional Expressions (MDX), a query language for OLAP databases that provides calculation syntax similar to worksheet formulas.

If you're not familiar with the MDX query language, you can create a named set based on items in the rows or columns of your PivotTable report. To learn more about the MDX query language, see Querying Multidimensional Data.

If you are familiar with the MDX query language, you can use MDX to create or modify a named set.

Create a named set based on row or column items

  1. Click the OLAP PivotTable report.

  2. On the Analyze tab, click Fields, Items, & Sets > Create Set Based on Row Items or Create Set Based on Column Items.

    Fields, Items, & Sets

  3. In the Set name box, type the name you want to use for the set.

  4. To pick the rows of items you want to include in the named set, do one or more of the following:

    • To remove a row, click to the left of the row you want to select, and click Delete Row.

    • To add a new row, click to the left of the row you want to add the new row under, and click Add Row.

    • To create a copy of an item, click to the left of the row you want to copy, and click Copy Row.

    • To move an item, click to the left of the row you want to move, and use the Up and Down arrows.

  5. Items from different levels are shown in separate fields in the hierarchy, and the named set replaces the current fields in the row or column area.

    • To show those items in the same field as other items, uncheck the Display items from different levels in separate fields box.

    • To keep the current fields shown in the row or column area, uncheck the Replace the fields currently in the row area with the new set box or the Replace the fields currently in the column area with the new set box. The set won't appear in the PivotTable when you click OK, but it will be available in the PivotTable Fields List.

  6. Click OK to create the named set.

    Keep in mind that you can't apply filtering to named sets.

Use MDX to create a named set

Important:  When you use MDX to create a named set or to modify the MDX definition of an existing named set, any additional modifications can be done only by using MDX.

  1. Click the OLAP PivotTable report.

  2. On the Analyze tab, click Fields, Items, & Sets > Manage Sets.

    Fields, Items, & Sets

  3. Click New > Create Set using MDX.

  4. In the Set name box, type the name you want to use for the set.

  5. To set the MDX definition for the named set, do any of the following:

    • In the Set definition box, type or paste a copied MDX definition.

    • On the Fields and Items tab, select the field list entry you want to include, and click Insert.

      You can also drag a field list entry to the Set definition box, or double-click a field list entry.

      Available Field List entries

      Field List entry

      Examples of MDX generated by using the Adventure Works cube

      Dimension

      [Product]

      Attribute hierarchy (includes All member)

      [Product].[Category]

      Attribute hierarchy level (does not include All member)

      [Product].[Category].[Category]

      Member from attribute hierarchy

      [Product].[Category].&[4]

      User hierarchy

      [Product].[Product Categories]

      User hierarchy level

      [Product].[Product Categories].[Category]

      Member from user hierarchy

      [Product].[Product Categories].[Category].&[4]

      Measure

      [Measures].[Internet Sales Amount]

      Calculated measure

      [Measures].[Internet Ratio to Parent Product]

      Named set

      [Core Product Group]

      KPI value

      KPIValue(“Product Gross Profit Margin)

      KPI goal

      KPIGoal(“Product Gross Profit Margin”),

      KPI status

      KPIStatus(“Product Gross Profit Margin”)

      KPI trend

      KPITrend(“Product Gross Profit Margin”)

      Member property from user hierarchy

      [Product].[Product Categories].Properties(“Class” )

      Member property from attribute hierarchy

      [Product].[Product].Properties(”Class”)

    • On the Functions tab, select from the available MDX functions you want to use, and click Insert. MDX functions are supported by Analysis Services; they do not include Excel VBA functions.

      Function arguments are placed inside chevron characters (<< >>). You can replace the placeholder arguments by clicking them and typing the names you want to use.

      Some examples of MDX functions:

      ADDCALCULATEDMEMBERS( «Set» )
      AGGREGATE( «Set»[, «Numeric Expression»] )
      «Level».ALLMEMBERS
      «Hierarchy».ALLMEMBERS
      ANCESTOR( «Member» «Level» )
      ANCESTOR( «Member», «Distance» )
      ANCESTORS( «Member», «Distance» )
      ANCESTORS( «Member», «Level» )
      ASCENDANTS( «Member» )
      AVG( «Set»[, «Numeric Expression»] )
      AXIS( «Numeric Expression» )
      BOTTOMNCOUNT( «Set», «Count»[, «Numeric Expression»] )
      BOTTOMPERCENT( «Set», «Percentage», «Numeric Expression» )
      BOTTOMSUM( «Set», «Value», «Numeric Expression» ) …

  6. To test the new MDX definition, click Test MDX.

    Items from different levels are shown in separate fields in the hierarchy, fields are ordered and duplicate entries are automatically removed (because HIERARCHIZE and DISTINCT are added to the set), and the named set replaces the current fields in the row or column area.

    • To show those items in the same field as other items, uncheck the Display items from different levels in separate fields box.

    • To change the default hierarchy and keep duplicate entries, uncheck the Automatically order and remove duplicates from the set box.

    • To keep the current fields shown in the row or column area, uncheck the Replace the fields currently in the row area with the new set box or the Replace the fields currently in the column area with the new set box. The set won't appear in the PivotTable when you click OK, but it will be available in the PivotTable Fields List.

    • If you're connected to a SQL Server Analysis Services cube, a dynamic named set is created. This named set automatically recalculates with every update.

      To prevent the named set from being recalculated with every update, uncheck the Recalculate set with every update box.

  7. Click OK to create the named set.

Note: Keep in mind that you can't apply filtering to named sets.

Edit or delete a named set

  1. Click the OLAP PivotTable report.

  2. On the Analyze tab, click Fields, Items, & Sets, and then click Manage Sets.

    Fields, Items, & Sets

  3. Select the set you want to edit or delete.

  4. Do one of the following:

    • To edit the named set, click Edit, then make the changes you want.

    • To delete the named set, click Delete, and click Yes to confirm.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×