Using the SQL Report Wizard

How to:

The SQL Report Wizard assists you with SQL passthru, which allows you to execute SQL code that retrieves data from an RDBMS. You can use the resulting extract file in Report Painter or InfoAssist. The supported engines for the SQL Report Wizard are DB2, DB2 for IBM i, Microsoft SQL Server, Oracle, Sybase, and Teradata.

The SQL Report Wizard is available throughout all development areas of Developer Studio: Projects, Data Servers, and Managed Reporting. When working in Managed Reporting, the tool enables administrators to use SQL Reports in the available repository folder or to use procedures that reside on the WebFOCUS Reporting Server.

Note: It is recommended to use the Create Synonym Tool to execute a SQL Command for a stored procedure. For more information, see Synonym Creation Parameters for Stored Procedures, in the Accessing Data and Creating Synonyms chapter of the Describing Data with Graphical Tools manual.


Top of page

x
Procedure: How to Include SQL Commands From an External .sql File
  1. Access the SQL Report Wizard by doing one of the following:
    • In the Explorer, right-click the Procedures folder and select New, Procedure. In the Add Procedure dialog box enter a file name and then select SQL Report Wizard from the Create with drop-down list. Click Open.
    • In the Procedure Viewer, click the component connector and then click SQL Report on the drop-down list.
    • You can click the SQL Report icon on the Procedure Viewer toolbar.

    The SQL Report Wizard - Welcome window opens, as shown in the following image.

    SQL Report Wizard diagram

  2. Click the Included from an external '.sql' file option button. This enables you to browse and select external procedures that exist in the repository of the project. This enables sites to leverage pre-existing SQL procedures. It is not possible to execute procedures from Managed Reporting.

    Note: You can only browse the files on the APP PATH if your .sql extension is a valid filter in the Properties dialog box. To do this, see How to Add an .sql Extension as a Valid Filter.

  3. Click Next to see the SQL Report Wizard - Data access information window, as shown in the following image.

    SQL Report Wizard-Data access info dialog box

  4. In the Select the SQL database engine area, select a database engine from the drop-down list. The list consists of available engines in the edasprof.prf file.
  5. In the Select the connection area, choose a connection from the drop-down list generated from the engine that you selected. You can choose the default value, which is the first connection in the edasprof.prf file for the selected engine, or choose another connection defined in edasprof.prf.
  6. Click Next to see the SQL Report Wizard - Include external SQL file window, as shown in the following image.

    SQL Report Wizard dialog box

  7. Enter the external SQL file name in the field, or click Browse to select it.

    Note: You can only browse the files on the APP PATH if your .sql extension is a valid filter in the Properties dialog box. To do this, see How to Add an .sql Extension as a Valid Filter.

  8. Optionally, you can run with limited records by clicking Run SQL. By default, the Run with limited records check box is selected so you can test your procedure with a read limit if the engine supports it. There is a field box next to the check box in which you can enter the number of records to be read. 100 is the default limit.
  9. Click Next to see the SQL Report Wizard - Summary of SQL options window.

    Do one of the following:

    • To create a report, select the Create Report option button. This option is selected by default.
    • To create a graph, select the Create Graph option button.
  10. Click Finish to run the SQL procedure. When you have completed the procedure, you can run it from the Procedures folder in the Explorer view.

Top of page

x
Procedure: How to Pass SQL Commands to the RDBMS Using SQL Passthru
  1. Access the SQL Report Wizard by doing one of the following:
    • In the Explorer, right-click the Procedures folder and select New, Procedure. In the Add Procedure dialog box enter a file name and then select SQL Report Wizard from the Create with drop-down list. Click Open.
    • In the Procedure Viewer, click the component connector and then click SQL Report on the drop-down list.
    • You can click the SQL Report icon on the Procedure Viewer toolbar.

    The SQL Report Wizard - Welcome window opens.

  2. Click the Option button next to Type SQL statements in the report request. This enables you to enter SQL commands that will be passed on to the RDBMS with the SQL Passthru feature.
  3. Click Next to see the SQL Report Wizard - Data access information window.
  4. In the Select the SQL database engine area, select a database engine from the drop-down list. The list consists of available engines in the edasprof.prf file.
  5. In the Select the connection area, choose a connection from the drop-down list generated from the engine that you selected. You can choose the default value, which is the first connection in the edasprof.prf file for the selected engine, or choose another connection defined in edasprof.prf.
  6. Click Next to see the SQL Report Wizard - Enter SQL statements window, as shown in the following image.

    SQL Report Wizard dialog box

  7. In the field box, type the SQL statements you want to pass to the RDBMS.
  8. Optionally, you can run with limited records by clicking Run SQL. By default, the Run with limited records check box is selected so you can test your procedure with a read limit if the engine supports it. There is a field box next to the check box in which you can enter the number of records to be read. 100 is the default limit.
  9. Click Run SQL to run your report.
  10. Click Next to see the SQL Report Wizard - Summary of SQL options window.

    Do one of the following:

    • To create a report, select the Create Report option button. This option is selected by default.
    • To create a graph, select the Create Graph option button.
  11. Click Finish to run the SQL procedure. When you have completed the procedure, you can run it from the Procedures folder in the Explorer view.

Top of page

x
Procedure: How to Import SQL Commands From an Existing .sql File
  1. Access the SQL Report Wizard by doing one of the following:
    • In the Explorer, right-click the Procedures folder and select New, Procedure. In the Add Procedure dialog box enter a file name and then select SQL Report Wizard from the Create with drop-down list. Click Open.
    • In the Procedure Viewer, click the component connector and then click SQL Report on the drop-down list.
    • You can click the SQL Report icon on the Procedure Viewer toolbar.

    The SQL Report Wizard - Welcome window opens.

  2. Click the Option button next to Import from an existing .sql file. This enables you to modify SQL code after importing it from an external file to the procedure being built. It enables you to modify the request using bits of code.
  3. Click Next to see the SQL Report Wizard - Data access information window.
  4. In the Select the SQL database engine area, select a database engine from the drop-down list. The list consists of available engines in the edasprof.prf file.
  5. In the Select the connection area, choose a connection from the drop-down list generated from the engine that you selected. You can choose the default value, which is the first connection in the edasprof.prf file for the selected engine, or choose another connection defined in edasprof.prf.
  6. Click Next to see the SQL Report Wizard - Import external SQL file window, as shown in the following image.

    SQL Report Wizard dialog box

  7. Type the SQL file name that you want to import or click Browse to select it.

    Note: You can only browse the files on the APP PATH if your .sql extension is a valid filter in the Properties dialog box. To do this, see How to Add an .sql Extension as a Valid Filter.

  8. Optionally, you can run with limited records by clicking Run SQL. By default, the Run with limited records check box is selected so you can test your procedure with a read limit if the engine supports it. There is a field box next to the check box in which you can enter the number of records to be read. 100 is the default limit.
  9. Click Run SQL to run your report.
  10. Click Next to see the SQL Report Wizard - Enter SQL statements window. You can edit the imported SQL code, if necessary.
  11. Click Run SQL to run your report.
  12. Click Next to see the SQL Report Wizard - Summary of SQL options window.

    Do one of the following:

    • To create a report, select the Create Report option button. This option is selected by default.
    • To create a graph, select the Create Graph option button.
  13. Click Finish to run the SQL procedure. When you have completed the procedure, you can run it from the Procedures folder in the Explorer view.

Top of page

x
Procedure: How to Add an .sql Extension as a Valid Filter

To browse the files on the APP PATH, your .sql extension must be a valid filter in the Properties dialog box.

  1. Right-click the project, select Properties, then select the Edit Filters tab.
  2. Scroll through the list until you find the .sql extension in the Extensions column.

    If the .sql file is not part of the displayed list in the Extensions column, click the Add new filter file type filter(s) icon .

  3. Select the .sql extension.
  4. Click OK.

WebFOCUS