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
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 Posts
Re: appending text to a value in a column2012-08-31T04:05:10ZThis is the accepted answer. This is the accepted answer.In 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.