Topic
IC4NOTICE: 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.
2 replies Latest Post - ‏2012-10-08T21:07:58Z by Pritesh_Jewan
Pritesh_Jewan
Pritesh_Jewan
4 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: appending text to a value in a column

    ‏2012-08-31T04:05:10Z  in response to Pritesh_Jewan
    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
      ACCEPTED ANSWER

      Re: appending text to a value in a column

      ‏2012-10-08T21:07:58Z  in response to SystemAdmin
      Hi Phil,

      This worked perfectly.

      Thank You

      Regards
      Pritesh