Tag Archives: cognos

T-SQL Tuesday #22 Data Presentation

1 Reply

This month’s TSQL Tuesday topic: Data Presentation.

I am not going to show any code this month, and I am also going to veer off the topic of SQL server slightly.

My company does a lot of reporting though Cognos.  The nice thing about Cognos is that it is platform independent.  Once a connection is made to an Oracle, MSSQL, or even MySQL database, the presentation looks the same to a report developer.  The end user goes to the URL for cognos and runs their report.  They may not even know what application or technical backend is supplying the data.

We have LOTS of reports that get run across the enterprise.  We also probably have a couple hundred report developers that write reports with varying degrees of frequency.  I’m sure you can imagine that without any standards no two reports would look similar and end users would not know how to use or interpret the information that they get out of a report.  Hence why data presentation is so important.

There are several standards that have to be met in our environment in order for a report to be published for end users.  While I don’t think it would be appropriate for me to show you any screenshots, I can summarize some of the general rules here.

  1. All reports must use the corporate header and footer.  Information included must contain the business unit for the report, Title of the report, corporate logo, confidentiality statement, page numbers, and telling how to best view the report (ie: html, pdf, Excel, etc.)
  2. The last page of the report must be the standard documentation page.  This page has a table with specific information recorded about the report including:
    • Report Purpose: A general description of what the report is and how it should be used.
    • Data Source: name of the server, database, or application where the data in the report came from.
    • Author: who wrote the report, and how to contact them with questions.
    • Security: Security requirements of the report.  Does it contain confidential information? Who or what security groups are allowed to run the report.
    • Business Logic: List of information that may be critical to interpreting the report.  For example, “Annual sales figures are calculated based on the fiscal year of July 1 to June 30 in this report”.  Also list any abbreviations that everyone may not know.  For example, “AHG = Average Height by Gender (I made that up)”.
    • Assumptions: What assumptions, if any, were made while writing the report.   I have also seen some report authors insert any user defined variables here.  This way when someone runs a report and they think that it is wrong, the report author can see exactly what values the user entered into the prompt boxes as a point to start troubleshooting.

The documentation page on a report becomes a valuable tool for anyone trying to understand a report and also very useful for someone who is troubleshooting a report.  Of course it is also helpful when an end user does not delete the documentation page…..

UDA-SQL-0283 Metadata describing column does not match results from database

Leave a reply

In my current job, I have a MySQL database server that I am responsible for.  We also run some reporting through Cognos against this server.  I ran across a problem while testing reports on an upgrade to Cognos 10.  I thought I would share the solution here in case it helps someone else out someday.

I kept running into error messages while testing the Cognos reports that hit the MySQL database:

  • RQP-DEF-0177 An error occurred while performing operation ‘sqlOpenResult’ status=’-28′.
  • UDA-SQL-0114 The cursor supplied to the operation “sqlOpenResult” is inactive.
  • UDA-SQL-0283 Metadata describing <column name> does not match results from the database.

I then opened up Cognos Framework Manager to look at the package.  The weird thing is that I was able to run a Test Sample and see data return in Framework Manager.  However the same data item was throwing errors in Report Studio.  When I looked at the data items in Framework Manager, they had a data type of nVarChar.

Time to bring in the big guns

I started working with our Cognos support group to figure out what was going on.  They were able to edit the definition of one of the data queries in Framework Manager.  When the updated the data item, the data type of the text columns changed from “nVarChar” to “Character Length 16”.  Once they made the change and updated the package, the reports started running again in Report Studio.

Woohoo, problem solved!

Oh wait, I spoke too soon…I went back in to Framework Manager to repeat our support group’s steps against other query items to update the whole package.  Unfortunately when I updated any of the data queries, the data type kept changing back to “nVarChar” and the reports threw errors again.  I even tried to re-update the data item that our support group fixed.  When I made my edit, the reports broke again.

The Solution

Long story short, and after 2 weeks and plenty of headaches….I had a different version of the MySQL ODBC driver installed on my PC from what our Cognos support group did.  I changed the driver version from 5.1 to 3.51, and finally it worked!  I figured that the newer driver would be better, but apparently Cognos 10 Framework Manager has a problem with the MySQL ODBC 5.1 driver, and needs the 3.51 driver instead.

With the older driver installed on my system, I was able to update the Framework Manager package and then pull back data error free in Report Studio.

One other thing of note: after I update the query subject in the Physical Layer in Framework Manager, the links broke in the Presentation layer.  I had to go back into the Presentation Layer to update the links there as well.