We are using Cognos Report Studio 8.4.1 that is bundled with Tivoli monitoring. We have a requirement to display the date as follows: 2011/12/01 in a column in the report where the 2011 comes from a Year data item and 12 comes from the Month data item. But the end user wants the 01 hard coded as the report provides the highest value for the month. So I created a Query calcuation and put the following in: Year||'/'||Month (Numeric)||'/'||'01' which works fine and give me the results that I want.
However, when we started deploying the report to the different Cognos environments that we have the report would fail to run in some of the environment and what we finally found was that the environments where the report worked were at DB2 9.7FP5 and the environments where the report failed were at DB29.5(we cannot upgrade the 9.5 environments at the moment). We then found the following article which explains the limitation similar to the error we are getting:
Does anyone know any other way in Cognos/DB2 9.5 that we can achieve the above? We basically want to merge the Year column and the Month column into a new column and add the string /01 to the end
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
2 replies Latest Post - 2012-10-08T21:07:58Z by Pritesh_Jewan
Pinned topic appending text to a value in a column
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-10-08T21:07:58Z at 2012-10-08T21:07:58Z by Pritesh_Jewan
SystemAdmin 110000D4XK15496 PostsACCEPTED ANSWER
Re: appending text to a value in a column2012-08-31T04:05:10Z in response to Pritesh_JewanIn Report Studio you do not have to concatenate values to show them in a single cell in the report. If you look at the Report Studio toolbar you should see a button with a padlock on it. If you click this button it will unlock the layout. You will then be able to drag multiple data items into a single list column cell. You can add static text items between the data items for formatting purposes. Another alternative is to create a layout calculation where you concatenate the values outside of the query definition. The layout calculation will do the concatenation as part of the rendering process and the calculation will not be passed down to the database as part of the SQL query.