Topic
  • 2 replies
  • Latest Post - ‏2012-10-08T21:07:58Z by Pritesh_Jewan
Pritesh_Jewan
Pritesh_Jewan
4 Posts

Pinned topic appending text to a value in a column

‏2012-08-30T12:04:31Z |
Hello,

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:

http://www-01.ibm.com/support/docview.wss?uid=swg21408843

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

Thank You

Regards
Pritesh
Updated on 2012-10-08T21:07:58Z at 2012-10-08T21:07:58Z by Pritesh_Jewan
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: appending text to a value in a column

    ‏2012-08-31T04:05:10Z  
    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.
  • Pritesh_Jewan
    Pritesh_Jewan
    4 Posts

    Re: appending text to a value in a column

    ‏2012-10-08T21:07:58Z  
    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.
    Hi Phil,

    This worked perfectly.

    Thank You

    Regards
    Pritesh