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:

To create a new report with the List form element, open the Report Designer and create the spreadsheet “Report 2”. Double click on Report 2, stay in cell A1, and navigate to Tools→Form Elements→List…  The Format Control dialog appears:

Please note the constraint for the data sources formula and ODBC query.

Here, we can apply settings for the list. Enter “Region” as the list name and label, then click on Subset to start the Subset Editor.

On the General tab, select the dimension “Regions”. On the Hierarchy tab, check the box to activate the filter, then check the box “Filter elements by level” and select “Start level 1” and “End level 3” and click OK. Close the Format Control dialog box by clicking OK.

The following list appears:

Select cells D4:D6 and open Tools→ DynaRanges→Horizontal DynaRange. As source, use a subset with the second hierarchy level of the Years dimension.

Next highlight C5:E5 and select the “Vertical DynaRange” command. Source is once again a subset. On the General tab, select the Demo database and the dimension “Regions”. On the Picklist tab, check the box to activate the filter, and check the top Variable box. Select “Region” as variable and select the option “As preselection for the subset” in the Behaviour field, as shown in the screenshot below:

The spreadsheet appear like this:

We just need the PALO.DATA function in cell D5 now. Select Query→Paste Data Function…  In the dialog box, select the database and the cube and check “Guess Arguments”. Then click on “Paste”.

Right-click on D5 and select “Format cells” from the context menu. Set the number format to 0 decimal places and click OK.

Check the box under View→Designer Preview or click the Designer Preview 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, then go into 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), spreadsheets, or framesets from the Report Designer can be assigned directly to a folder in the Reports module with the command File→Quick Publish.