To create a new report, open the Report Designer and create a new spreadsheet called “Report 1”. Double-click on Report 1, stay in cell A1, and navigate to Tools→Form Elements→ComboBox. Enter “Region” as the ComboBox name and click on Subset to open the Subset Editor, as shown below:
Please note the constraint for the data sources formula and ODBC query.
Here, we can apply settings for the ComboBox. In the General tab, under Dimension select “Regions”. Then switch to the Hierarchy tab, activate the Hierarchy filter, select “Start Level 1” and “End Level 2”, and click on OK.
Close the Format Control dialog box by clicking on OK. The following list box will appear:
Enter a horizontal DynaRange into D4:D6. As the source, use a subset with the second hierarchy level of the dimension “Years”.
Highlight C5:E5 and select the “Vertical DynaRange” command. Once again, us a subset as the source. On the General tab, select the Demo database and the Regions dimension.
On the Hierarchy tab, check box to activate the filter as well as the top “Variable” box, as shown in the screenshot below.
Select “Region” as variable. It will 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 the Paste button. Right-click on cell D5 and select Format Cells from the context menu. Set Decimal Places to 0.
By checking View→Designer Preview or clicking the Designer Preview icon, it appears like this:
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 Report 1, 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), spreadsheets or framesets from the Report Designer can be assigned directly to a folder in the Reports module with the command File→Quick Publish.
Now, click on “User preview”. Double-click on Report 1 for a full view of all the data: