Thursday, June 7, 2012

Explaining Report Studio SQL Object

Keywords: SQL Object, Report Studio, Query, IBM Cognos

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.

3 comments:

  1. This is very true that maintaining of SQL is very hard and this is the reason that most of the people wants to know much more about the SQL object.These type of blogs always helps people to know about the particular subject

    ReplyDelete
    Replies
    1. diệu, được đưa tới Đấu Khí học viện chúng ta học tập.

      Hàn Giang Đào nói:

      - Ngươi đối với Nhạc gia có hiểu rõ không, chuyện của Nhạc gia có biết hết chứ/
      đồng tâm
      game mu
      cho thuê phòng trọ
      cho thuê phòng trọ
      nhac san cuc manh
      tư vấn pháp luật qua điện thoại
      văn phòng luật
      số điện thoại tư vấn luật
      dịch vụ thành lập doanh nghiệp
      http://we-cooking.com/
      chém gió
      - Viện trưởng nói đến chuyện đấu đế huyết mạch trên người của tại hạ sao?

      Nhạc Thành hỏi Hàn Giang Đào, vốn Nhạc Thành cũng muốn hỏi chuyện này.

      - Không phải, ta hỏi là chuyện của Nhạc gia ngươi biết bao nhiêu?

      Hàn Giang Đào cất tiếng.

      - Không biết nhiều, tại hạ chỉ nghe nói huyết mạch của Nhạc gia đã rất mỏng manh, chuẩn bị suy tàn.

      Nhạc Thành cất tiếng nói.

      Chuyện của Nhạc gia, Nhạc Tử Sơn cũng chỉ nói như vậy với Nhạc Thành. Nhìn biểu tình của Hàn Giang Đào, Nhạc Thành suy đoán rằng Hàn Giang Đào biết không ít chuyện của Nhạc gia.

      Delete
  2. how can we add total function in list report created using sql object

    ReplyDelete