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.0

This site makes extensive use of JavaScript.

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

Previewing a Virtual Table

Hide navigation
RSS
Modified on Wed, 27 Mar 2013 11:59 AM Categorized as Data Preparation, Virtual Tables
Click to return to: Documentation | Defining Virtual Tables


Overview

This article shows how to use the Preview tab in the Virtual Table Designer to view the SQL query representing your (standard) virtual table and preview a subset of the resulting data. You can use this preview option to quickly verify the definition of your virtual table. From the Preview tab, you can also modify or replace the generated SQL query with your own custom query (Manual Override toolbar option).

The Preview tab is divided into three sections:

  • A top section displaying the SQL query statement that represents your virtual table.
  • A middle section displaying column filters defined in the Columns tab or inherited from another virtual table.
  • A bottom section for previewing the data from your virtual table.

A horizontal splitter bar separates each section. Move a splitter bar up or down to adjust the vertical height of each section.

Previewing a virtual table

To preview your virtual table:

  1. Select the Preview tab in the Virtual Table Designer.
  2. (Optional) Enter the maximum number of records you want to preview from your virtual table in the Maximum records box.
  3. Click Generate Query and Preview Data. The SQL query statement is updated in the Query Statement text area and the data records from your virtual table are displayed in a grid at the bottom of the tab.

Previewing a virtual table.

Previewing a virtual table.


Note: If your virtual table definition has missing or invalid relationships, you will not be able to preview the data, and a warning message will be displayed in this tab.

Virtual table query filters

From the middle section of the Preview tab, you can view and optionally modify any of the filters that you defined using the Columns tab. Such filters, regardless of whether they are public or private, are classified as internal query filters and are listed in the Internal Query Filters grid. To modify one of these filters, click the Set value filtering button in the Action grid column for the filter. This will launch the Data Filter Setup Wizard. See Defining a Filter for details on reconfiguring the filter.

If your virtual table is based on another virtual table that has defined its own public filters, you will be able to see and configure these inherited filters from the External Child Structure Filters grid, which also appears in the middle section of the Preview tab.

Internal and external (inherited) filters displayed in the Preview tab.

Internal and external (inherited) filters displayed in the Preview tab.


Using a custom query (Manual Override toolbar option)

From the Preview tab in the Virtual Table Designer, you can modify or replace the generated query representing your virtual table.

This Manual Override option is not available in the following cases:

  • Your virtual table is based on other virtual tables.
  • Your virtual table defines a filter.
  • Your virtual table is based on a stored procedure.

Note: After using the manual override option, some of the functionality in the Virtual Table Designer will become unavailable for your virtual table. For example, the Data Sources tab will be removed and the Columns tab will become read-only for your virtual table. This operation cannot be undone.

Warning: If you have existing KPIs or datasets that are based on this standard virtual table, note that performing a manual override (without choosing the option to preserve compatibility) will cause those KPIs or datasets to become incompatible with the virtual table and force you to re-create all of those KPIs/datasets. To avoid this possibility, it is recommended to follow the procedure below before starting the override:

  1. Make a copy of the standard virtual table.
  2. Perform the manual override on the copied virtual table.
  3. Rewire each of the KPIs or datasets that depend on the original virtual table to use the copied virtual table instead.


To perform a manual override:

  1. Select the Preview tab in the Virtual Table Designer. The Query Statement text area in the top portion of the tab shows the SQL query that represents your virtual table.
  2. Click the Manual Override button in the Dundas Dashboard Toolbar.
    The Manual Override button.
  3. You will be prompted with a Confirmation dialog having three options:
    • Click Preserve compatibility if you want existing KPIs and datasets that are based on this virtual table to still work after you have done the manual override. The caveat for this option is that the SQL query uses unfriendly aliases for columns.
      Preserve compatibility.
    • Click Readable aliases if you want to have readable aliases for columns in the SQL query. This will cause any existing virtual tables, KPIs or datasets that are based on this virtual table to become incompatible, which will require you to re-create the KPIs/datasets or rewire them as described earlier.
    • Click Cancel if you don't want to perform the manual override at this time.
  4. Type your new or modified query in the Query Statement text area. Note that your custom query cannot contain an ORDER BY clause. Also, it is not recommended to add any comments to the query. If you must have comments, using comment blocks such as /* my comment */ may work.
  5. Click Parse and Preview Content to preview the data generated by your modified query.
  6. Click Save from the Dundas Dashboard Toolbar.
    The Save button.

You have modified your virtual table to use a custom query and previewed the resulting data.

Related topics


Click to return to: Documentation | Defining Virtual Tables

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

Copyright © 2009-2014 Dundas Data Visualization, Inc.