416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.5

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Creating Reports

Hide navigation
RSS
Modified on Thu, 09 May 2013 05:47 PM Categorized as Level-Intermediate, OLAP, Virtual Cubes
Click to return to: Documentation | Defining Virtual Cubes


Overview

From the Design Explorer, you can create one or more virtual cube reports which can be displayed on an analytical dashboard.

A virtual cube report.

A virtual cube report.


Tip: Before Dundas Dashboard 4.0, reports were defined in a separate tab of the Virtual Cube Designer.

Managing reports

To create a new report:

  1. Ensure that your virtual cube is checked out and any changes to it are saved.
  2. Right-click over this virtual cube in the Design Explorer.
  3. From the menu, click New Report.
    New Report.
  4. Click inside the text box and type a name for the report or press ENTER to accept the default name.
    Type the report name.
  5. You can now edit the report in the Report Designer.

To change the name of a report:

  1. Check out the parent virtual cube, if needed.
  2. Right-click over the report in the Design Explorer.
  3. From the menu, click Rename.
  4. Type the new name for the report, and then press ENTER.

To edit an existing report:

  1. Check out the parent virtual cube, if needed.
  2. Right-click over the report in the Design Explorer.
  3. From the menu, click Edit.
  4. You can now edit the report in the Report Designer.

Note that if the virtual cube is not checked out, the Edit option will still be available; this allows you to view the report but not modify it.

To delete a report:

  1. Check out the parent virtual cube, if needed.
  2. Right-click over the report in the Design Explorer.
  3. From the menu, click Delete.
  4. When prompted to confirm the operation, click Yes.

Here are some other useful operations you can perform on reports:

  • Use the context (⪚ right-click) menu of a report to cut or copy the report, and then paste it to another virtual cube.
  • You can also drag-and-drop a report from one virtual cube to another.
  • Re-order reports within a virtual cube by using drag-and-drop, or by using the Paste After option in the report context menu.

Using the Report Designer

The Report Designer lets you build an analytical report simply by dragging measures, KPIs or dimensions onto the appropriate sections of the report surface.

The Report Designer.

The Report Designer.


Adding cube elements to your report

Use the Cube Tree pane on the left to navigate the available measures, KPIs, and dimensions that you selected in the Definition tab of the Virtual Cube Designer.

Drag cube elements onto the appropriate panes/axes in the designer to build up your report. The functionality here is identical to using the Cube Viewer at the data connector stage. The primary difference is that the Cube Viewer lets you browse the entire cube, whereas in the Report Designer you browse only the subset of elements chosen through the Definition tab.

See Using the Cube Viewer for more details.

Editing the list of selected cube elements

From the Report Designer, you can edit the list of slicer dimensions, categorical dimensions, grouping dimensions, or measures as follows:

  1. Move your mouse over the dimension or measure in the appropriate pane/axis. A menu button appears.
  2. Click the menu button.
    Slicer dimension menu.
  3. From the menu, choose Edit. The Edit dimension (or Edit Measures) dialog is displayed, which lets you update the list of dimension members/measures selected for the corresponding report pane.
    Select dimension members.

Change hierarchy

Alternatively, for dimensions, choose Change Hierarchy from the menu to substitute a different dimension hierarchy altogether:

Select a different hierarchy.

Select a different hierarchy.


Selecting dates using calendars

If a time dimension hierarchy is displayed and has a date mapping set up, the Calendar option in the menu can be chosen to select a date or a range of dates using calendar controls.

Select a range of dates using calendars.

Select a range of dates using calendars.


Drill-down to a specific level

The menu for a categorical dimension or a grouping dimension offers additional options, such as the ability to drill-down or drill-up to a specific level in the dimension hierarchy.

Menu for a categorical dimension.

Menu for a categorical dimension.


The OLAP grid, after drill-down to the Product Group level.

The OLAP grid, after drill-down to the
Product Group level.


Filtering and sorting

The menu for a categorical dimension or a grouping dimension also lets you filter or sort dimension members based on measure values:

Menu for a Product dimension.

Menu for a Product dimension.


From the menu, choose Sort/Filter. The Sorting/Filtering dialog is displayed:

The Sorting/Filtering dialog.

The Sorting/Filtering dialog.


  • Use the Filter option to filter your dimension based on measure value comparisons.
  • Use the Top/Bottom option to display the top-most or bottom-most dimension members based on measure values. For example, use this option to find the top 5 best-selling products.
  • Use the Sort option to sort your dimension members based on measure values. If the Break Hierarchy option is also chosen, the dimension members will be sorted without maintaining the hierarchy between members.

Note: If you use the Top/Bottom option or the Sort option (with Break Hierarchy chosen as well), the small triangles for drilling up or down the hierarchy will be hidden from the OLAP chart and grid.

Example: Filtering measure values

Consider a report that displays the following:

  • Categorical dimension: Time
  • Grouping dimension: Product
  • Measure: Sale Amount

Report showing sale amounts with totals.

Report showing sale amounts with totals.


To have the report display only the rows where the total Sale Amount is greater than or equal to 1,000,000 dollars, use the Time dimension's Sort/Filter dialog and set it up as follows:

  • Select the Filter check box.
  • Condition: Greater Than or Equal
  • Condition value: 1000000
  • Measure: Sale Amount

Using the Sort/Filter dialog to filter measure values.

Using the Sort/Filter dialog to filter measure values.


The resulting report shows only the rows from calendar year 2000 to 2003 (which satisfy the filter condition).

The report after measure value filter is applied.

The report after measure value filter is applied.


Note: When filtering is enabled, the totals rows/columns are not displayed.

Report options

Click the Options button in the toolbar of the Report Designer in order to set various report options. For example, use the Enabled options to decide which toolbar buttons (⪚ Freeze or Transpose) should appear when the report is viewed by users from an analytical dashboard.

The Slicer As Subquery advanced option lets you include your choices on the slicer axis as part of the report subquery. Totals that are displayed in the report will be updated to reflect the changes to the subquery. Selecting this option can help to improve performance but it should be used with the understanding that you may get different results if you use hierarchies from the same dimension.

Report options.

Report options.


Axis context menus

The Slicer, Measures, Categorical and Grouping panes/axes each have a context menu which you can access by right-clicking in the pane, or clicking the menu button in the top-right corner.

Context menu.

Context menu.


  • Use the Edit query option to enter query text for the axis/pane which will be used to define a manual/custom MDX query.
  • Click the disable item to disable the adding or removing of dimensions/measures.
  • Click the Lock Sort/Filter item to lock the axis/pane with respect to sorting and filtering.

Manual MDX queries

Creating a manual query

The report editor lets you specify a manual/custom MDX query by entering your own query text in the Categorical, Grouping, or Slicer axis.

For example, enter manual query text for the Categorical axis as follows:

  1. Right-click over the axis (or click its menu button).
  2. From the menu, choose Edit query.
    Edit query.
  3. The Categorical axis is replaced with a query text box, which lets you enter your custom query text.
    Query text box.
    Tip: You can drag dimension members from the cube tree to the query text box. You can also paste text from your Windows clipboard to the query text box.
  4. Once you are done writing the query text, click the Apply query changes (checkmark) button to accept the changes. Or, click the Cancel query edit button to close the query text box without making any changes.

Some notes:

  • The query text for the Categorical axis corresponds to the Columns axis in an MDX query.
  • The query text for the Grouping axis corresponds to the Rows axis in an MDX query.
  • The query text for the Slicer axis corresponds to the Where clause in an MDX query.
  • The Measures axis does not provide the ability edit its query - measures enumerated here are added to the Rows axis as the last member in the tuple.

A manual MDX query looks like this (example):

SELECT {} ON COLUMNS, {} ON ROWS FROM [Sonatica] WHERE {}

Simple queries

To create a simple query, just enter the query text for each axis and apply the query.

Note: If you add a NONEMPTY MDX expression to the query, the Empty option in the report editor toolbar will be de-selected, and the Report Options dialog will disable the changing of the Empty option.

Parameterized queries

Types of parameters

The following types of query parameters can be used in a manual MDX query:

  1. String - A string value passed as a parameter (where supported) to the MDX query.
  2. DateTime - A datetime value assigned to the parameter value.
  3. Numeric - A numeric value passed to the parameter.
  4. Bool - A boolean value passed to the parameter.
  5. Dimension - A string value which represents a MDX expression for selected members is passed to the parameter. The dimension parameter must be used in conjunction with StrToXXX MDX functions.
  6. Measure - Same as a dimension parameter except that it allows the selection of measures only.

Adding a query parameter

The bottom section of the Report Designer lets you add, edit or delete query parameters. Query parameters can be attached to dashboard parameters (and parameter UI controls) during the dashboard design stage.

Click the top-left arrow button to expand or collapse the Query Parameters section:

Query parameters section.

Query parameters section.


Once created, parameters can be referenced in query text by combining the parameter name and the prefix, DD. For example, a parameter named Product can be referenced in query text as: <DD:Product>. This is referred to as a parameter token.

For convenience, there are two ways to insert a parameter token into your query text:

  1. Drag the parameter from the Query Parameters grid and drop it onto a query text box.
  2. Click the Copy parameter token to clipboard button in the Query Parameters grid to copy the parameter token text to your Windows clipboard. Then go to your query text box, do a right-click, and choose Paste.

A dimension parameter.

A dimension parameter.


Query text using a parameter token.

Query text using a parameter token.


Using CURRENTMEMBER with dimension parameters

The CURRENTMEMBER function for dimension parameters is supported. This is done by inserting <DD:HIERARCHY(Data Filter1)> into the query text (as an example), where Data Filter1 is a parameter name.

Here is an example of query text that uses two parameters and a CURRENTMEMBER expression:

GENERATE(strtoset(), TOPCOUNT(.CURRENTMEMBER*strtoset(),3, [Measures].[Internet Sales Amount]))

Changing dimension or measures parameters

There are several ways to change a dimension parameter:

  1. Use drag-and-drop from the cube tree or another axis to change the current parameter hierarchy in the same way that it is done with regular reports which don't use manual queries.
  2. Right-click over the parameter button in the axis and choose Change Hierarchy.
    Change parameter hierarchy.
  3. Right-click over the parameter button (or double-click it) to select different members.

Query scope calculated measures

There is no way to create a measure directly in the query text using a WITH expression. Instead, the measure should be defined in the Calculations tab so that the query generator can add it to the query automatically.

Protection functionality

By default, if axis controls and the cube tree are added to a dashboard, they will allow drag-and-drop of dimensions to an axis that contains a manual query, and this will produce crossjoins with query content.

If a manual query cannot be combined with the other dimensions, the “Disable add/remove dimensions” option should be used. If dimension member selection cannot be modified, the dimension descriptor state should be changed to Read only.

Report limitations

There some report limitations when using manual MDX queries:

  • Paging, totals, and non-empty functionality may not work exactly in the same way as with regular reports.
  • Drill-down to member functionality is not available.

Related topics


Click to return to: Documentation | Defining Virtual Cubes

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2016 Dundas Data Visualization, Inc.