Reportnet: compare current year to last year in a report

[JDAEMS]

Dear all,

I have a fact table that looks like this:

- Product_code
- affiliate
- date
- units

From this table I want to make a report in reportnet that looks like this:

Rank Product code UNITS CURRENT YEAR UNITS LAST YEAR

This report ranks all the products no matter which affiliate by UNITS CURRENT YEAR.

My question is: How can I show the units of this year and show the units of last year next to each other?

I am stuck in this report. Please help.

Thank you very much.

Jelle

 

[schampheleer]

Have a look at the following report. It does what you want + it also provides the rank for the last year.

The report runs against the sample GO Sales and Retailers. Just copy it and then in report studio select Open Report from Clipboard. When you see a prompt to enter a year, enter 2005.

HTH, Willem

<report xml:lang="en-gb" xmlns="

http://developer.cognos.com/schemas/report/1/"><!--RS:1.1.230.2-->

<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
<querySet xml:lang="en-gb">
<BIQuery name="Query1">
<cube><factList><item aggregate="none" refItem="Product name"/><item aggregate="none" refItem="Quantity Current Year"/><item aggregate="none" refItem="Quantity Last Year"/><dataItem aggregate="none" name="Rank Current Year" sort="ascending"><expression>rank([Quantity Current Year])</expression></dataItem><dataItem aggregate="none" name="Rank Last Year"><expression>rank([Quantity Last Year])</expression></dataItem></factList></cube>
<tabularModel name="Tabular Model1"><tabularModel name="Tabular Model2"><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Orders].[Product name]</expression></dataItem><dataItem name="Quantity Current Year" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><filter><condition>[gosales_goretailers].[Orders].[Order year]=?year?</condition></filter></tabularModel><tabularModel name="Tabular Model3"><dataItem name="Product name" aggregate="none"><expression>[gosales_goretailers].[Orders].[Product name]</expression></dataItem><dataItem name="Quantity Last Year" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><filter><condition>[gosales_goretailers].[Orders].[Order year]+1=?year?</condition></filter></tabularModel><dataItem name="Product name"><expression>[Tabular Model2].[Product name]</expression></dataItem><dataItem name="Quantity Current Year"><expression>[Tabular Model2].[Quantity Current Year]</expression></dataItem><dataItem name="Quantity Last Year"><expression>[Tabular Model3].[Quantity Last Year]</expression></dataItem><filter><condition>[Tabular Model2].[Product name]=[Tabular Model3].[Product name]</condition></filter></tabularModel><governor name="crossProductAllowed" value="0"/></BIQuery>
</querySet>
<layoutList>
<layout>

<pageSet>

<page name="Page1">
<pageBody>
<list refQuery="Query1">
<listColumnTitles><listColumnTitle><textItem><queryItemRef refItem="Rank Current Year" content="label"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef refItem="Rank Last Year" content="label"/></textItem></listColumnTitle><listColumnTitle><textItem><queryItemRef refItem="Product name" content="label"/></textItem></listColumnTitle><listColumnTitle><textItem><text>Quantity Current Year</text></textItem></listColumnTitle><listColumnTitle><textItem><text>Quantity Last Year</text></textItem></listColumnTitle></listColumnTitles>
<listColumns><listColumn><textItem><queryItemRef refItem="Rank Current Year"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Rank Last Year"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Product name"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Quantity Current Year"/></textItem></listColumn><listColumn><textItem><queryItemRef refItem="Quantity Last Year"/></textItem></listColumn></listColumns>
<style>
<CSS value="border-collapse:collapse"/>
</style>
<XMLAttribute name="RS_ListGroupInfo" value=""/></list>
</pageBody>
<pageHeader>
<block class="reportTitle">
<textItem class="reportTitleText">
<text/>
</textItem>
</block>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<table>
<tableRow>
<tableCell>
<textItem>
<expression>AsOfDate()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<text>- </text>
</textItem>
<textItem>
<expression>PageNumber()</expression>
</textItem>
<textItem>
<text> -</text>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<expression>AsOfTime()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableRow>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page></pageSet></layout>
</layoutList>
</report>

 

[DoubleD]

Just use a calculated sum to get UNITS CURRENT YEAR and UNITS LAST YEAR.
If you're using Oracle, use the DECODE function. If you're using SQL Server use the IF THEN ELSE statement.

Here's the calculation for UNITS CURRENT YEAR.

 IF Year(?YearPrompt?) = Year(Now()) THEN [i]UNITS[/i] ELSE 0

Pain is stress leaving the body.

DoubleD