416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.0

This site makes extensive use of JavaScript.

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

Manual Virtual Table

Hide navigation
RSS
Modified on Thu, 20 Nov 2014 01:38 PM Categorized as Virtual Tables
Click to return to: Documentation | Defining Virtual Tables


Overview

A manual virtual table is directly defined by a user-written SQL statement (i.e. your own custom query).

Note: Your SQL statement/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.

Note: Manual virtual tables does not support CTE (Common Table Expression).

Creating a manual virtual table

To create a manual virtual table:

  1. In the first step of the wizard, select Manual Virtual Table, then click Next.
  2. In the second step of the wizard, select a data connector from the grid.
    Choosing a data connector in the Virtual Table Setup Wizard.
  3. Click Finish. The Virtual Table Designer is displayed in the content area of Dundas Dashboard, minus the Data Sources tab.
  4. Configure general settings for your virtual table.
  5. Go to the Preview tab.
  6. Type your custom query in the Query Statement box. As part of the query statement, you can optionally define and insert manual query parameters. See Using manual parameters for more details.
  7. (Optional) Enter the maximum number of data rows you want to preview in the Maximum Records box.
  8. Click Parse Query and Preview Content to preview the resulting data.
    Preview tab for a manual virtual table.
  9. Go to the Columns tab to see the definitions of the columns chosen by your custom SQL statement.
    Columns tab for a manual virtual table.

Using manual parameters

When you choose to create a manual virtual table, you have the option of defining manual query parameters and inserting them into your custom query statement. Such parameters are public by default and will be accessible during the KPI/dataset design stages.

To add a manual parameter:

  1. Go to the Preview tab of your manual virtual table, then click Add Manual Parameter. The Manual Parameter Setup Wizard is displayed.
    First step of the Manual Parameter Setup Wizard.
  2. In the first step of the wizard:
    • Choose the data type for your parameter. The following data types are supported:
    • Click Next.
  3. In the second step of the wizard:
    • Enter a public name for the parameter in the Dashboard label box.
    • Enter a description for the parameter in the Description box.
    • Select the (Null) allowed for value selection check box if you want to allow Null as a valid value for your parameter.
      Second step of the Manual Parameter Setup Wizard.
    • If the data type of the parameter is DateTime, you can select the Ignore time check box if you want to ignore the time portion of the parameter value.
    • Click Next.
  4. In the third step of the wizard:
    • You have to option of choosing a look-up (virtual) table to provide parameter values and their corresponding display (i.e. name) values. This is an alternative to manually entering parameter values during the dashboard design stage. To set up the look-up table, follow the steps as shown in the article, Defining a Filter.
    • Choose a default value for the parameter.
      Third step of the Manual Parameter Setup Wizard.
  5. (Optional) Click Next to go to the last step of the wizard and specify validation rules for the value of the parameter. See Defining Validation Rules for more details.
  6. Click Finish to close the wizard. The newly created parameter appears in the User Statement Parameters grid. Click the plus sign button in the Parameter setup grid column to insert the parameter into your custom query statement.

A query statement with a manual parameter.

A query statement with a manual parameter.


User-based manual parameters

A user-based manual parameter lets you specify the parameter value based on information about the current Dundas Dashboard user, such as a user name, ID, or group membership. Your manual query can then filter your data depending on the current user (e.g. return the appropriate set of records for each user).

If you selected the User-based filter option in the first step of the Manual Parameter Setup wizard:

User-based manual parameter option.

User-based manual parameter option.


Click Next to go to the Choose Parameter Type screen. Select the type of user information that you want to be passed to the parameter:

  • User Account Name
  • User Account ID
  • List of comma-separated Group Names
  • List of comma-separated Group IDs
  • Language/Culture name
  • Custom attribute name

Tip: A custom attribute is a name-value pair that a Dundas Dashboard administrator can set on a per-user account basis (via the Custom Attributes tab in the Edit Account screen). For example, you may have a custom attribute name called SecurityLevel.

Choose type of user information.

Choose type of user information.


Click Finish to close the wizard. The newly created parameter appears in the User Statement Parameters grid in the Preview tab. Click the plus sign button in the Parameter setup grid column to insert the parameter into your custom query statement.

Click the plus sign button to insert the manual parameter into your query statement.

Click the plus sign button to insert the manual parameter into your query statement.


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.