Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: tarunkrdas2013 on 24 Sep 2015 11:29:53 AM

Title: Error while rendering to different report pages
Post by: tarunkrdas2013 on 24 Sep 2015 11:29:53 AM
Problem Abstract:- Report Error coming for Report Expression for Report Variable

Hi ,

I have a DMR report which renders to Report pages like HTML,PDF,No data problem is when i write below Case expression,I get error,
I have attached the error as well and report variable

case

when
(string2date(ParamValue('p_Date')) >= ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]->[all].[YESTERDAY].[2015-09-22]))then ('NO DATA')
)then ('NO DATA')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'UNITED STATES' and
(string2date(ParamValue('p_Date')) < ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]->[all].[YESTERDAY].[2015-09-22]))) then ('PDF US')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'CANADA' and
(string2date(ParamValue('p_Date')) >= ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]->[all].[YESTERDAY].[2015-09-22])))then ('PDF CN')

when ReportOutput () = 'HTML' and
(string2date(ParamValue('p_Date')) < ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]->[all].[YESTERDAY].[2015-09-22])))then ('HTML')

end


Error displayed

Invalid expression case

when
(string2date(ParamValue(&#39;p_Date&#39;)) &gt;= ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]-&gt;[all].[YESTERDAY].[2015-09-22]))then (&#39;NO DATA&#39;)
)then (&#39;NO DATA&#39;)

when ReportOutput () = &#39;PDF&#39; and
ParamDisplayValue(&#39;pmt_Country&#39;) = &#39;UNITED STATES&#39; and
(string2date(ParamValue(&#39;p_Date&#39;)) &lt; ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]-&gt;[all].[YESTERDAY].[2015-09-22]))) then (&#39;PDF US&#39;)

when ReportOutput () = &#39;PDF&#39; and
ParamDisplayValue(&#39;pmt_Country&#39;) = &#39;CANADA&#39; and
(string2date(ParamValue(&#39;p_Date&#39;)) &gt;= ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]-&gt;[all].[YESTERDAY].[2015-09-22])))then (&#39;PDF CN&#39;)

when ReportOutput () = &#39;HTML&#39; and
(string2date(ParamValue(&#39;p_Date&#39;)) &lt; ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]-&gt;[all].[YESTERDAY].[2015-09-22])))then (&#39;HTML&#39;)

end
. CRX-YXX-4010 A parsing error was found at or near the position 159 in the expression &quot;case

when
(string2date(ParamValue(&#39;p_Date&#39;)) &gt;= ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]-&gt;[all].[YESTERDAY].[2015-09-22]))then (&#39;NO DATA&#39;)
)then (&#39;NO DATA&#39;)

when ReportOutput () = &#39;PDF&#39; and
ParamDisplayValue(&#39;pmt_Country&#39;) = &#39;UNITED STATES&#39; and
(string2date(ParamValue(&#39;p_Date&#39;)) &lt; ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]-&gt;[all].[YESTERDAY].[2015-09-22]))) the&quot;.
Title: Re: Error while rendering to different report pages
Post by: Karti on 25 Sep 2015 07:20:39 AM
From first glance, I would say that the error is coming from the fact that your first 'When' statement has 2 'Then' statements. One inside the 'When' while the other is in the correct spot outside of it.
Title: Re: Error while rendering to different report pages
Post by: tarunkrdas2013 on 25 Sep 2015 04:01:27 PM
Hi karti,

my expression is

case

when
(string2date(ParamValue('p_Date')) >= cast([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday],date)then ('NO DATA')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'UNITED STATES' and
(string2date(ParamValue('p_Date')) < cast([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday],date) then ('PDF US')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'CANADA' and
(string2date(ParamValue('p_Date')) < cast([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday],date) then ('PDF CN')

when ReportOutput () = 'HTML' and
(string2date(ParamValue('p_Date')) < cast([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday],date) then ('HTML')

end

Problem is when i run the report i got above error so error might have shown twice but actual expression is the one i posted now..
Title: Re: Error while rendering to different report pages
Post by: tarunkrdas2013 on 25 Sep 2015 04:14:18 PM
i just changed with

case

when
(string2date(ParamValue('p_Date')) >= string2date([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]) then ('NO DATA')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'UNITED STATES' and
(string2date(ParamValue('p_Date')) < string2date([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]) then ('PDF US')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'CANADA' and
(string2date(ParamValue('p_Date')) < string2date([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday])then ('PDF CN')

when ReportOutput () = 'HTML' and
(string2date(ParamValue('p_Date')) < string2date([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday])then ('HTML')

end
again not working
Title: Re: Error while rendering to different report pages
Post by: tarunkrdas2013 on 25 Sep 2015 04:19:32 PM
sorry with so many expressions all i am trying to do is to convert below Render variable Report  expression of Cube Report  to DMR expression

+++++++++++++++++Cube+++++++++++
case

when
(string2date(ParamValue('p_Date')) >= string2date(substring(timestamp2string (CubeDataUpdatedOn ([C_Department_Flash].[FISCAL CALENDAR])), 1, 4) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 6, 2) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 9, 2))) then ('NO DATA')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'UNITED STATES' and
(string2date(ParamValue('p_Date')) < string2date(substring(timestamp2string (CubeDataUpdatedOn ([C_Department_Flash].[FISCAL CALENDAR])), 1, 4) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 6, 2) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 9, 2))) then ('PDF US')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'CANADA' and
(string2date(ParamValue('p_Date')) < string2date(substring(timestamp2string (CubeDataUpdatedOn ([C_Department_Flash].[FISCAL CALENDAR])), 1, 4) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 6, 2) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 9, 2)))then ('PDF CN')

when ReportOutput () = 'HTML' and
(string2date(ParamValue('p_Date')) < string2date(substring(timestamp2string (CubeDataUpdatedOn ([C_Department_Flash].[FISCAL CALENDAR])), 1, 4) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 6, 2) + '-' +
substring(timestamp2string (CubeDataUpdatedOn  ([C_Department_Flash].[FISCAL CALENDAR])), 9, 2)))then ('HTML')

end


+++++++++++++++++DMR++++++++++++++++
case

when
(cast(ParamValue('p_Date'),date) >= ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]) then ('NO DATA')

when ReportOutput () = 'PDF' and
ParamDisplayValue('pmt_Country') = 'UNITED STATES' and
(cast(ParamValue('p_Date')) < ([Hierarchical].[Fiscal Calendar].[Yesterday].[Yesterday]) then ('PDF US')


end

But no success as of now
Text only | Text with Images