[RS]Logic for Previous members or Next members of same level

[Suraj]

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.

[MDXpressor]

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]-&gt;?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>

[MFGF]

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.

[MDXpressor]

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?)