IBM Cognos Report Studio is a powerful tool. It is flexible which makes it powerful and dangerous at the same time. You can create a report based on a Framework Manager Package or go directly to the database. I tend to advise that you should always go through Framework Manager to create your reports instead of building custom Report Studio SQL Queries within the report.

Often, people bypass Framework Manager because they may feel that it is rigid. I will admit that it is rigid and for good reason. Whenever I see custom sql code in a report, it is there for really one reason. The code is compensating for what is lacking in the data, whether it is data structure or data quality. I know some report writers are getting all worked up saying that Framework Manager takes a lot of work to do one thing that can be accomplished faster in a report. I agree, but how scalable is the fast route? If you have another report that needed that same code, you would have to report with the same code that has to be managed and maintained separately. However, if it were maintained in Framework Manager you would have one place to make the change and it would apply to all the reports built on the package. Framework Manager allows you to scale your work and custom sql in a report is more of a one off…or as I like to call it …a run off…they tend to run off with your time.

The point of this conversation is to point out that every tool has its place. It is my position that code in a report is not the best use of Report Studio capability. It merely masks the real issue. Data quality and structure must be addressed at the database level. Any attempt to bypass that work will only lead to more headaches later. Ok one more thing before I get off my soap box, even the most talented report writer would run up and kiss the person that could fix their data. I have been asked to write a post on how to use and leverage Microsoft SSIS to scrub your data and I promise I will get to such a post.

Ok so back to this post. When you get into writing code directly into a report, you have moved into programming and out of report writing. You can write the code in the report, then place that code on the database and call it from Framework Manager by using a stored procedure to build the query subject. After that, you can write your report based on the query subject. This is a much more efficient way to build a report using custom code. The database can handle commands and syntax better than the report and this method is more scalable.

If you lessen the amount of custom work in the report and correct the issues in the database directly, you will see a marked improvement in report runtime and happier report writers.