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.

Programmatic Virtual Table

Hide navigation
RSS
Modified on Tue, 01 Apr 2014 08:08 AM Categorized as Virtual Tables
Click to return to: Documentation | Defining Virtual Tables


Overview

&dw; lets you create a programmatic virtual table which is based on a stored procedure or function but there are some limitations to be aware of:

  • You cannot use such a virtual table with the caching feature if the virtual table has a public parameter.
  • You cannot define a standard dimension based on such a virtual table.
  • A virtual table based on a stored procedure will result in slower performance than a virtual table that is based on a database table, view, or function.
  • Default parameter values are not supported for functions. All parameter values of a function must be supplied.

Warning: A Stored Procedure-based Virtual Table has additional overhead compared to using tables, views, or table-valued functions. You can find more information here.

Creating a virtual table based on a stored procedure or function

Step 1: Create the virtual table

To create a virtual table based on a stored procedure or function:

  1. In the first step of the Virtual Table Setup Wizard, select Programmatic Virtual Table, then click Next.
  2. In the second step of the Virtual Table Setup Wizard, select a data connector from the grid. Note that only data connectors that support stored procedures or functions are displayed in the grid.
  3. In the third step of the wizard, select a stored procedure or function from the grid by clicking a check box in the Select column.
    Choosing stored procedures and functions in the Virtual Table Setup Wizard.
  4. Click Finish. The Virtual Table Designer is displayed in the content area of &dw;, minus the Data Sources tab.
  5. Select the General tab to configure general settings for your virtual table.

Step 2: Configure stored procedure or function parameters

Configure each of the parameters for your stored procedure or function as follows:

  1. Select the Preview tab.
    Preview tab for a virtual table based on a stored procedure or function.
  2. In the Stored Procedure Parameters grid, each row displays the information for a particular parameter of your stored procedure or function. Click the Parameter setup button in a grid row if you want to make the corresponding stored procedure (or function) parameter public, or if you want to assign a default value to the parameter. The Parameter setup button launches the Stored Procedure Parameter Setup Wizard. A parameter must be public in order to be accessible at the Dataset or KPI design stage. During Dataset or KPI design, the stored procedure or function parameter can also be made public to dashboard designers and ultimately connected to a dashboard parameter.
  3. If your stored procedure/function parameter is of type string, the first step of the Stored Procedure Parameter Setup Wizard lets you choose between a standard parameter and a user-based parameter. A user-based parameter lets you set the parameter value based on user information. To configure a user-based parameter, see the next section. Otherwise, select Standard Parameter and click Next.
    Choose standard or user-based parameter.
  4. In the Define parameter step of the Stored Procedure Parameter Setup Wizard, perform the following:
    • To make the stored procedure or function parameter public:
      • Select the Public check box.
      • 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 the parameter value to be set to Null.
        The first step of the Stored Procedure 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.
  5. In the Set up Look-up Table and Assign Default Values step of the wizard:
    • You have to option of choosing a look-up (virtual) table to provide parameter values and their corresponding display (&ie; 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.
    • Enter a default value for your parameter.
  6. (Optional) Click Next to go to the last step of the wizard and add validation rules for the value of the stored procedure or function parameter. Validation of parameter values is useful for ensuring that the structure of the recordset produced by a stored procedure or function is always consistent. See Defining Validation Rules.
  7. Click Finish to close the wizard. The Stored Procedure Parameters grid displays the updated attributes and default value for your parameter.
    The updated Stored Procedure Parameters grid.
  8. (Optional) Enter the maximum number of data rows you want to preview in the Maximum Records box.
  9. Click Discover and Preview Data to preview the resulting data.
  10. Select the Columns tab to see the definitions of the columns generated by the stored procedure or function.

User-based parameters

If your stored procedure or function has a string parameter, you can choose to set the parameter value based on information about the current Dundas Dashboard user, such as a user name, ID, or group membership. Your stored procedure or function can then filter your data depending on the current user (e.g. return the appropriate set of records for each user).

To set up a user-based parameter:

  1. Click the Parameter setup button which corresponds to your string parameter. The Stored Procedure Parameter Setup Wizard is displayed.
    Parameter setup button.
  2. In the first step of the wizard, select the User-Based Parameter option, then click Next.
    User-based parameter option.
  3. In the second step of the wizard, choose 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
      Choose user info type.
  4. Click Finish to complete configuration of the parameter. The Stored Procedure Parameters grid displays the updated attributes for your parameter.
    User-based parameter done.
  5. (Optional) Enter the maximum number of data rows you want to preview in the Maximum Records box.
  6. Click Discover and Preview Data to preview the resulting data.
  7. Select the Columns tab to see the definitions of the columns generated by the stored procedure or function.

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.

Notes

  • If you subsequently modify a stored procedure or function in your database after setting up the virtual table, it may be necessary for you to re-discover the stored procedure/function at the data connector-level, then edit/recreate dependent objects such as the virtual table itself, KPIs, datasets, &etc; For this reason it is recommended not to continually modify your stored procedures, for example, after creating the corresponding virtual table.

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.