Build a Report Using Your Own SQL

You can build a report by adding SQL from an external source, such as another report.

If you edit the SQL of a query, you must change the Processing property for that query to Limited Local.

Procedure

  1. Create a new report and choose the Blank template.
  2. From the report navigation menu, click Report > Queries.
  3. Click the Toolbox icon The toolbox icon, and drag Query to the work area.
  4. Click the Toolbox icon, and to build an SQL query, drag SQL to the right of the query.
    Tip: You can drag SQL anywhere in the work area, and IBM® Cognos® Analytics - Reporting will automatically create a query.
  5. Click the Show properties icon Show properties icon, and in the Properties pane, double-click the Data source property and click a data source.
  6. If required, set the Catalog property to the name of the catalog.
  7. Double-click the SQL property and type the SQL.
  8. Click Validate to check for errors.
  9. Select the query, and In the Properties pane, set the Processing property to Limited Local.
  10. Double-click the query.

    If the SQL is valid, the data items defined in the SQL appear in the Data Items pane.

  11. In the navigation menu, click Report, click Pages, and then click a report page.
  12. Click the Toolbox icon and drag an object to the work area.

    For example, drag a list, crosstab, chart, or repeater.

  13. Click the data container.
  14. In the Properties pane, click the select ancestor button select ancestor icon and click the container you just created.

    For example, if you created a list, click List.

    Tip: You can also click the container selector (three orange dots) of the container to select it.
  15. Set the Query property to the query for the report type.
  16. Click the Data items icon Queries icon
  17. Drag the items from the query to the data container.