Using Date dimension how to format the date in Report studio(DD-Mon-YYYY)

[jd]

Hi I am working with cube based report studio report. in cube I have Date dimension (Year/Quarter/Month)

In report studio I hae to format the date column like DD-Mon-YYYY(12-Jan-2010).

how can I use the Date dimension to fomat like above date

Thanks.

[MFGF]

If your date dimension has only Year, Quarter and Month levels, then you do not have any actual dates to be able to format in this way.  The best you could do would be to add a fixed day part to each month (1st, maybe)?  Can you explain a little more about what you're trying to achieve?

MF.

[jd]

Thanks MFGF.

I changed my date dimension in transformer. now I have dates like 20090501, 20090821.

This cube is data source to my report studio report. In report studio I have to format the date like 01-Aug-2009.
So I have used to _char, To_date and extract functions but none of them are working.  could you please let me know how to get the date format like 01-Aug-2009.

Thanks,

[MFGF]

By far the easiest way to achieve this is to set an appropriate format for the date in the properties of the date level in Transformer (on the TIME tab - modify the format) - use a format such as dd-MMM-yyyy.

Regards,

MF.

[jd]

Hi MFGF thanks for the quick reply.

Actually in the transformer my time dimension is under other dimension. ex: state->region>location>startdate>product id

if I take start date as a level, in the properties time tab is disabled I cann't change the format there.

If I take the start date as a separate dimension, my report is running forever. so I took my start date and all other items  in one dimension only, this way I am getting my records correctly and report is running very quickly.

Is there any way in the report I can change the date format?

Thanks.

[MFGF]

Hmmm - sounds like there is no real benefit in having your data in a cube here.  You are not making use of any of the dimensionality with a structure like yours.  Why not just report directly off the underlying data?

If you must use the cube, I'd suggest your only course of action is to get hold of the caption of the level (use a caption() function) then use a substring() function to extract each part you require from the caption.  You will probably need to use a CASE construct to convert the numeric month values to month names too.

Regards,

MF.

[jd]

Thanks alot MFGF.

it is kind of little work but worked perfectly how I want.

Thanks again.

another issue: I have a list report and prompt page. Now I have to move that prompt page prompts to report page on top of the list. I cut my prompts and pasted in the report page top of the list and tried to run the report but it is running for ever.  Any suggestions/ideas from you?

[CognosPaul]

You should use the substring and case statements inside a report expression, as opposed to the query. When you use the query substring functions Cognos will store the output of the query as a local file, and run Cognos SQL on top of that. The performance takes a huge hit. Read more here.

EDIT: Fixed URL