Running-Product

There may be occasion where you need to do a running product.

Actual Running Total Running Product
3 3 3
6 9 18
8 17 144
2 19 288
1 20 288

Unfortunately there is no Cognos or database function to do this. Fortunately we have grade school math to come to the rescue! Thanks to the magic of natural logarithms, transcendentalism and running-totals we can make it happen.

The first step is to find the Natural Logarithm of the measure. Use the Cognos function ln: ln([Measures].[Count]). Wrap that in a running total running-total(ln([Measures].[Count])). Now we’ll use the transcendental number 2.71828… (or e) for short. e to the power of the running-total will return the running-product. exp(running-total(ln([Measures].[Count])))

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-nz">
				<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="Product type"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product type"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Count"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Count"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Running-Product"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Running-Product"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
								</pageBody>
							</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="Running-Product"><expression>exp(running-total(ln([Count]) for [Product line]))</expression></dataItem><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Returned items (query)].[Product].[Product line]</expression></dataItem><dataItem name="Count" aggregate="total"><expression>running-count(1 for [Product line])</expression></dataItem><dataItem name="Product type" aggregate="none" rollupAggregate="none"><expression>[Returned items (query)].[Product].[Product type]</expression></dataItem></selection></query></queries></report>
Back To Top