How to do a LEFT OUTER or INNER JOIN

[pw]

Hi folks,

i am trying to do a benchmarking report, which compares (in a cross-tab) some €-sums of different sales-persons.
So i get a sum auf every person for the last month, this works perfect.

Now i need another sum of each person, which shows me their sum of sales of the last year.

so my cross-tab should look something like this:

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  | sales_last_month | sales_last_month_last_year
-------------------------------------------------------------------------
person1
person2
person3


Example:Ã,  Sales last month is now complete sum of october 2005.Ã, 
Sales last month last year would be October 2004!

I need a left join, could also be inner join (we have values of each person).

I tried to combine to tabular models, but report net says, i am not allowed to execute "Cross-joins"...... (but that wasn't what report net was supposed to do :-) )

So if anyone has a hint for me, i would be very thankful!

Cheers,
Philipp



PS:
I tried to use two tabular models for the main query (one model for the current year_last_month, one for last_year_last_month) for my cross-tab. i always get the error: RSV-RND-0052Ã,  Failed to validate the layout due to missing metadata.

[sir_jeroen]

In a crosstab most times an outer join ain't necessary.. No values are represented as empty cells

But take a look at this code

Code Select Expand

<report xml:lang="en" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
<querySet xml:lang="en">
<BIQuery name="Query1">
<cube><dimension name="Staff name"><level name="Staff name"><item refItem="Staff name" aggregate="none" key="true" isHeader="true"/></level></dimension><dimension name="Order year"><level name="Order year"><item refItem="Order year" aggregate="none" key="true" isHeader="true"/></level><level name="Order month"><item refItem="Order month" aggregate="none" key="true" isHeader="true"/></level></dimension><factList><item refItem="Revenue" aggregate="automatic" isHeader="true"/></factList></cube>
<tabularModel><dataItem name="Staff name" aggregate="none"><expression>[gosales_goretailers].[Sales reps].[Staff name]</expression></dataItem><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="Revenue" aggregate="total"><expression>[gosales_goretailers].[Orders].[Revenue]</expression></dataItem><filter use="required" summary="false"><condition>[Order month]=10</condition></filter></tabularModel></BIQuery>

<BIQuery name="Query1_crosstab"><cubeReference refQuery="Query1"/><summary><rowEdge><level refLevel="Staff name"><item refItem="Staff name"/></level></rowEdge><columnEdge><level refLevel="Order year"><item refItem="Order year"/></level><level refLevel="Order month"><item refItem="Order month"/></level></columnEdge><dataCells><item refItem="Revenue"/></dataCells></summary></BIQuery></querySet>
<layoutList>
<layout>

<pageSet>

<page name="Page1">
<pageBody>
<crosstab refQuery="Query1_crosstab">
<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabCorner/>
<crosstabRows/>
<crosstabColumns/>
<crosstabLevel refLevel="Staff name"><textItem><queryItemRef refItem="Staff name"/></textItem></crosstabLevel><crosstabLevel refLevel="Order year"><textItem><queryItemRef refItem="Order year"/></textItem></crosstabLevel><crosstabLevel refLevel="Order month"><textItem><queryItemRef refItem="Order month"/></textItem></crosstabLevel><crosstabCell><textItem><queryItemRef refItem="Revenue"/></textItem><member refMember="Revenue"/></crosstabCell></crosstab>
</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>

[bdybldr]

Hi Phillipp,
Have you tried setting the Cross Product Allowed property to "Allow" under the query properties? 

[bdybldr]

I just thought of another solution...

If it's not necessary to have a crosstab report, you could build a list report and use a case or If Then construct to define last month and last month last year.

Ex. for last month

If
([Date] between '01-OCT-2005' and '31-OCT-2005')
THEN
([sales_amt])
ELSE
(0)

Do the same thing for last month last year (date between '01-OCT-2004' AND '31-OCT-2004')

Then set aggregate to total for both fields.  Hope this helps.