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.
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
<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>
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?
A simple detail filter should handle this piece for you.
Regards,
MF.
Hi,
It works now. Thank you.