Date Prompts (format) 1

[BeckyBoop]

From the catalog I am bringing in a date field (i.e. 06/28/2002). I have created a prompt to filter Start Date and End Date. The customer wants the date in the format of (dd-mon-yyyy). I've created the string (finally managing to convert date to string, substring, etc.) and am showing 28-Jun-2002 in the actual report. However, when the prompt displays, unfortunately it shows both 06/28/2002 AND 28-Jun-2002 (1 each for start date and 1 each for end date). Anyone know of a work around for this? I've tried creating as catalog prompts and report prompts, etc.
Thanks in advance

 

[griffindm]

BeckyBoop,

Welcome to the Cognos forum. I'm not sure I understand all aspects of the problem. I'll tackle the two questions separately.

Double Prompts
To really understand why this is happening I'd need you to post the text of your filter in the report. Also, are you defining the prompts twice (once for the filter, and again for display in the report)? I assume that each of the date prompts (both start and end) are appearing twice. There was a version of six that I encountered that had a reported problem I experienced myself with single prompts appearing multiple times, but that was fixed in a RBF release shortly afterwards.

Date Formatting
You need the dates in the format dd-mon-yyyy for both the report and the prompts? Are the prompts (start date and end date) setup as strings or dates?

Impromptu can handle different date formats for display in the report without converting the dates to strings. Just right-click on the report data and select 'Format' from the dialog option. The date formats offered in the dialog are starting points only. You can customize them as needed. Just type in 'dd-mmm-yyyy' in the 'Normal' box of the format screeen.

The formatting of the prompts is a little more complex. I find it's usually best to leave them as dates, as this allows Impromptu to validate the date before accepting the prompt. I usually handle date prompt formatting and date validation in macros for the real picky users. Cognos provides the following instruction to change the formatting of date prompts (as of version 6 RBF3) in the following instruction, though I have never been able to get it to work myself. (Don't get discouraged. I didn't try too hard). If you follow these instructions and they work for you, you will not even have to change the date formats in the report manually, as Impromptu will follow the regional settings for the date display in the report.

Description:

Is it possible to change the default date prompt format for IWR and Impromptu? For example: Date in Prompt Manager is displayed as 2001-01-01 format but would like it to show as 01/01/2001?

Solution:

As of version 6.0 RBF3 of Impromptu and IWR you can format the date for prompts through the Regional Settings in the control panel. These changes will be reflected after launching the next Impromptu or ImpServer session.

In Control Panel -> Regional Settings, in the Date tab, under Short Date Style, specify the date format desired. This is the format that will be displayed in Impromptu.

Note* Specifying English (United States) regional setting causes the default date prompt value to change.

To work around this, try changing to the English(Canadian) regional setting.

I hope this helps,

Dave Griffin

 

[griffindm]

Becky,

Please post back if any of these suggestions are helpful. It's always gratifying to know if the suggestions work out, and sometimes the person helped can add additional info to help the next person too!

Regards,

Dave Griffin ;-)

 

[BeckyBoop]

Thanks for your help Dave,

Here's the filter I'm currently using. All are 'date fields' in order for the 'between' to work.

"Date Created Conv between ?Start Date? and ?End Date?..."

Yes, when a user opens the report, they are prompted for Start Date and End Date twice - one for each format. I only want the dd-mon-yyyy prompt. I converted the date to a string so it would show correctly (I don't need them to show in the actual report, but I do need them to show as a Prompt Variable. Currently the Prompt Variable defaults to year-mm-dd. I'm sure it's because it is taking the first prompt format.

Thanks for the help; any suggestions are greatly appreciated!

Becky

 

[griffindm]

I'm sorry Becky, I'm getting a little lost in your description. In Prompt Manager, what are the prompts you see and what are their types? As in:

Prompt_Name Prompt_Type Default

?Start_Date? Date? String? ?
etc.

A complete listing will help in figuring out what's going on.

Did you try the Regional Setting option? I curious whether you had more luck than I did.

Dave Griffin ;-)

 

[BeckyBoop]

Dave,

I didn't change the Regional Settings option. If I change this, won't this be the default setting? This is "supposed" to be the only report where I will be using the dd-mon-yyyy format.

These are the prompts showing in Prompt Manager:

Prompt_Name: Start Date
Type: Type in
Data_Type: date
Default Value: 2001-12-01

Prompt_Name: End Date
Type: Type in
Data_Type: date
Default_Value 2002-05-01

Prompt_Name: Report Start Date
Type: Type in
Data_Type: string
Default_Value: 01-Jun-2002

Prompt_Name: Report End Date
Type: Type in
Data_Type: string
Default_Value: 01-Jun-2002

If I elimiate the Start Date and End Date prompts, the filter in my report doesn't work.

Thanks,
Becky

 

[griffindm]

Becky,

Four different prompts in the Prompt Manager, all used in the report, means four prompts appear when the report is run.

Sorry for the mixup in Regional Settings. Not knowing where you were at, I thought that the dd-mmm-yyyy format would be acceptable for all of your reports.

What you will have to do to make this work is to remove the two true DATE prompts from the report, and replace them in the filter with two calculated columns where you convert the strings into true dates using the To_Date function. The 'nlsparms' value to use with the function is 'DD-Mon-YYYY'.

Thus your calculation for StartDate is:
to_date(ReportStartDate,'DD-Mon-YYYY')...

This is a database-only function, and will only appear if your database supports it. I know it works for Oracle, and probably works for SQL Server as well. If the function does not appear, you may have to parse the individual parts of the string date and use another function to rebuild the date such as 'make-datetime', which runs locally.

The major drawback to this is that Impromptu will not validate the date prompts at time of entry, and the user may get a database error or no report data if a bad date is used with the prompts.

Otherwise, this should do it for you.

HTH,

Dave Griffin

 

[BeckyBoop]

Dave

Great - YEA! Your solution worked beautifully. Thank you so very much!

Becky