Configuring Percentage Share Parameters 

Use

Use this screen to configure the parameters specific to the Percentage Share template. See Percentage Share (OLAP) for the description, business question, and workflow for using this template.

 

Procedure

...

       1.      Select the level that contains the members you want to see in the query’s result using the parameters in the table below:

Calculate Share Of Members Of Parameters

Parameter	Values	Action
Dimension	Lists all dimensions in the cube on which the query is based (see Selecting a Cube).	Select the dimension that contains the hierarchy to display in the Hierarchy field.
Hierarchy	Lists all hierarchies in the currently selected dimension.	Select the hierarchy that contains the level to display in the Level field.
Level	Lists all levels in the currently selected hierarchy.	Select the level that contains the members you want to see in the query’s result. The level name in the radio buttons below updates according to the value you select.

 

       2.      Select the group of members upon which to base the percentage share:

¡        Select As a percentage of the sum of all members of level <level name> to calculate individual members’ shares in proportion to the total for all level members.

¡        Select As a percentage of the sum of all filtered members of level <level name> to calculate individual members’ shares in proportion to only those members you select in the subsequent step of defining a filter.

You must define a filter for the second option to work properly. Defining filters can also be important to narrow the display for the first option.

 

       3.      From the Based on measure field, select the measure upon which to base the calculation. This field lists all measures in the cube on which the query is based.

       4.      Click Next.

 

Result

You proceed to the Define Filters screen.

 

Example

The table below illustrates the difference between basing the result on all level members and basing the result on filtered level members:

Percentage Share – All vs. Filtered

Product Family	Unit Sales	% Based on All	% Based on Filtered
Wine	200	20%	33%
Beer	200	20%	33%
Juice	200	20%	33%
Whiskey	200	20%
Soft drinks	200	20%

 

In this case, the Product Family level has five members. You have selected three members (Wine, Beer, and Juice) on the Define Filters screen (which follows this screen in the wizard). To illustrate the effect of this template’s different options:

·        As a percentage of the sum of all members of level Product Family:

The percentage share is based on the total for the whole level. In the table above, each member has 20% of the share of the total. If you wanted to define a query that tells you what percentage Canada and Mexico make up of your total sales, you would also use this option. In this case, you would filter your results to just select Canada and Mexico, and their percentages added together would not add up to 100% if there are other members contributing to the total that are simply not displayed.

·        As a percentage of the sum of all filtered members of level Product Family:

The percentage share is based on only the total of the filtered members. In the table above, each member takes 33% of this share. You might also use such a query to compare sales results among three sales representatives, excluding the rest of the group – essentially comparing themselves to each other within a subset of a level.

The table above is for illustration purposes. The actual result of this query will show three columns: one for the level, one for the measure, and one for the percentage share.