Convert a Date format into 'MMM-YYYY' format

[v2k4u]

Hi Gurus,

I have a question about how to convert the Date field into 'MMM-YYYY' format in Cognos when using teradata as the database, i have tried _make_timestamp function but it does not provide me the exact format that i am looking for.
So any help in regards to this would be greatly appreciated.

Thanks,
Vinod Kumarr.

[MFGF]

Hi Gurus,

I have a question about how to convert the Date field into 'MMM-YYYY' format in Cognos when using teradata as the database, i have tried _make_timestamp function but it does not provide me the exact format that i am looking for.
So any help in regards to this would be greatly appreciated.

Thanks,
Vinod Kumarr.

Hi,

Assuming the date item is a date datatype already, all you should need to do is to bring the date item into your report, select the Data Format property, set the format to Date, set the Date Ordering to "Day, Month, Year", set Display Years to "Yes", set Display Months to "Short name", and set Display Days to "No"

If it isn't already a Date datatype, you will need to either cast() it or use a _make_timestamp() to convert it to a date, then use the above approach.

Cheers!

MF.

[v2k4u]

Hi MF,

Thanks for your reply, the solution you provided works for displaying the data but it is getting repeated for all the days even though we give the Display Days property to No. I want everything in a month to be grouped into single entity like Apr-2014, but what is happening now is that the values are getting repeated for individual days and it is giving me the result set of Apr-2014 for 30 times.

[bdbits]

Um, that is a completely different question. You asked about data formatting, but your problem is with your query, and/or grouping in the report.

For me at least, there is not enough information about your data source and query to guess at why you are getting repeated rows.

[MFGF]

Hi MF,

Thanks for your reply, the solution you provided works for displaying the data but it is getting repeated for all the days even though we give the Display Days property to No. I want everything in a month to be grouped into single entity like Apr-2014, but what is happening now is that the values are getting repeated for individual days and it is giving me the result set of Apr-2014 for 30 times.

Ohhh - ok. I thought you just wanted to control the formatting. If you want to group by the MMM-YYYY values, then you need a slight modification to the technique, as currently your dates are still full dates under the covers, so you will be getting (eg) 30 rows for Sep-2014 (because there are 30 days in September).

Add a query calculation to your report that has the expression _first_of_month ([Your date item])
Use this in your report rather than the original date item, and format as I suggested above. This should give you what you need. :)

Cheers!

MF.

[v2k4u]

Hi MF,

Thanks for your suggestion, it just worked perfectly fine for me. But i have a quick question when you give the expression as _first_of_month([Data Item]) i thought it would probably give me the results only for the first day of the month but it actually gives me the aggregated result set for the whole month. So i am curious to know more about it.

Thanks,
Kumar.

[MFGF]

Hi MF,

Thanks for your suggestion, it just worked perfectly fine for me. But i have a quick question when you give the expression as _first_of_month([Data Item]) i thought it would probably give me the results only for the first day of the month but it actually gives me the aggregated result set for the whole month. So i am curious to know more about it.

Thanks,
Kumar.

It takes the date on each row of data and converts it to the first date of that month. Since Cognos automatically groups descriptive items and summarizes measure items, all rows with the same descriptive value (ie the same date) in your display object (list/crosstab/chart etc) get grouped together into a consolidated row. All rows have the same date for any particular month, so they all get consolidated into one row for each month. Your formatting shows only the Month and Year part of the date and hides the day. :)

Cheers!

MF.