Loading...

Creating Database Data Sets

Use data sets to collect the information that you will use to build charts, tables, and dynamic inputs.

Before You Begin

  • To create an embedded data set, your CommCell user account requires a role with the following permissions and associations:
    • Add Report permission and an association with the CommCell entity
    • Query Datasource permission and an association with the Data Source entity that you will use

For more information on users, permissions, and associations, see User Administration and Security - Overview.

About This Task

To add user-based security to your reports, you can add stored procedures from the Security API to the report query. These stored procedures are available only for data sets created from the CommServe database. For more information, see Security API Overview.

Procedure

  1. On the Web Console for the Private Metrics Reporting Server, click Reports.

    The Worldwide Dashboard appears.

  2. Create a new report, or open an existing report:
    • To create a new report, at the top of the page, click Worldwide Reports > Report Manager , and on the Reports tab, click New Report.

    • To open an existing report and add a new data set to it, at the top of the page, click Worldwide Reports, click Report Manager , and on the Reports tab, next to the Report Name under Actions, click Edit.

    The Report Builder page appears
  3. On the Report tab, in the Name box, type a name for the report, and in the Description box, type a short description of the report.
  4. On the Data Sets tab, click Add .
  5. In the Add Data Set dialog box, on the Query tab, select Database.
  6. In the Data Set Name box, type a name.
  7. In the Data Sources list, select the name of a data source, and in the Databases list, select the database that you want to query.
  8. Select Tables or Views, and in the list, select the database table or view that you want to use.

    When you select a table or view, a query appears in the Query box. You can add additional commands to this query or type your own query.

  9. For Oracle data sets, you must add additional statements to the query:
    • You must write statements in PL/SQL. In the Query box, type a query.

      For example: SELECT * FROM TableName

    • If you are using stored procedures, then the stored procedure in the Oracle database requires a REF CURSOR parameter that you can pass the @cursor moniker into. In the Query box, type HR.STORED_PROCEDURE(@cursor);.

      For example: HR.SP_EMPLOYEES(@cursor);

  10. Click Preview to review the query output.
  11. Optional: Change the display names of the fields that will appear in the Data Sets list. Click the Fields tab, then in each box under Field Name, type a display name for the corresponding Field Source.

  12. Optional: Select the amount of time that the query will run before it times out. Click the Advanced tab, and in the Time Out box, type or select the number of minutes.
  13. Click Done.

    The data set you created appears under Data Sets in the Report Builder.

What to Do Next