Example: Report with ComboBoxes and two interdependent vertical DynaRanges

This article provides an example of a dynamic report with two ComboBoxes and two interdependent vertical DynaRanges, first as outer and second as inner DynaRange. In the example are displayed all customers who have nothing ordered in a certain year and in a certain region.

For more information on these and other form elements, see the following articles:

Creating two ComboBoxes

To create a new report with the first ComboBox form element:

  1. In Report Designer, create the spreadsheet “Nested_Dynaranges”.
  2. Double click on Nested_Dynaranges and stay in cell A1. Enter „Year“ and go to A2.

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

    Note: there is a constraint for the data sources formula and ODBC query.

  1. Enter “Year_selection” as the ComboBox name.
  2. Click on Subset to open the Subset Editor.
  3. On the General tab, select the server/database Biker and the dimension Years.
  4. Activate the filter on the Hierarchy tab and check Filter elements by level. Select Start level 2 and End level 2.
  5. Click OK.
  6. Close the Format control dialog box by clicking OK. The following ComboBox appears:

  7. Go to B1 and enter „Region“.
  8. Go to B2. In the Insert tab*, select the ComboBox element:

  9. The Format control dialog is displayed, prompting you to choose the settings for the ComboBox:

  10. Enter “Region_selection” as the ComboBox name.
  11. Click on Subset to open the Subset Editor.
  12. On the General tab, select the server/database Biker and the dimension Customers.
  13. Activate the filter on the Hierarchy tab and check Filter elements by level.
    Select Start level 2 and End level 2.
  14. Click OK.
  15. Close the Format control dialog box by clicking OK. The following ComboBox appears:

Creating two nested interdependent vertical DynaRanges

To create the outer DynaRange, follow these steps:

  1. Highlight the cells D3:G5.  In the Insert tab, select the Vertical DynaRange icon:

    You receive:

  2. As source, use a subset of the dimension Customers and select the following settings in Hierarchy tab:

  3. Select Region_selection as variable.

To create the inner DynaRange, follow these steps:

  1. Highlight the cells E4:F4.  In the Insert tab, select the Vertical DynaRange.
  2. As source, use a subset of the dimension Customers and select the following settings in Hierarchy tab:

  3. Select Dynarange1 as variable.
    Note: When referring from the formula of the dependent DynaRange to its “parent”, you have to use the parent’s name (for example, “DynaRange1”), not a cell reference. Cell references in DynaRange source formulas are static, meaning they are not rewritten as the DynaRange expands.
  4. To display only the customers with 0 orders we select the Data tab and set the following settings:

    Select cube: Orders Months: Year Channels: All Channels
    Orderlines: All Orders Products: All Products Versions: Actual
    Years: Variable „Year_selected“ Customers: nothing (here will be displayed the dimension elements which match the criteria) Measures: Units

    We close the open dialogs with „OK“ and receive the following entries in the spreadsheet:

Add a PALO.DATA function in F4:

  1. Select Query > Paste Data Function…
  2. In the dialog box, select the server/database Biker and the cube Orders. Then check Guess Arguments. Click Paste.
  3. The guessed formula is:
    =PALO.DATAC(“localhost/Biker”,”Orders”,”All Orders”,Year_selected,”Year”,”All Products”,Region_selected,”All Channels”,”Variance”,”Units”)

    Two guessed arguments we should correct: „Region_selected“ and „Variance“.

    The correct formula has to use E4 (here is displayed the base element of the dimension „Customers“) instead of „Region_selected“ and „Actual“ instead of “Variance”:
    =PALO.DATAC(“localhost/Biker”,”Orders”,”All Orders”,Year_selected,”Year”,”All Products”,E4,”All Channels”,”Actual”,”Units”)

On the Design tab, click the Designer preview icon  to see the result:

For performance reasons, a maximum of four elements will appear for each dimension in Designer preview.

To see a full view of all the data close Designer preview and click the Open user mode icon :

Now you can easily change „Year“ and „Region“ to see customers with 0 orders for certain years and certain regions.

 

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