Grouping data with new rows, filtering data, and recalculating data: Derived elements

You may want to look at report or document data in a different way by grouping some report rows into a single row and renaming the new group you have created. For example, if your report displays profit data from 2005, 2006, 2007, and 2008 on separate rows, you might want to group the 2005, 2006, and 2007 rows together into a single row on the report, and name that new row Profits Through 2007.

When you are finished analyzing the report, you can save the report in your My Reports folder. You can also save your new group of report rows as a separate object called a derived element, to be used again on a different report. To use a derived element on another report, the other report must contain the attribute that the derived element was originally based on. For example, using the example above, the derived element was made up of the elements 2005, 2006, and 2007. These elements are part of the Year attribute. To use the Profits Through 2007 derived element on another report, that report must contain the Year attribute.

For an introduction to derived elements with examples and images, see the MicroStrategy OLAP Services Guide.

Prerequisites

•  

• You must own the MicroStrategy OLAP Services product.

• You must have access to a report that is connected to an Intelligent Cube. If you do not, then you must create one. To create an Intelligent Cube report see Creating a report that accesses an Intelligent Cube.

To add a new derived elements group

•  

1. Open the Intelligent Cube report.

• Click Create Report.

• Click Choose Intelligent Cube.
  
  Note: The Intelligent Cube must be created in Desktop. See the MicroStrategy OLAP Services Guide for more information.

• Locate and then click the Intelligent Cube you wish to use in your report. Click OK. The report template opens.

• Drag and drop the objects in the Report Objects pane to the report grid and then run the report.

2. Right click on the object heading for which you wish to create a new derived elements group, and then click Derived Elements. The Derived Elements dialog box opens.

3. Click New and then Group. The New Group text box will appear.

4. Rename the new group so that you can locate it later.

5. Add elements to the group under the Definition tab. Use the arrows to add or remove elements from the list.
   
   Note: Use the Search for: field to find a specific element.

6. Click the Properties tab. Choose one of the options in the Element behavior section:

• Consolidate items into one element

• Keep individual items separate

7. Choose one of the options from the Subtotal behavior drop-down list:

• Use this element when calculating subtotals

• Use the individual items that comprise this element when calculating subtotals

• Do not use this element when calculating subtotal

8. Choose one of the options from the All Other behavior section:

• Do not include individual items in the All Other element

• Include individual items in the All Other element

9. Click OK to apply the changes and close the dialog box. You should see your new group in the report grid.
   
   Note: Clicking the icon also allows you to save your new group for use in other reports.

To add a new derived elements calculation

•  

1. Open the Intelligent Cube report.

• Click Create Report.

• Click Choose Intelligent Cube.
  
  Note: The Intelligent Cube must be created in Desktop. See the MicroStrategy OLAP Services Guide for more information.

• Locate and then click the Intelligent Cube you wish to use in your report. Click OK. The report template opens.

• Drag and drop the objects in the Report Objects pane to the report grid and then run the report.

2. Right click on the object heading for which you wish to create a new derived elements calculation, and then click Derived Elements. The Derived Elements dialog box opens.

3. Click New and then Calculation. The New Calculation text box will appear.

4. Rename the new calculation so that you can locate it later.

5. Click the Elements link under the Definition tab to add elements to the calculation. Use the arrows to add or remove elements from the list.
   
   Note: Use the Search for: field to find a specific element.

6. Choose a function from the Function drop-down list to use it for all the components of the new calculation. The selected function is displayed in the Preview area.

7. Click the Groups link under the Definition tab to add groups to the calculation. Use the arrows to add or remove groups from the list.

8. Click the Numbers link under the Definition tab to specify a number for the calculation.

9. Click the Properties tab.

10. Choose one of the options in the Element behavior section:

• Consolidate items into one element

• Keep individual items separate

11. Choose one of the options from the Subtotal behavior drop-down list:

• Use this element when calculating subtotals

• Use the individual items that comprise this element when calculating subtotal

• Do not use this element when calculating subtotal

12. Choose one of the options from the All Other behavior section:

• Do not include individual items in the All Other element

• Include individual items in the All Other element

13. Click OK to apply the changes and close the dialog box. You should see your new calculation in the report grid.
    
    Note: Clicking the icon also allows you to save your new calculation for use in other reports.

To add a new derived elements filter

•  

1. Open the Intelligent Cube report.

• Click Create Report.

• Click Choose Intelligent Cube.
  
  Note: The Intelligent Cube must be created in Desktop. See the MicroStrategy OLAP Services Guide for more information.

• Locate and then click the Intelligent Cube you wish to use in your report. Click OK. The report template opens.

• Drag and drop the objects in the Report Objects pane to the report grid and then run the report.

2. Right click on the object heading for which you wish to create a new derived elements filter, and then click Derived Elements. The Derived Elements dialog box opens.

3. Click New and then Filter. The New Filter text box will appear.

4. Rename the new filter so that you can locate it later.

5. Choose Qualify to filter on the attribute forms of the selected attribute.

6. Select the form from which you like to qualify from the drop-down list, then select an operator, and finally enter a value in the text box.

7. Choose Select to filter on the attribute elements of the selected attribute.

8. Select In List or Not In List from the drop-down list.

9. Select the elements on which you would like to filter and use the arrows to add or remove groups from the list.

10. Click the Properties tab.

11. Choose one of the options in the Element behavior section:

• Consolidate items into one element

• Keep individual items separate

12. Choose one of the options from the Subtotal behavior drop-down list:

• Use this element when calculating subtotals

• Use the individual items that comprise this element when calculating subtotals

• Do not use this element when calculating subtotals

13. Choose one of the options from the All Other behavior section:

• Do not include individual items in the All Other element

• Include individual items in the All Other element

14. Click OK to apply the changes and close the dialog box. You should see your new filter in the report grid.
    
    Note: Clicking the icon also allows you to save your new filter for use in other reports.

Related topics

•  

• Creating a report that accesses an Intelligent Cube