Outer joins in FM Manager

[Arvind]

Hello all,

I have a question on Outer joins in FM manager.  The outer join does not return the correct results when a filter is added. How do I define an outer join such that a filter is applied *after* the outer join is done? The database I am using is Oracle.  In oracle client, the sql needs to be structured in a certain way in order to get the correct results. I am not sure how I mimic that in FM manager. Here is an example:


SELECT * FROM MASTERTABLE A LEFT OUTER JOIN CHILDTABLE B ON A.KEY=B.KEY
WHERE CHILDTABLE = 'somefilter'

The above query does not produce correct results.  The filter needed to be applied on the join, like this:

SELECT * FROM MASTERTABLE A LEFT OUTER JOIN CHILDTABLE B ON A.KEY=B.KEY and CHILDTABLE = 'somefilter'.

In cognos, the filters are being passed from different prompts and so they end up as an Where clause rather than the Join clause. 

Has anyone came across this and have a solution/workaround? Let me know if the example about does not make sense.


TIA.

[cognostechie]

You can apply the filter on the join itself. Click on the ellipsis in the bottom window and it will let you qrite your own SQL for the join, then you can specify:

Mastertable.Key = Childtable.Key and Childtable = somefilter

[Arvind]

Yeyes, buy filter is actually coming from a prompt which I won't know until the report is run.

[cognostechie]

Mastertable.Key = Childtable.Key and Childtable = #prompt('Prompt Name','char')#

I am not sure about whether you are on the right track. Modelling is different than writing SQL in Oracle and you have to percieve it in a different way. Results should be the same but modelling is not done keeping in mind that it should generate the same SQL.

As an example, outer joins work in case of confirmed dimensions which have 2 Facts joined to a common dimension. In that case cognos will generate two seperate queries and join them with a full outer join. If the user selects a value of Cust-ID from the prompt then it should filter both the facts for that Cust-ID. The way that works is that the filter is applied to the Customer Dimension (not the Facts) so it gets applied to both the inner queries because both the Facts will have it's own queries but both the queries are using the same Customer Dim. The join in that case is on the outer query, not the inner query.

What you should do in this case is to expose the field of Childtable in the Presentation Layer and use that in the prompt anf the filter, that way the user selected value will get applied as a filter to that field of Childtable. In that case the join in FM should simply be

Mastertable.Key = Childtable.Key

[Arvind]

Thanks for the response.  I provided an SQL example hoping that'd help in better understanding the problem I am facing.  I understand modeling is different from using SQL.  Thanks for the solution that you provided.  This is how I have designed the model, but still not getting the expected results.

In order to demonstrate the requirement, below is the report specs created from the sample package - Go Sales (Query).  This one filters the year 2007 just to limit the data.  I am showing countries as rows and year & quarter as the columns with quantity shipped as a measure.  I am using an outer join the report studio. This report works exactly how I want it. All quarters are listed in the column header even if the data is not present.

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10"

expressionLocale="en-us">


<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales

(query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>

<layouts>
<layout>
<reportPages>
<page

name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>


<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<crosstab

horizontalPagination="true" refQuery="Query4" name="Crosstab1">
      <crosstabCorner>
        <contents/>
        <style>
          <defaultStyles>
            <defaultStyle refStyle="xm"/>
          </defaultStyles>
        </style>
      </crosstabCorner>
     
     
     
      <style>
        <CSS value="border-collapse:collapse"/>
        <defaultStyles>
          <defaultStyle refStyle="xt"/>
        </defaultStyles>
      </style>
    <noDataHandler>
        <contents>
          <block>
            <contents>
              <textItem>
                <dataSource>
                  <staticValue>No Data Available</staticValue>
                </dataSource>
                <style>
                  <CSS value="padding:10px 18px;"/>
                </style>
              </textItem>
            </contents>
          </block>
        </contents>
     

</noDataHandler><crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabN

estedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity

shipped"

edgeLocation="e2"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></

contents><factCell><style/></factCell><style><defaultStyles><defaultStyle

refStyle="ml"/></defaultStyles></style></crosstabNodeMember></crosstabNodeMembers></crosstab

Node></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter"

edgeLocation="e3"><style><defaultStyles><defaultStyle

refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dat

aSource></textItem></contents><sortList><sortItem refDataItem="Quarter"

sortOrder="ascending"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode>

</crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year"

edgeLocation="e1"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></

contents><factCell><style/></factCell><style><defaultStyles><defaultStyle

refStyle="ml"/></defaultStyles></style><sortList><sortItem refDataItem="Year"

sortOrder="ascending"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode>

</crosstabColumns><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource

></textItem></contents><style><defaultStyles><defaultStyle

refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><cross

tabNodeMembers><crosstabNodeMember refDataItem="Country"

edgeLocation="e4"><style><defaultStyles><defaultStyle

refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dat

aSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></cr

osstabRows></crosstab></contents>
</pageBody>
<pageHeader>
<contents>


<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>


<contents>


<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>


<dataSource>


<staticValue/>


</dataSource>


</textItem>


</contents>
</block>
</contents>
<style>


<defaultStyles>


<defaultStyle refStyle="ph"/>


</defaultStyles>
<CSS

value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>


<tableRows>


<tableRow>


<tableCells>


<tableCell>


<contents>


<date>


<style>


<dataFormat>


<dateFormat/>


</dataFormat>


</style>


</date>


</contents>


<style>


<CSS value="vertical-align:top;text-align:left;width:25%"/>


</style>


</tableCell>


<tableCell>


<contents>


<pageNumber/>


</contents>


<style>


<CSS

value="vertical-align:top;text-align:center;width:50%"/>


</style>


</tableCell>


<tableCell>


<contents>


<time>


<style>


<dataFormat>


<timeFormat/>


</dataFormat>


</style>


</time>


</contents>


<style>


<CSS value="vertical-align:top;text-align:right;width:25%"/>


</style>


</tableCell>


</tableCells>


</tableRow>


</tableRows>


<style>


<defaultStyles>


<defaultStyle refStyle="tb"/>


</defaultStyles>


<CSS value="border-collapse:collapse;width:100%"/>


</style>
</table>
</contents>
<style>


<defaultStyles>


<defaultStyle refStyle="pf"/>


</defaultStyles>
<CSS

value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems"

value="true" output="no"/><XMLAttribute name="listSeparator" value=","

output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-10T18:59:05.697Z"

output="no"/></XMLAttributes><queries><query

name="Query1"><source><model/></source><selection><dataItem name="Quantity shipped"

aggregate="total"><expression>[Inventory (query)].[Inventory].[Quantity

shipped]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1"

output="no"/><XMLAttribute name="RS_dataUsage" value="fact"

output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none"

rollupAggregate="none"><expression>[Inventory

(query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1"

output="no"/><XMLAttribute name="RS_dataUsage" value="attribute"

output="no"/></XMLAttributes></dataItem><dataItem name="Quarter" aggregate="none"

rollupAggregate="none"><expression>[Inventory

(query)].[Time].[Quarter]</expression><XMLAttributes><XMLAttribute name="RS_dataType"

value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute"

output="no"/></XMLAttributes></dataItem><dataItem name="Country" aggregate="none"

rollupAggregate="none"><expression>[Inventory

(query)].[Branch].[Country]</expression><XMLAttributes><XMLAttribute name="RS_dataType"

value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute"

output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpr

ession>[Year]=2007</filterExpression></detailFilter></detailFilters></query><query

name="Query2">
      <source>
        <model/>
      </source>
      <selection><dataItem name="Quarter" aggregate="none"

rollupAggregate="none"><expression>[Inventory

(query)].[Time].[Quarter]</expression><XMLAttributes><XMLAttribute name="RS_dataType"

value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute"

output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none"

rollupAggregate="none"><expression>[Inventory

(query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1"

output="no"/><XMLAttribute name="RS_dataUsage" value="attribute"

output="no"/></XMLAttributes></dataItem></selection>
   

<detailFilters><detailFilter><filterExpression>[Year]=2007</filterExpression></detailFilter>

</detailFilters></query><query name="Query4">
      <source>
       
      <joinOperation>
      <joinOperands>
        <joinOperand cardinality="0:N"><queryRef refQuery="Query1"/></joinOperand>
        <joinOperand cardinality="1:N"><queryRef refQuery="Query2"/></joinOperand>
      </joinOperands>
      <joinFilter>
        <filterExpression>[Query1].[Quarter] = [Query2].[Quarter] and
[Query1].[Year] = [Query2].[Year]</filterExpression>
      </joinFilter>
    </joinOperation></source>
      <selection><dataItem

name="Year"><expression>[Query2].[Year]</expression></dataItem><dataItem name="Quarter"

rollupAggregate="none"><expression>[Query2].[Quarter]</expression></dataItem><dataItem

name="Quantity shipped"><expression>[Query1].[Quantity

shipped]</expression></dataItem><dataItem

name="Country"><expression>[Query1].[Country]</expression></dataItem></selection>
    </query></queries><reportName>test2</reportName></report>


following up in another post...

[Arvind]

The above code works fine the way I want it. However, I also want to make the Countries as a prompt. I want to show  all years and quarters but for a select country. Here is the report with the prompt added on the country.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>

<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<crosstab horizontalPagination="true" refQuery="Query4" name="Crosstab1">
      <crosstabCorner>
        <contents/>
        <style>
          <defaultStyles>
            <defaultStyle refStyle="xm"/>
          </defaultStyles>
        </style>
      </crosstabCorner>
     
     
     
      <style>
        <CSS value="border-collapse:collapse"/>
        <defaultStyles>
          <defaultStyle refStyle="xt"/>
        </defaultStyles>
      </style>
    <noDataHandler>
        <contents>
          <block>
            <contents>
              <textItem>
                <dataSource>
                  <staticValue>No Data Available</staticValue>
                </dataSource>
                <style>
                  <CSS value="padding:10px 18px;"/>
                </style>
              </textItem>
            </contents>
          </block>
        </contents>
      </noDataHandler><crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity shipped" edgeLocation="e2"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style/></factCell><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><sortList><sortItem refDataItem="Quarter" sortOrder="ascending"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style/></factCell><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><sortList><sortItem refDataItem="Year" sortOrder="ascending"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Country" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
<promptPages><page name="Prompt Page1">
      <pageHeader>
        <contents>
          <block>
            <contents>
              <textItem>
                <dataSource>
                  <staticValue/>
                </dataSource>
                <style>
                  <defaultStyles>
                    <defaultStyle refStyle="tt"/>
                  </defaultStyles>
                </style>
              </textItem>
            </contents>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="ta"/>
              </defaultStyles>
            </style>
          </block>
        </contents>
        <style>
          <defaultStyles>
            <defaultStyle refStyle="hp"/>
          </defaultStyles>
        </style>
      </pageHeader>
      <pageBody>
        <contents><block><contents><generatedPrompt parameter="parameter" required="true"></generatedPrompt></contents><style><CSS value="margin-bottom:12px"/></style></block></contents>
        <style>
          <defaultStyles>
            <defaultStyle refStyle="py"/>
          </defaultStyles>
        </style>
      </pageBody>
      <pageFooter>
        <contents>
          <promptButton type="cancel">
            <contents/>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="bp"/>
              </defaultStyles>
            </style>
          </promptButton>
          <promptButton type="back">
            <contents/>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="bp"/>
              </defaultStyles>
            </style>
          </promptButton>
          <promptButton type="next">
            <contents/>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="bp"/>
              </defaultStyles>
            </style>
          </promptButton>
          <promptButton type="finish">
            <contents/>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="bp"/>
              </defaultStyles>
            </style>
          </promptButton>
        </contents>
        <style>
          <defaultStyles>
            <defaultStyle refStyle="fp"/>
          </defaultStyles>
        </style>
      </pageFooter>
      <style>
        <defaultStyles>
          <defaultStyle refStyle="pp"/>
        </defaultStyles>
      </style>
    </page></promptPages></layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-10T18:59:05.697Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Quantity shipped" aggregate="total"><expression>[Inventory (query)].[Inventory].[Quantity shipped]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Inventory (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[Inventory (query)].[Time].[Quarter]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Country" aggregate="none" rollupAggregate="none"><expression>[Inventory (query)].[Branch].[Country]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression>[Year]=2007</filterExpression></detailFilter></detailFilters></query><query name="Query2">
      <source>
        <model/>
      </source>
      <selection><dataItem name="Quarter" aggregate="none" rollupAggregate="none"><expression>[Inventory (query)].[Time].[Quarter]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Inventory (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
    <detailFilters><detailFilter><filterExpression>[Year]=2007</filterExpression></detailFilter></detailFilters></query><query name="Query4">
      <source>
       
      <joinOperation>
      <joinOperands>
        <joinOperand cardinality="0:N"><queryRef refQuery="Query1"/></joinOperand>
        <joinOperand cardinality="1:N"><queryRef refQuery="Query2"/></joinOperand>
      </joinOperands>
      <joinFilter>
        <filterExpression>[Query1].[Quarter] = [Query2].[Quarter] and
[Query1].[Year] = [Query2].[Year]</filterExpression>
      </joinFilter>
    </joinOperation></source>
      <selection><dataItem name="Year"><expression>[Query2].[Year]</expression></dataItem><dataItem name="Quarter" rollupAggregate="none"><expression>[Query2].[Quarter]</expression></dataItem><dataItem name="Quantity shipped"><expression>[Query1].[Quantity shipped]</expression></dataItem><dataItem name="Country"><expression>[Query1].[Country]</expression></dataItem></selection>
    <detailFilters><detailFilter><filterExpression>[Country] = ?parameter?</filterExpression></detailFilter></detailFilters><queryHints><useSQLJoinSyntax value="explicit"/></queryHints></query></queries><reportName>test3</reportName></report>

Once I added the prompt, the outer join no longer works. For example, if I select Australia, it shows only three quarters in the year 2007. The outer join gets converted to an inner join.

I know my original question was how to do this in the FM but if this can be done in RS for now, that is good enough for me. Another interesting thing I noticed that there is an option in the Query object in the Report studio which allows the filter condition to be added to the On clause or to Where clause.  I chose 'Explicit' meaning the condition is added to On clause, but I still not getting the output that I am looking for.

Thanks for your help.