List report sub total

[nayitian]

Hi

I am having a list report like this
Col    M1   M2   M3
a       1     1      1
b       2      2     2
c        3     3     3

How can I add a sub total row of b+c, I was using a sub query to calculate b+c, then union the result set, it worked but the performance was very bad. Is there any function to calculate sub total of partial data set?

Thanks.

[pricter]

Try to grouped them the desired set.
For example create a data item
if ([Product line] in ('Camping Equipment', 'Golf Equipment')) then ('1') else ([Product line])
and group the list with the "group" data item.

Check the below report specification if it was you want

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en">
<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>

<list horizontalPagination="true" name="List1" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="Data Item1"><listFooter><listRows><listRow><rowCells><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Data Item1"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="of"/></defaultStyles></style><conditionalStyles><conditionalStyleCases refVariable="Boolean1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Total(Quantity)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="os"/></defaultStyles></style><conditionalStyles><conditionalStyleCases refVariable="Boolean1"><conditionalStyle refVariableValue="1"/></conditionalStyleCases><conditionalStyleDefault><CSS value="display:none"/></conditionalStyleDefault></conditionalStyles></rowCell></rowCells></listRow></listRows></listFooter></listGroup></listGroups></list></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"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]</expression></dataItem><dataItem name="Data Item1"><expression>if ([Product line] in ('Camping Equipment', 'Golf Equipment')) then ('1') else ([Product line])</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="Total(Quantity)" aggregate="calculated" rollupAggregate="total"><expression>[Quantity]</expression></dataItem></selection></query></queries><reportVariables><reportVariable type="boolean" name="Boolean1">
<reportExpression>[Query1].[Data Item1]='1'</reportExpression>
<variableValues>
<variableValue value="1"/>
</variableValues>
</reportVariable></reportVariables></report>


[nayitian]

Hi

Thank you so much for helping on this. That's a nice solution.
Furthermore, if I only need subtottal of group '1', all the rest should not be in the list report, is this doable?

[MFGF]

A simple detail filter should handle this piece for you.

Regards,

MF.

[nayitian]

Hi,

It works now. Thank you.