Tag: functions

OLAP

Quickie: Generate function (first N months of years)

| cognospaul

Problem: How do you return the first N months of each year in the time hierarchy? It must be dynamic (no static sets, because who wants to go back every year and fix it?), and the user wants to be able to select the number of months that are displayed.

Solution:

generate
(
   [great_outdoors_company].[Years].[Years].[Year]
   , head
     (
        descendants
        (
           currentMember([great_outdoors_company].[Years].[Years])
           , [great_outdoors_company].[Years].[Years].[Month])
     , #prompt('n','integer')#
     )
)

The generate function will evaluate the head(descendants(currentMember)) expression for each member in the years level.

Uncategorized

Running-Product

| cognospaul

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

Running-total on a cube

| cognospaul

The concept of a running-total is relational concept.  Since a relational table can be visualized in only two dimensions it is very easy to understand and visualize running and moving aggregates.

A multidimensional data source, on the other hand, is more complex.  You might want the running aggregate on one dimension but not another. When attempting to use the running aggregate functions on a cube you cannot guarantee consistent results, drilldowns might behave unexpectedly, and the entire processing time is increased as the resultset must be processed locally.

Fortunately running and moving aggregates can be handled with purely dimensional functions.

Consider the following request. A line graph that shows the running total of sales for the current and previous years.  The ordinal axis shows all months of the current year.

To start create a new line graph report.  Create a Query Calculation in the Category area with the following expression (replacing with correct values from your own cube):

descendants ([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231],[Cube].[Date].[Date].[Month] )

This will return the Month descendants of 2010  (see a previous post on a better way of finding the current year).

Create a Query Expression in the series with the following expression:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],currentMember([Cube].[Date].[Date])))

Run the report. If all goes well you should see the line growing as expected.

It is important to understand the mechanism. The expression in the series is processed for each item in the ordinal axis.  The currentMember function will take the member that’s being evaluated. So far January 1 the expression would appear as:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20100101-20100131]))

When it gets to the October category it will appear as:

total([Cube].[Measures].[Sales] within set periodsToDate([Cube].[Date].[Date].[Year],[Cube].[Date].[Date].[Month]->:[PC].[@MEMBER].[20101001-20101031]))

The periodsToDate function will take the member and create a set of members starting with the first member in the specified level to the member being evaluated.  October/2010 would return the set: January/2010, February/2010, March/2010, April/2010, May/2010, June/2010, July/2010, August/2010, September/2010, October/2010.

The next demand is to see the running total for the previous year.  In a relational database you might do this with an outer join. Here it’s just a logical extension of the same expression.

total([Cube].[Measures].[Actual] within set periodsToDate([Cube].[Date].[Date].[Year],parallelPeriod([Cube].[Date].[Date].[Year],1,currentMember([Cube].[Date].[Date]))))

This is essentially the same expression, with the parallelPeriod function wrapping the currentMember.

The same concept can be used for moving totals. Instead of the periodsToDate function, you would use lastPeriods().

total([Cube].[Measures].[Sales] within set lastPeriods (5, currentMember([Cube].[Date].[Date])))

This will always create a total for a set of 5 members ending with the member being evaluated.

Here’s a small challenge. Knowing this, how do you do a running-total in reverse?