Build a Report Using Your Own SQL or MDX

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

If you are working with MDX, you must be aware of the MDX syntax that IBM Cognos Analytics - Reporting supports.

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

You cannot build a report by adding your own MDX if the data source is IBM Cognos TM1®. IBM Cognos TM1 does not support custom MDX.

Procedure

  1. Create a new report and choose the Blank template.
  2. Click the Queries icon Queries icon, and click Queries.
  3. Click the Toolbox icon The toolbox icon and drag Query to the work area.
  4. From the Toolbox, do one of the following:
    • To build an SQL query, drag SQL to the right of the query.
    • To build an MDX query, drag MDX to the right of the query.
    Tip: You can drag SQL or MDX anywhere in the work area, and 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 or MDX property and type the SQL or MDX.
  8. Click Validate to check for errors.
  9. If you are working with SQL, select the query, and in the Properties pane, set the Processing property to Limited Local.
  10. Double-click the query.

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

  11. Click the Pages, and 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. Click the Show properties icon, and In the Properties pane, click the Select ancestor icon 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 Insertable objects icon Insertable objects and then click the Data items tab Queries icon. Drag the items from the query you chose in the previous step to the data container.