Why does Impromptu display "#FORMAT#" when displaying different Dates?

[macrus]

We have an impromptu Series 7 query which is extracting data from DB2. The data in question is our 'calendar' table, with each day in different formats.

eg. 1st January 2003, 2003-Jan, 2003-01-01, 2002-03.

Impromptu had a problem displaying "2002-03" but did gave us the "#FORMAT#" values for all records in the column which was supposed to be formatted as "2003-Jan".
Has anyone come across this before and if so, any clues as to the cause of this problem.

By the way. The solution we found was to remove the column from the query and then re-insert it again. Once performed this action alleviated the issue described above.
thanks,
Macrus

 

[zhanghua]

Format symbol Description
General Shows the date value in the General format.
D Shows the day of the week as one or two digits
(1-31).
DD Shows the day of the week as two digits (01-31).
DDD Shows the day of the week as a three-letter abbreviation (Mon).
DDDD Shows the full name of the day of the week (Monday).
M Shows the month as one or two digits (1-12).
MM Shows the month as two digits (01-12).
MMM Shows the month as a three-letter abbreviation (Jan).
MMMM Shows the full name of the month (January).
YY Shows the last two digits of the year (00-99).
YYYY Shows all four digits of the year (1900-2000).
- (hyphen)
/
:
space Shows the symbol as entered.
' Shows the symbol as entered. When you return to the Format dialog box, you will see a backward slash to indicate that the apostrophe has significance as a format.
\ Shows the next character in the format. Use this symbol to treat a format symbol as a standard character. Enter two backward slashes to show a backward slash.
¡° ¡± Shows whatever is between the quotation marks.

If you specify an invalid format, Impromptu shows #FORMAT# for the selected data items.

 

[macrus]

Thanks for the help with Impromptu Date formats BUT...

this data is actually read from DB2 as a string. We get the feeling that the string is close to a valid Impromptu format by mistake. We want the value to appear and be read as a string not a date.

If anyone has come across this #FORMAT# problem before I'd be interested to hear how you got there and how you worked around the problem.

thanks again

 

[griffindm]

How is Impromptu seeing the column as a datatype? Check under the Report | Query menu option and Profile access tab. Select the 'Query Column' radio button and read the data definition. This will help us understand what is going on.

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

 

[macrus]

Dave,

Following your instruction I have noted that Impromptu is seeing this column as "General".

 

[sln007]

First step would be to have the program or programs that populate the particular "DATE" data be changed to a single standard format. If this "DATE" column has varied formats....then you've got serious data-integrity problems.
Solve this and then you'll have far better results.

 

[griffindm]

sln007 is on target. If you want to present the data in a consistent format, you have to get the data in the table consistent. Impromptu should be able to present the column as a string in the same manner it is in the database without issues. As it is not a true date, however, it can neither interpret the string to a consistent date format, nor convert it to a true date without consistency in the table data.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

 

[macrus]

Thanks for the ideas guys!

Here are 3 sample records from our DB2 table:

DATE FIN_YR CAL_YR MON_LBL
1/1/03 2002-03 2003 2003-Jan
2/1/03 2002-03 2003 2003-Jan
... ... ... ...
1/8/03 2003-04 2003 2003-Aug

ALL values in each of the columns within the DB2 table are formatted the same. Impromptu has had an issue determining a format for the column named "MON_LBL" It seems to us, that Impromptu has attempted to define the string "2003-Aug" as a Date automatically.
Thanks for the tips