Converting to Character from Numeric and Date for DMR

[gosoccer]

Hi guys,

I had to use cast(date, char (14)) to change dates to characters so they can be included as a part of DMR Dimension creation. I did this Date conversion using cast within Framework and now the package including the Date Dimension can be published.

There is only one problem. The Date that is converted to Char needs to be used as a mm/dd/yyyy Date Format within the Report Author and since it is Character, it's totally ignoring the Date Format setting to be estabished in Report Studio date format setting.

If you have any suggestions, please let me know.

I greatly appreciate it.

[MFGF]

Hi guys,

I had to use cast(date, char (14)) to change dates to characters so they can be included as a part of DMR Dimension creation. I did this Date conversion using cast within Framework and now the package including the Date Dimension can be published.

There is only one problem. The Date that is converted to Char needs to be used as a mm/dd/yyyy Date Format within the Report Author and since it is Character, it's totally ignoring the Date Format setting to be estabished in Report Studio date format setting.

If you have any suggestions, please let me know.

I greatly appreciate it.

Within the report author? Do you perform surgery on the people who write your Cognos reports? Or do you mean in Report Studio? :)

Is the date simply the caption of one of your day-level members, or do you also have a relational package where you are trying to format dates?

If the former, you simply need to extract the month, the day and the year from your original date (use the extract() function), cast these as character strings, then concatenate them together with '/' between.

MF.

[gosoccer]

Sorry about that. No Report Studio!!!! :o :o :o I don't believe, I said Report Author again..
Ok yeah! "caption of one of your day-level members". DMR.

Man, so I have to do all this (Extract ()) Etc, in Report STUDIO, for having the date in the right format?

Ok, thanks a lot for your time.  :)

[MFGF]

Sorry about that. No Report Studio!!!! :o :o :o I don't believe, I said Report Author again..
Ok yeah! "caption of one of your day-level members". DMR.

Man, so I have to do all this (Extract ()) Etc, in Report STUDIO, for having the date in the right format?

Ok, thanks a lot for your time.  :)

Hi,

If it's DMR then no - just do it in Framework Manager on the day-level of your regular dimension.

Cheers!

MF.

[gosoccer]

Would you mind elaborating on your answer? :) :) Any sample code to use would be awesome!!
Thx a lot as always. I'm really stuck on this one... But what's new!!!

[MFGF]

Would you mind elaborating on your answer? :) :) Any sample code to use would be awesome!!
Thx a lot as always. I'm really stuck on this one... But what's new!!!

Open your project in Framework Manager
Double-click the Regular Dimension for time
Select the Day level of the hierarchy
Use the Add button at the bottom to add a new attribute to the Day level
Add an expression using the extract() function - something along the lines of
cast(extract(month, [Dimensional view].[Time].[Time].[Day].[Date]  ), varchar(2)) + '/' + cast(extract(day, [Dimensional view].[Time].[Time].[Day].[Date]  ), varchar(2)) + '/' + cast(extract(year, [Dimensional view].[Time].[Time].[Day].[Date]  ), varchar(4))
Set this attribute to have a role of _memberCaption
Save and publish your package.

Cheers!

MF.

[gosoccer]

Ok, super! Thx so much. I'll chase it.