Hi all,
I have a package from a cube.
One of the hiearchies is Years that has 2004 - 2009.
Prompt page allows users to select one of the years.
I want to display data for selected year and previous two years.
Since it is dimensional, <= or - + do not work.
Children, Parent, Descendants etc. also don't work as those years are on the same level.
I also tried prevmember and nextmember but does not do anything.
Is there a function that does this?
thank you for any ideas.
What type of cube is it?
How do you want the Previous Years to be captured, as a set or individually?
Here is some .xml for the Great Outdoors Company cube in which I deliver the Previous Years as a set called Years.
<report xmlns="http://developer.cognos.com/schemas/report/2.0/" expressionLocale="en"><!--RS:8.1-->
<modelPath>/content/package[@name='Great Outdoors Company']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Current Year"><expression>[great_outdoors_company].[Years].[Years].[Year]->?Year?</expression></dataItem><dataItem name="Years"><expression>set(prevmember([Current Year]),lag([Current Year],2))</expression></dataItem><dataItem name="Revenue"><expression>[great_outdoors_company].[Measures].[Revenue]</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<crosstab class="xt" refQuery="Query1">
<crosstabCorner class="xm"><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></crosstabCorner>
<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Current Year" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Years" class="cl"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="cv"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell></crosstab>
</contents>
</pageBody>
<pageHeader class="ph">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
</report>
Hi,
You can do this as follows:
In your crosstab, drag a Query Calculation to the columns area, call it EndYear, and for the expression, drag in the Year level from your time hierarchy, and follow it with a -> operator and a parameter
eg
[great_outdoors_company].[Years].[Years].[Year] -> ?Yr?
Drag a second Query Calculation to the left of EndYear in the columns area, call it MidYear, and for the expression, use the prevMember function on your EndYear data item
eg
prevMember([EndYear])
Drag a third Query Calculation to the left of MidYear, call it StartYear, and for the expression, use the prevMember function on your MidYear data item
eg
prevMember([MidYear])
This should be all you need to do - no filters etc are required.
Best regards,
MF.
Quote from: MFGF on 15 Jan 2007 06:07:44 AM
Hi,
You can do this as follows:
eg
[great_outdoors_company].[Years].[Years].[Year] -> ?Yr?
Drag a second Query Calculation to the left of EndYear in the columns area, call it MidYear, and for the expression, use the prevMember function on your EndYear data item
eg
prevMember([EndYear])
Drag a third Query Calculation to the left of MidYear, call it StartYear, and for the expression, use the prevMember function on your MidYear data item
eg
prevMember([MidYear])
This method would produce 3 separate members.Ã,Â
You could also create a single set by using the LastPeriods function against [End Year]:
lastperiods(3,[great_outdoors_company].[Years].[Years].[Year]->?Year?)