Create a named set in an OLAP PivotTable
When you work with an Online Analytical Processing OLAP PivotTable in Excel, you can create named sets, a flexible feature that you can use to:
-
Group common sets of items that you can re-use, even when those sets are not present in the data.
-
Combine items from different hierarchies in ways that were not 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 that is similar to worksheet formulas.
If you are not familiar with the Multidimensional Expressions (MDX) query language, you can create a named set that is based on items in the rows or columns of your PivotTable. 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.
What do you want to do?
Create a named set based on row or column items
-
Click the OLAP PivotTable for which you want to create a named set.
This displays the PivotTable Tools, adding an Options and a Design tab.
-
On the Options tab, in the Calculations group, click Fields, Items, & Sets, and then click Create Set Based on Row Items or Create Set Based on Column Items.
The New Set dialog box is displayed. If needed, you can resize this dialog box by dragging the sizing handle in the lower-right corner of the dialog box.
-
In the Set name box, type the name that you want to use for the set.
-
To specify the rows that you want to include in the named set, do one or more of the following:
-
To remove a row from the list of items, click the area to the left of the row that you want to select, and then click Delete Row.
-
To add a new row to the list of items, click the area to the left of the row below which you want to add the new row, and then click Add Row.
-
To create a copy of an item, click the area to the left of the row that you want to copy, and then click Copy Row.
-
To move an item to a different location, click the area to the left of the row that you want to move, and then use the Up and Down arrows to move it to the appropriate location.
-
-
By default, items from different levels will be displayed in separate fields in the hierarchy, and the named set replaces the current fields in the row or column area.
-
To display those items in the same field as other items, clear the Display items from different levels in separate fields check box.
-
To keep the current fields displayed in the row or column area, clear the Replace the fields currently in the row area with the new set or Replace the fields currently in the column area with the new set check box. The set will not appear in the PivotTable when you click OK, but it will be available in the PivotTable Field List.
-
-
Click OK to create the named set.
Note:
-
To undo all actions after closing the dialog box, click Undo on the Quick Access Toolbar.
-
You cannot apply any type of 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 only be done by using MDX.
-
Click the OLAP PivotTable for which you want to create a custom named set.
-
On the Options tab, in the Calculations group, click Fields, Items, & Sets, and then click Manage Sets.
The Set Manager dialog box is displayed. If needed, you can resize this dialog box by dragging the sizing handle in the lower-right corner of the dialog box.
-
Click New, and then click Create Set using MDX.
-
In the Set name box, type the name that you want to use for the set.
-
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 that you want to include, and then 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 one or more functions from the available MDX functions that you want to use, and then click Insert. MDX functions are supported by Analysis Services; they do not include VBA functions.
Function arguments are placed inside chevron characters (<< >>). You can replace the placeholder arguments by clicking them, and then typing the valid names that you want to use.
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» ) …
-
-
To test the new MDX definition, click Test MDX.
-
By default, items from different levels will be displayed 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 display those items in the same field as other items, clear the Display items from different levels in separate fields check box.
-
To change the default hierarchy and keep duplicate entries, clear the Automatically order and remove duplicates from the set check box.
-
To keep the current fields displayed in the row or column area, clear the Replace the fields currently in the row area with the new set or Replace the fields currently in the column area with the new set check box. The set will not appear in the PivotTable when you click OK, but it will be available in the PivotTable Field List.
-
-
If you are connected to a SQL Server 2008 Analysis Services cube, a dynamic named set is created by default. This named set automatically recalculates with every update.
-
To prevent the named set from being recalculated with every update, clear the Recalculate set with every update check box.
-
-
Click OK to create the named set.
Note:
-
To undo all actions after closing the dialog box, click Undo on the Quick Access Toolbar.
-
You cannot apply any type of filtering to named sets.
Edit or delete a named set
-
Click the OLAP PivotTable that contains the named set that you want to edit or delete.
-
On the Options tab, in the Calculations group, click Fields, Items, & Sets, and then click Manage Sets.
The Set Manager dialog box is displayed. If needed, you can resize this dialog box by dragging the sizing handle in the lower-right corner of the dialog box.
-
Select the set that you want to edit or delete.
-
Do one of the following:
-
To edit the selected named set, click Edit, and then make the changes that you want.
-
To delete the selected named set, click Delete, and then click Yes to confirm.
-
No comments:
Post a Comment