CognosScript loses column formatting

[flarles]

When creating a macro in Impromptu to create a report and email it, part of the text in the subject and body is a date that is extracted from the Impromptu report.

We want the date to display in the email as "January 2007"; however it also comes through as "31/01/2007" even it we format it in the Impromptu column or try to reformat it within the macro as:

dim MyDate
MyDate = objImpReport.GetDataValue(4, CurrentRow)
MyDate = Format(MyDate,"mmmm yy")".

How can we preserve the correct format when using CognosScript?

 

[drlex]

Try setting MyDate as a string.

Dim MyDate as String

soi la, soi carré

 

[flarles]

Thanks for the suggestion.

I tried "Dim MyDate as String" but got the same result.

 

[drlex]

Odd, as I have scripts that do the same thing. Is the data field at column 4 a date, or just a string that looks like a date?
If you step through the 'script using F8, what does the content of the MyDate variable look like as it goes through the lines you post?

soi la, soi carré

 

[flarles]

Yes, it seems very odd and is extremely frustrating.

I followed your suggestion, and the variable comes through as "myDate$: "30/09/2007

 

[drlex]

And stays as such after the line
MyDate = Format(Mydate,"Mmmm yy")?
Unless Cognos is interpreting 30/09/2007 as the 9th of the 30th month, and therefore incapable of being formatted as a date, I can't see why that line won't reformat as per your requirements. If so, you might need to check system and cognos date formatting or cobble together a line like
MyDate = Format(Mid(Mydate),3,3)+Left(Mydate),3)+Right(Mydate,4),"Mmmm yy") to reformat the variable.

soi la, soi carré

 

[flarles]

I finally got it to work with a slight tweak of your latest suggestion.

MyDate = Format(Mid(MyDate,4,2)+"/"+Left(MyDate,2)+"/"+Right(MyDate,4),"Mmmm yy")

Thanks again for your help. Much Appreciated.

 

[drlex]

Glad you're sorted - looks like Cognos was 'expecting' m-d-y order. I recall a similar issue with an older version of Impromptu on install which was solved by a couple of amendments to the cerlocale.xml file on the <DateTimeFormats> section of the relevant locale.

soi la, soi carré