Date prompt

[shilpahv]

Hi all,
         I am using cube as a package to create reports.
The issue i am facing is, when i use a value prompt for date, it displays the available dates and when selected, data got is fine but if i use a date prompt, there is a data mismatch for selected date.
Can anyone tell how to go about with this.

Thankyou  :)

[MFGF]

Hi,

The issue is that within your cube, each date is held as a member which has an ID and a caption, and neither of these are real relational dates (as you would find in a relational database).  The members are identified by their MUN (Member Unique Name) which includes the path and location used to navigate to the member within its dimensional hierarchy, but are displayed as caption values, which are usually text fields.

If you must display a date prompt, I guess the only option you have is to extract the various date components from your member captions (year, month, day) and then use a _makeTimeStamp() function to convert them into a real date, then filter on this.  To be honest, though, you're going to make your report extremely inefficient by doing this, as you will end up coding a detail filter, which is a relational reporting technique, not a dimensional reporting technique.  A better approach than using a detail filter would probably be to code a query calculation to display your dates using the expression

set([your date hierarchy] -> ?param?)

This will result in a tree prompt being generated, and your users will be able to select whichever date members (from any level) they are interested in.

Regards,

MF.

[shilpahv]

Thanks for the input,we used set function and it worked.
But we want to have a date prompt on the prompt page according to which data gets filtered.Our report has many lists,each of which will get filtered on different date intervals.

Can you please suggest any possible solutions to preserve the calendar prompt and filter data accordingly.

Thanks

[MFGF]

Hi,

I did suggest a possible solution - using a calculation to extract the year, month and day parts from your captions and then creating a date from them using the _makeTimeStamp() function.  I just wanted to point out how inefficient this will be, though - it will probably have a seriously negative impact on your report performance.

Regards,

MF.

[shilpahv]

Hi,
    I didn't find maketimestamp() function in the report studio.I am working on cognos8.
The set function worked fine.But I need to have calculations on date like prior 3 weeks data etc..I believe this set function will not work for such type of calc.
Can u suggest on something for this.
Thanks.

[MFGF]

Hi,

I am using Cognos 8, and there is definitely a _make_timestamp() function [note - I forgot the underscore in the middle in my previous post, but also note that the function name starts with an underscore].  You can find it in the Business Date/Time Functions folder.

Regards,

MF.