Crosstab report using Column and Row from Time Dimension

[JulioSarmiento]

All, we are trying to produce a Crosstab Report with a Date prompt.  The user will select the date prompt and the crosstab will display a year over year, month by month of the revenue.  Therefore if the user selects June 31, 2007 from the date prompt the data that should be display will look like:

                 Jan     Feb     May     Apr     Jun
Year 2007   1000    2000   1500    3000   2200
Year 2006    700     1200   1500    2000   1086

Our Time Dimension is:
TimeH
   Year
      Quarter
         Month
            Week
               Date

I have not been able to get the months across the columns of the report.  The year i can get via a filter but the months...

Thanks,
Julio

[COGNOiSe administrator]

It might be a problem with your model. Here is a GOSAR sample:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
<modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Order year" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order year]</expression></dataItem><dataItem name="Order month" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order month]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[gosales_goretailers].[Orders].[Order month]&lt;=datepart({month},?Parameter1?)</filterExpression></detailFilter></detailFilters></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="Quantity"/></dataSource></textItem></contents></crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order year" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order month" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Quantity"/><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>
<promptPages><page class="pp" name="Prompt Page1">
<pageHeader class="hp">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
</pageHeader>
<pageBody class="py">
<contents><selectDate parameter="Parameter1"/>
</contents>
</pageBody>
<pageFooter class="fp">
<contents>
<promptButton type="cancel" class="bp">
<contents/>
</promptButton>
<promptButton type="back" class="bp">
<contents/>
</promptButton>
<promptButton type="next" class="bp">
<contents/>
</promptButton>
<promptButton type="finish" class="bp">
<contents/>
</promptButton>
</contents>
</pageFooter>
</page>
</promptPages></layout>
</layouts>
</report>