Need to display date dimension in proper date format

[rd152343]

Hi Team,

We have a date dimension build through cube designer. The Data item defined as Member caption is a DATE column and view data in Dynamic Cube designer seems to display the value in proper date format, while after publishing the cube, when I try to drag and drop the data item in report, the value is not in date format and I am not able to apply the data format on the column to make it appear as date.

Has any one faced this issue? Can some one throw some light on this issue?

Thanks!

[sdf]

I am not able to apply the data format on the column to make it appear as date.

Are you having error???

have you tried to cast dataitem to date?
e.g

cast([dataitem],date)

???

[rd152343]

Hi,

No, I am not getting an error. The date value appears as String in YYYY-MM-DD format and data format does not get applied on the value.

Tried cast function too, but of no help.

Has anyone faced this issue with v11 dynamic cube reporting?

Thanks!

[sdf]

I am guessing you have tried string2date() as well..
hmm.. that's odd.

[a3nCognosUser]

What does the cast function return?

[CognosPaul]

I finally found it!

I wrote an article that describes this exact scenario:

http://www.performanceg2.com/tricks-with-graphs-in-cognos-10/

The big problem is that strings can't be formatted, so you need to change it into a date. OLAP doesn't support casting so it has to be done on the report level. Set the axis to use a report expression, cast the string in there, and then you can apply data formatting.

[rd152343]

Hi All,

Thanks for all your replies.

Please find attached the screenshot. We have defined a date column as the member caption (first block in the screenshot), while viewing data in dynamic cube, the calendar date column displays the data in date format.

The same column while dragged and dropped into report, displays as String, while we expected to display as date field. Though we shouldnt use cast, we tried with cast function and still data was coming as String. cast(ProjectEndDate, Date) was returning an error, while trying to set data format (through data item value or report expression) displayed string value with no change in date format.

Then, we tried capturing member caption through roleValue function and typecast to DATE, seems to be working when we execute through TSQL (third block in screenshot), while dragging in to crosstab does allow to format the date (through date item value or report expression) (fourth block in screenshot).

Not sure on how to display the date format as per the client requirement. Please help us, if you have faced this issue earlier.

Paul,

OLAP doesnt support casting. Incase if we use date functions/cast function inside the data item, then the operation is performed at the Cognos server level.
How is that different from using a report expression and applying data formatting? Which will be better in terms of performance?

Date level is defined with YYYYMMDD as memberkey and member caption, and added an attribute for Date with DATE data type. The requirement is to filter based on memberkey, but the return value/display value should be the attribute data item with DATE data type. Is it possible to achieve?

Thanks!



Thanks!

[CognosPaul]

There is a big difference between doing a cast in the query and in a report expression.

In the query forces Cognos to process the query locally, on the Cognos server. This is the behavior for any action in the query that the underlying database can't support.

On the flip side, doing it in the report expression is happening as the data is being drawn on the page. The query is still processed in the cube (assuming of course you don't have any other non-supported functionality in the query) and is sent directly to the rendering engine. The output will probably look the same, but the end result will usually be much faster.

DQM adds an extra wrinkle into the mix. DQM means that some extra server processing may be happening anyway.

Your second question is having a set expression, but showing an attribute. This is definitely possible. In crosstabs and graphs there is a difference between the node and the caption. The node is building the structure of the datacontainer, be it the axis in a graph or rows in a crosstab. The label that appears inside that node defaults to the caption, but you can set it to a data item value. Drag the property you want into the query, reference that in the "properties" row of the node, and then call that by setting the text item to be data item value.

[rd152343]

Hi Paul,

Thanks for the reply.

If I understand correctly, Any unsupported function at db will be handled by the Cognos server processing locally.

Report expression at the report page will be handled by the rendering engine, which makes the display faster. But, if the expression in report expression is not supported by DB, then still the Cognos has to process the expression locally.

Please correct me if I am wrong.

Regarding second point, usage of attribute is allowing me to retain the date format. But, requirement is to hit on the level dynamically, where when user likes to see day level range, then DAY level has to he selected, which in this case attribute will be coming from DAY Level, while when user likes to see Month range for the chart, then attribute has to be come from MONTH level. This has to be achieved by using conditional prompt macro or is there any simple way to achieve this requirement?

And, when my slicer is on DAY level, can I display the data based on MONTH Level member?

Thanks!

[CognosPaul]

That's pretty much it. The report expression is still processed locally, but it's a fairly minor process. It's formatting as it's drawing the output. Compare that to having to store the output on disk and processing locally.

Does the cube designer permit you to build attributes with the same name on multiple levels? I know SSAS and DMR does. If you can, build a property on each level called Display and then use roleValue:
roleValue('Display',currentMember([Hierarchy]))

You can go up a level when the user selects a date range, but it takes a bit of work. If the user selects a range that spans months, how do you want to handle it? If the user selects a partial month, do you want to show the value for the entire month or only what was selected?

Finally, you're saying slicer. If you are showing the members in the output, make sure not to put the prompt in the slicer. Putting it in the rows or columns will be enough, anything inside those intersections will be sliced by the month/day.

[rd152343]

Hi Paul,

Thanks for your reply.
Cube designer does not allow to add a custom attribute. So have to live with existing attributes.

If the user selects a range that spans months, then, though the display will be based on months, the value/intersection of date and measure  should be based on date range selected.
Cognos allows it, but there is an issue with Multilingual naming convention which have raised a PMR (so had to reply late to this post)

My requirement is, User will be prompted to select the date range, where the input parameters to the report will be based on Start Date Key and End Date Key. Need to find out the difference between date range, and manipulate the date display - if the difference is less than 30, then DATE, if the difference is between 30 and 180, then WEEK START DATE, if the difference is between 180 and 365, then MONTH START DATE, and > 365, then display format in Year.

"Finally, you're saying slicer. If you are showing the members in the output, make sure not to put the prompt in the slicer. Putting it in the rows or columns will be enough, anything inside those intersections will be sliced by the month/day."

Just want to understand - If I add the slicer based on DATE KEY and use the DATE Level for display, will it cause an issue (Using the same dimension for slicer and display), as Report studio throws a warning when the same expression is used in Slicer and Crosstab display.

My design idea - Slicer on Date Key, display value will be selected based on difference between START DATE KEY and END DATE KEY.  Will this work or result in some issue?

Thanks for all your help!

Thanks!