Search code examples
sql-servercognos-10cognos-bi

How do I get the name of the month (char) and the Year (date) to appear in my data item and column in Cognos

My Accounting Period Date is using a Numeric data type

1990-01-01
2008-08-01
2008-07-01

So I applied CAST function in the data item.enter image description here

And the results appeared like this.

Jan 1, 1990
Aug 1, 2008
Jul 1, 2008

Is there a way to make it appear like this?

Jan 1990
Aug 2008
Jul 2008

I tried using a new data item using Replace Function like this but it does not work.

This is the error I get. enter image description here

Could someone help me please.

Solution

  • Part of the problem you will run into is Cognos's auto formatting of date types. That's what you are seeing with the Jan 1, 1990 format. The data still exists as a date but Cognos chooses to reformat it when running. Thus, you cannot use a substring function to extract out the components you want as it doesn't exist as a string. Straight casting to a string reverts it to the 'YYYY-MM-DD' format in string form, obviously not what you want.

    There are three options.

    If you just want the date to display in the desired format on the report, then the simplest solution is to use the 'Data Format' property on the column itself in the report definition (list, crosstab, singleton etc). Within the Data Format property sheet there is a property called 'Pattern'. You can use this property to define how you want the column displayed. Enter 'MMM YYYY' in this property to create the output you desire.

    enter image description here

    If you need the actual data item to change to match the format you require then you will have to build a string:

    CASE extract(month,[Date]) 
WHEN 1 THEN 'Jan' 
WHEN 2 THEN 'Feb' 
WHEN 3 THEN 'Mar' 
WHEN 4 THEN 'Apr' 
WHEN 5 THEN 'May' 
WHEN 6 THEN 'Jun' 
WHEN 7 THEN 'Jul' 
WHEN 8 THEN 'Aug' 
WHEN 9 THEN 'Sep' 
WHEN 10 THEN 'Oct' 
WHEN 11 THEN 'Nov' 
WHEN 12 THEN 'Dec' 
END || ' ' || cast(extract(year,[Date]),char(4))

    Your last option is to use a data source vendor-specific cast function. For example, DB2's to_Char() function allows you to precisely define how the date is converted to a char. Other database servers have their own functions that do similar things. You'll have to figure out if there is an equivalent in your data source vendor's solution.

    The down side to this approach is that if you change data sources to another vendor you will have to adjust the report as well or it will likely throw an error. When you use Cognos's own functions, Cognos automatically converts the function to the equivalent vendor-specific function for you. You gain portability and maintenance at the possible expense of flexibility and power.