Example of a dynamic calculated slice element

You might use a calculated element to sum the values of the elements in a hierarchy. If you enable users to choose the hierarchy then the calculated element must be dynamic.

This topic is an example of how to create a dynamic calculated element on a slice. This example uses the OLAP metadata hierarchy of the Sample application to display the dimensions of the Analysis cube in a combo box. When you select a dimension, the report displays the value for each element in the hierarchy. In addition, the calculated element displays the sum of all the values.

  1. Create a custom list on the OLAP metadata hierarchy. Specify that the list displays the children of the Analysis cube.
  2. Create a combo box in a report and drag the OLAP metadata list onto it. Rename the combo box dims.
  3. In Design mode, select a dimension other than Measure from the combo box.
  4. Create a slice.
  5. For the Rows hierarchy, select Edit Formula and specify =ReportObjects.dims.attribute1.
    Attribute1 retrieves the name of the hierarchy.
  6. In the 1st rows segment, select Edit list. The List Designer is displayed for the dimension that you selected in the dims combo box. Select any element.
  7. In Selection Settings, select Edit Formula. In the Edit Custom MDX dialog box, specify =ReportObjects.dims.attribute1&"Total".
    Total is displayed in View mode as the name of the calculated element.
  8. Add a second segment to the rows. Select Edit list. The List Designer is displayed for the dimension that you selected in the dims combo box. Select any element.
  9. In Selection Settings, select Edit Formula. In the Edit Custom MDX dialog box, specify =ReportObjects.dims.attribute1&".Levels(1).Members".
    In View mode, this displays the first-level elements of the selected hierarchy.
  10. Add the Measure dimension as the Columns hierarchy.
  11. For the first segment, select Units.
  12. Click the Edit slice settings icon in the toolbar of the General section.
    The Slice Settings Designer is displayed.
  13. Click Add Element and select the Dynamic option.
  14. In the Formula field, specify: =ReportObjects.dims.Attribute1&".[Total]"
  15. In the Expression field, specify =="Sum("&ReportObjects.dims.Attribute1&".Levels(1).Members)"
  16. Drag the slice into the report.
Related topics
  • Custom hierarchies
  • OLAP meta data hierarchy
  • Slices