Autogenerated Growth Over Previous Period

From OpenI Wiki

Table of contents

1 Overview

1 Use cases
2 Implementation

[edit]

Overview

Growth over the previous period is most commonly used measure in the reports irrespective of domains in which analysis is made. Creating a formula Growth over previous period for a measure at the front end is cumbersome and requires MDX knowledge.

This feature creates Growth over previous period formula for all measure dimension members in the view and also adds them in the view by click of a button.

Growth Over Previous Period works in the following way

If the Time dimension member in the view is Year 2006 (Year Level) then the growth over Year 2005( Last Year) is shown.

If the Time dimmension member in the view is Quarter 2 - 2006 ( Quarter Level) then the growth over Quarter 1 - 2006 is shown

Computed members are added in to Measure dimension as with member expression in MDX query

[edit]

Use cases

1.User clicks "Percent Growth" button toolbar

2.System generates with member clause called Growth Over Previous Period

3.Measure is automatically added to select clause

4.Measure is available in olap navigator

[edit]

Implementation

This feature is implemented as an Extension similar to swap axis, drill through etc.

The extension works only when the two conditions are satisfied 1. Time type dimension in the cube and

2. Measure dimension either in the rows or columns.

For all the members of the measure dimension that are in the analysis generates the formula expression as

[measures].[Store Cost Growth] as '(Iif(( ([Measures].[Store Cost],[Time].CurrentMember.PrevMember) = 0 or isempty( ([Measures].[Store Cost],[Time].CurrentMember.PrevMember) ) , 1,( [Measures].[Store Cost] - ([Measures].[Store Cost],[Time].CurrentMember.PrevMember) )/([Measures].[Store Cost],[Time].CurrentMember.PrevMember) ))' , format = '#.00%'

Adds these expressions to the existing MDX query to get the computed formula from the Olap server

PS: Not found a correct way, currently doing this by using string manipulation. Adds with member expression when the MDX query is not starting with with member or adds at appropriate location in the query using the string manipulation. Suggestions are welcome

Adds to the newly created members to the view by manipulating quax

[edit]

Version control

cvs branch: Autogenerated_Growth_Over_Previous_Period

[edit]

OpenI

Add:

Images in the openi/images/toolbar/compugrowth-down.png

Images in the openi/images/toolbar/compugrowth-up.png

Add button in the tool bar – analysis.jsp

Add new message in org/openi/toolbar/resoures.properties

Add Extension in org/openi/xmla/config.xml

[edit]

Jpivot changes

Most of the changes are gone into JPIVOT. Changes are done for both XMLA and Mondrain providers so that the changes can be adopted in JPIVOT.

Changes are related to adding CompuGrowth Extension – These are new files and config.xml file

Showing Delete Icon in navigator

com\tonbeller\jpivot\navigator\member\MemberNavigator.java

DefaultDeleteModel is implemented for display of the delete icon and deletion of member.

- IsDeletable:

Members that can be deleted are identified by their presence of member in pQuery formula list.

- Delete:

Delete check whether member can be deleted for reconfirmation and then deletes form view and also from formula list of pQuery. MDX query is generated from pQuery object on OK of the Navigator and hence does not consider deleted formula members.

Manipulating mdx for adding with member expression

com/tonbeller/jpivot/olap/query/QueryAdapter.java

Gets the existing MDX query and manipulates the MDX query to add the new formula members.

For the given Measure and Time Dimension formula is formed as given blelow [measures].[Store Cost Growth] as '(Iif(( ([Measures].[Store Cost],[Time].CurrentMember.PrevMember) = 0 or isempty( ([Measures].[Store Cost],[Time].CurrentMember.PrevMember) ) , 1,( [Measures].[Store Cost] - ([Measures].[Store Cost],[Time].CurrentMember.PrevMember) )/([Measures].[Store Cost],[Time].CurrentMember.PrevMember) ))' , format = '#.00%' Adds to MDX query as with member expression.