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:
- In Report Designer, create the spreadsheet “Report 1”.
- Double click on Report1 and stay in cell A1. In the Insert tab*, select the ComboBox element:
- 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.
- Enter “Region” as the list name and label.
- Click on Subset to open the Subset Editor.
- On the General tab, select the dimension Regions.
- Activate the filter on the Hierarchy tab. Select Start Level 1 and End Level 2.
- Click OK.
- Close the Format Control dialog box by clicking OK. The following ComboBox appears:
To create DynaRanges, follow these steps:
- Select D4:D6 and go to Insert. Select the the Horizontal DynaRange.
- As source, use a subset with the second hierarchy level of the Years dimension.
- Highlight C5:E5 and select the Vertical DynaRange from the Insert tab.
- Select Subset as a source.
- On the General tab, select the Demo database and the dimension Regions.
- Activate the filter on the Picklist tab, and check the top Variable box.
- Select Region as variable. The following data is displayed on the spreadsheet:
Add a PALO.DATA function D5:
- Select Query > Paste Data Function…
- In the dialog box, select the database and the cube and check Guess Arguments. Click Paste.
- Right-click D5 and select Format cells from the context menu.
- 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.