Example: Report with ComboBox and DynaRanges


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

Creating a ComboBox element

To create a new report with the ComboBox form element:

  1. In Report Designer, create the spreadsheet “Report 1”.
  2. Double click on Report1 and stay in cell A1. In the Insert tab*, select the ComboBox element:

  3.  The Format Control is displayed, prompting you to choose the settings for the ComboBox:

    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 dimension Regions.
  7. Activate the filter on the Hierarchy tab. Select Start Level 1 and End Level 2.
  8. Click OK.
  9. Close the Format Control dialog box by clicking OK. The following ComboBox appears:

Creating DynaRanges

To create DynaRanges, follow these steps:

  1. Select D4:D6 and go to Insert. Select the the Horizontal DynaRange.
  2. As source, use a subset with the second hierarchy level of the Years dimension.
  3. Highlight C5:E5 and select the Vertical DynaRange from the Insert tab.
  4. Select  Subset as a source.
  5. On the General tab, select the Demo database and the dimension Regions.
  6. Activate the filter on the Picklist tab, and check the top Variable box.
  7. Select Region as variable. The following data is displayed on the spreadsheet:

Add a PALO.DATA function 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 Designer Preview icon  to see the result:

The Region ComboBox will now allow you to select each sub-region:

For example, by selecting “North”, it will look like this:

Save and close Report1, then  go into the Reports module and drag Report 1 onto Hardware:

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

Now, click on User preview. Double-click on Report 1 for a full view of all the data:

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