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>
Advertisements

2 Responses to Running-Product

  1. Hagit Tzabari says:

    Genius!!

  2. Matheus says:

    Mate you saved me a headache. Thanks a million.

    Now I know I shouldn’t have skipped that maths class.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: