Explaining Report Studio SQL Object

SQL Objects are one of the most underused features of report studio. The main reasons for this are that, even advanced users are generally not proficient in SQL or report authors are not familiar enough with the data source to write SQL Queries.

Note:

• SQL Objects are hard to maintain since analyzing publish impact from framework manager will not catch tables or columns used in SQL objects.
• Since SQL objects are at the report level, model administrators are usually not aware of what tables the users are using if SQL objects are used. If you need to secure tables, do it at the database level and restrict access to the cognos user (the one used in the data source connection).

That said, SQL objects can come in handy in many scenarios. Some examples:

• User wants to query database tables that are not included in the package

Not all database tables are necessarily included in the published package. This might be because the tables don't join with the tables in the package or has unrelated information. If the users want to query such tables for one-off reports, then they can simply write a sql query against the underlying data source.

• User wants to compare data between multiple data sources

If users are creating reconciliation reporting between multiple data sources (dev vs. qa vs. prod), then they can simply multiple sql objects each pointing to a different data source and join these queries to compare the data.

• User wants to use complex database functions in the report

Although you can use database functions in report studio, not all functions are available. For example, I had to use an oracle function called wm_concat to concatenate values. This was an aggregate function similar to sum or avg. I couldn't find this function in cognos or find an equivalent function, so I used a SQL object and wrote the query myself.

• User wants to use a different join than what is defined in the package

Users might want to use a different kind of join (inner vs outer) or join tables based on different criteria than what is defined in the package. SQL queries let you control the join conditions.

These are just some examples. Once you start using them, you might find other scenarios where SQL objects may prove useful.

Now let's see how to use them.

• Mouse over the Query Explorer pane and click on "Queries". In the "Insertable Objects" pane, you can find the object "SQL". You can drag and drop it in the Queries section or double click SQL.

• You don't have to manually create a query before adding a SQL object. If you directly add a SQL object, Cognos will automatically create a query and place the SQL object as a child of that query.
• Following are the properties of a SQL object

• SQL Syntax: You can keep the sql syntax as Native as long as you know the syntax of your database.
• Data Source: Pick the data source that you want to run the query against. You have to pick the data source before you enter the query, otherwise cognos will not be able to validate the query and will throw an error message
• SQL: Do not use delimiters like semi-colon at the end of the query.
• The first time you enter the query, the columns from the SQL are automatically added to the parent query. If you make any subsequent changes to the query like adding or removing columns, you have to manually update the query.