Exercise - creating the RP005 sample report template

RP005 is a template, included with the Best Practices sample database. This topic describes how to create the same template.

For general instructions to create a report from a template, see:

Creating a report from a report template.

For a detailed exercise, using the RP005 template, see:

Exercise - creating a report from the RP005 sample template.

This exercise involves these tasks:

  • Placing and specifying the combo boxes and hyperblocks
  • Creating report variables
  • Creating a CELLGET formula
  • Using the Advanced pane of the List Designer to replace all references to report objects with references to variables.

To create the RP005 template:

  1. In the Report Catalog, right-click the Report Templates folder and select New > Report Template.
    The Create Report dialog box is displayed.
  2. Specify a name for the template and click OK.
  3. Double-click the template in the Report Catalog.
    In Design Mode, a blank spreadsheet is displayed.
  4. Click cell C7 and click Combo Box on the Objects tool bar.
    A combo box is created in C7.
  5. Create combo boxes in cells C9 and C11.
  6. Open the Database Structure pane and expand the Analysis cube.
  7. Drag the Period dimension onto the combo box in C7.
  8. Double-click the combo box in C7.
    The Object Properties dialog box is displayed.
  9. Name the combo box lv_time.
  10. Drag the Region dimension onto the combo box in C9.
  11. Name the combo box lv_region.
  12. Drag the Measure dimension onto the combo box in C11.
  13. Name the combo box lv_measure.
  14. Select cells E13 and F13 and drag the Product dimension to E13.
    A hyperblock, based on the Product dimension, is created in cells E13:F13.
  15. Drag the POS dimension onto cell F12.
    A hyperblock, based on the POS dimension, is created in cells F12:F13.

    The Product and POS hyperblocks overlap at cell F13.

  16. Open the Accessories pane and create and specify these report variables:
    Variable Specify with
    trv_alias Best Practices OLAP
    trv_cube Analysis
    trv_time_dim [period]
    trv_time_hie [period]
    trv_region_dim [region]
    trv_region_hie [region]
    trv_measure_dim [measure]
    trv_measure_hie [measure]
    trv_analyzedX_dim [pos]
    trv_analyzedX_hie [pos]
    trv_analyzedY_dim [product]
    trv_analyzedY_hie [product]
  17. In cell F13, create this CELLGET formula:
    =CELLGET(ReportVariables.trv_alias.Text,ReportVariables.trv_cube.Text,E13,F12,ReportObjects.lv_time.Text,ReportObjects.lv_region.Text,ReportObjects.lv_measure.Text)

    The formula returns a value in cell F13.

  18. Click the border of the Product hyperblock.
  19. Open the Advanced pane of the List Designer.
  20. In the General section, double-click Best Practices OLAP and select <Edit Formula> from the list which is displayed.
    The Edit Formula dialog box is displayed.
  21. In the Edit Formula dialog box, expand Report Variables and double-click trv_alias.
    =ReportVariables.trv_alias.Text is displayed in the right-hand pane.
  22. Click OK.
  23. In the General section of the Advanced pane, use the Edit Formula dialog box to specify these assignments:
    General section of Advanced Pane of List Designer Assignment in Edit Formula dialog box
    Cube =ReportVariables.trv_cube.text
    Dimension =ReportVariables.trv_analyzedY_dim.text
    Hierarchy =ReportVariables.trv_analyzedY_hie.text
  24. Click the border of the POS hyperblock.
    General section of Advanced Pane of List Designer Assignment in Edit Formula dialog box
    Cube =ReportVariables.trv_cube.text
    Dimension =ReportVariables.trv_analyzedY_dim.text
    Hierarchy =ReportVariables.trv_analyzedY_hie.text
  25. Specify these assignments in the Advanced pane of the List Designer:
    General section of Advanced Pane of List Designer Assignment in Edit Formula dialog box
    Alias =ReportVariables.trv_alias.text
    Cube =ReportVariables.trv_cube.text
    Dimension =ReportVariables.trv_analyzedX_dim.text
    Hierarchy =ReportVariables.trv_analyzedX_hie.text
  26. Click the border of the Time combo box and specify these assignments in the Advanced pane of the List Designer:
    General section of Advanced Pane of List Designer Assignment in Edit Formula dialog box
    Alias =ReportVariables.trv_alias.text
    Cube =ReportVariables.trv_cube.text
    Dimension =ReportVariables.trv_time_dim.text
    Hierarchy =ReportVariables.trv_time_hie.text
  27. Repeat step 26 for the Region combo box but assign Dimension and Hierarchy to the Region variables.
  28. Specify the correct assignments for the Measure combo box.
  29. Save the report template.
  30. In the Report Catalog, right-click the template and select Report Parameters.
    The Report Parameters dialog box is displayed.
  31. Click Add a Report Parameter.
  32. In the Variable field, expand Report Variables and select trv_alias.
  33. In the Type column, select Alias as the type.
  34. Add a parameter for the cube, selecting Cube as the type.
  35. Add parameters for each dimension and hierarchy for which you created a variable. Select Dimension or Hierarchy as the Type as appropriate.
  36. Optionally, use the right-pointing arrow in the tool bar to indent each hierarchy parameter beneath its dimension.
    This will automatically fill each hierarchy parameter when a user specifies its dimension parameter in the Report Wizard.
  37. Click OK.
  38. Save the template.
  39. Test the template by creating a report.
    We recommend that you work through the exercise, using your template, instead of the RP005 template supplied.
    See Exercise - creating a report from the RP005 sample template
Parent topic: Creating a report template