Hi Guys, I am really stuck at revenue calculations for Year ago. When the user select a range of calender dates for instance Mar 1 2009 to Mar 31 2010. He wants to see revenue for that time period and also Previous years calculations ie. Mar1 2008 to Mar 31 2009.
I tried _add_years(?calender_date?,-1) with if and case statements but I am not getting Year ago calculations, but I get 0. Do you have any thoughts about this ?
I Tried this by just putting in the calculation rather than in filters for prompts but I am getting 0 even for current select
<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-us">
<modelPath>/content/folder[@name='AN Business Intelligence']/folder[@name='IT - Information Technology']/folder[@name='Cognos Samples']/folder[@name='Packages']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Current Year Select Revenue"><expression>IF (CAST([Sales (query)].[Time dimension].[Date],DATE) BETWEEN ?START DATE? AND ?END DATE?) THEN ([Sales (query)].[Sales fact].[Revenue])
ELSE (0)
</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<list refQuery="Query1" horizontalPagination="true" name="List1">
<style>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
</list>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<classStyles>
<classStyle name="pd_1" label="Excellent">
<CSS value="background-color:#009933; color:#FFFFFF;"/>
</classStyle>
<classStyle name="pd_2" label="Very good">
<CSS value="background-color:#FFFFFF; color:#009933;"/>
</classStyle>
<classStyle name="pd_3" label="Average">
<CSS value="background-color:#FFFFFF; color:#CC9900;"/>
</classStyle>
<classStyle name="pd_4" label="Below average">
<CSS value="background-color:#FFFFFF; color:#990000;"/>
</classStyle>
<classStyle name="pd_5" label="Poor">
<CSS value="background-color:#990000; color:#FFFFFF;"/>
</classStyle>
</classStyles>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>