Example: Report with List and DynaRanges


This article provides an example of a dynamic report with a List and DynaRanges. For more information on these and other form elements, see the following articles:

Creating a List element

To create a new report with the List form element:

  1. In Designer, create the spreadsheet “Report 2”.
  2. Double click on Report 2 and stay in cell A1. In the Insert tab*, select the List element:
  3.  The Format control dialog is displayed, prompting you to choose the settings for the list:

    Note: there is a constraint for the data sources formula and ODBC query.
  4. Enter “Region” as the list name and label.
  5. Click on Subset to open the Subset Editor.
  6. On the General tab, select the server/database Demo and the dimension Regions.
  7. Activate the filter on the Hierarchy tab and check the box Filter elements by level.
  8. Select Start level 1 and End level 3 and click OK.
  9. Close the Format control dialog box by clicking OK. The following list appears:

Creating DynaRanges

To create DynaRanges, follow these steps:

  1. Select D4:D6 and go to Insert. Click the Horizontal DynaRange icon.
  2. As source, use a subset.
    On the General tab, select the Demo database and the dimension Years.
  3. Activate the filter on the Hierarchy tab and select the second hierarchy level of the Years dimension:

  4. Highlight C5:E5 and click the Vertical DynaRange icon from the Insert tab.
  5. Select  Subset as a source.
  6. On the General tab, select the Demo database and the dimension Regions.
  7. Activate the filter on the Picklist tab, and check the top Variable box.
  8. Select Region as variable and select the option As preselection for the subset in the Behavior field:

  9. The following data is displayed on the spreadsheet:

Add a PALO.DATA function in D5:

  1. Select Query > Paste Data Function…
  2. In the dialog box, select the database and the cube and check Guess Arguments. Click Paste.
  3. Right-click D5 and select Format cells from the context menu.
  4. Set the number format to 0 decimal places and click OK.

On the Design tab, click the Open user mode icon   to see the result:

You can select multiple list items by holding down the CTRL key and clicking on the desired items. For example, in the screenshot below, Germany, France, United Kingdom, and South have been selected:

Save and close Report 2. Go to the Reports module and drag Report 2 onto “Reports” in the report group “Demo Reports”, as shown below:

If you don’t want to make any other adjustments (e.g. variable setting), you can also assign opened spreadsheets and framesets from the Report Designer directly to a folder in the Reports module with File > Publish.

*In this example, we use the default, Simple Ribbon toolbar.