Count of Positions using Rules

[krishna0123]

Hi Guys,

i need help in order to achieve my requirement.

here is my requirement ,i have a cube with combination of 2 dimensions(screen shot for reference)

Dim1- Hierarchy (A to E)
Dim2- Postion and Count (elements)

E1-E8 - emp's
C1-,C2(levels)- Positions(eg for C1,C2)

C1-123 032 ADMIN
C3-056 010 Team lead

* Data of E1,E2, will match with C3 node
* Data of E5 will match with C1 node
* Same position can have for multiple employees (eg: E1,E2 employees have same position)
* Data(position) is picklist ,user will select the position

coming to my query ,i need to get count of positions with respect to node.
eg: E1,E2 will match with C2 node ,so the count should be 2 and data for E5 will match C1 node so the count should be 1
when user select/change the position for a particular employee the count of positions should reflect immediately

Final thing ,i need it through rules only not by TI's because data (position selection) is user selection

Thanks in Advance

[rmackenzie]

Sorry to say this but your presentation of the requirement doesn't make sense. Firstly:

i need help in order to achieve my requirement.

I don't see this as a requirement, but more as homework. If you had a real requirement then you should present that Employees roll up-to Cost Centres ro Managers, for example. And that your ultimate aim is to do FTE reporting. Or that you are trying to build an interface to allow a user to forecast new employees/ positions in their business unit etc etc.

Also, you say that:

* Data of E1,E2, will match with C3 node
* Data of E5 will match with C1 node

But two sentences later say that:

eg: E1,E2 will match with C2 node

So, your description of the homework seems internally inconsistent.

Additionally it is not clear what you are trying to achieve by 'matching' n-levels with consolidations that they do not roll-up to. What are the diagonal dotted lines supposed to indicate?

[krishna0123]

Hi rmackenzie,
Thanks for responding for my query.
please refer this new screen shot,this might gives better information

i have 2 cubes cube 1 and cube 2(as shown in screen shot)

cube 1

dim 1 -CEO to employee hierarchy
dim 2- current position and new position are 2 elements

**current position(element 1) is mapped from database
**new position(element 2) is user selective picklist

cube 2:

dim1-with all positions(fetching from database)
dim2- count


Assume you have 2 vacancies "403380 06 maintenance" and u r transferring 2 employees from other organisation to fill these positions

now i am expecting count of new position with respective to organisations in cube 2

eg:
As shown in screen shot ,under org 1(202020) emp 1 was promoted to new position "403380 06 maintenance" and org 3 (601190) emp 8 was promoted to new position "403380 06 maintenance"


so the count of 403380 06 maintenance =2
similarly count of 108410 03 programmer =2
similarly count of 202030 02 Eng c =1

might be we can get solution using TI's but i am expecting solution using rules

Thanks in Advance

[tomok]

What you want to do is not feasible in your given dimensionality. You would have to have a massive IF condition, comparing every element in the Position dimension against every element in the Employee dimension, accumulating a 1 for every match. You would quickly exceed the limit on the text length allowed in rule code. The answer is to add the Employee dimension (what you are calling dim 1 in cube 1) to Cube 2 and then in the rules for Cube 2, match the selected position from Cube 1 against the element name of the Position dimension and place a 1 in the count when they match. Something like this:

Code: Select all

['Count'] = N:IF(!Position @= DB('Cube1', !Employee, 'New Position'), 1, 0);

To see the total count for each position you would look at the intersection of each Position, the element "Total Employees" and the Count measure in Cube 2.

FYI, if you did the count accumulation via a TI process you wouldn't need the extra dimension in Cube 2. Food for thought.