If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Error in Report Expression

Started by colt, 02 May 2011 04:42:40 AM

Previous topic - Next topic

colt

Hi,
at the moment Cognos is driving me crazy.
I have a report based on a cube.
Within the report, I have a Text-Element with Report Expression:

CASE WHEN ParamValue('Periodenauswahl') is null
          THEN timestamp2date (_last_of_month (_add_years (_add_months (ReportDate ();-1);-1)))
          WHEN ((ParamValue('Periodenauswahl') is not null) AND (Substring(ParamValue('Periodenauswahl');6;2)='13'))
          THEN timestamp2date(_last_of_month (_add_years (date2timestamp(string2date (SUBSTRING(ParamValue('Periodenauswahl');1;4) + '-12-01'));-1) ))
         ELSE timestamp2date(_last_of_month (_add_years (date2timestamp(string2date (SUBSTRING(ParamValue('Periodenauswahl');1;4) + '-' + Substring(ParamValue('Periodenauswahl');6;2) + '-01'));-1) ))

END

ParamValue of Periodenauswahl  YYYY0001, YYYY002, YYYY003....YYYY012, YYYY013
YYYY for the Year, 013 for Year end period.
The report expression above should show the last day of the same period of the last year.
It does exactly what it should, except on 013 periods.
IF I chose 2010011 then '2009-11-30' is shown.
Here I get a '--' displayed in my report.
The very strange thing: If I replace the definition in the ELSE statement to string2date('2004-12-08'), then if I chose 2010013, the date 2009-12-31 is shown correctly.

CASE WHEN ParamValue('Periodenauswahl') is null
          THEN timestamp2date (_last_of_month (_add_years (_add_months (ReportDate ();-1);-1)))
          WHEN ((ParamValue('Periodenauswahl') is not null) AND (Substring(ParamValue('Periodenauswahl');6;2)='13'))
          THEN timestamp2date(_last_of_month (_add_years (date2timestamp(string2date (SUBSTRING(ParamValue('Periodenauswahl');1;4) + '-12-01'));-1) ))
         ELSE string2date('2004-12-08')
END


Any ideas why the first expression does not work ?