using a range of dates for Key Date

[Former Member]

In a HR Bi data warehouse, we have a position-to-position hierarchy, where each of the nodes are time dependent. So, it shows for each node, valid from and valid to dates, and all the employees who are reporting to that position. This hierarchy is built on the infoobject 0HRPOSITION, which is maintained in R/3 and extracted to BI.

Let us take an example: Position 1000 is valid from 1-1-2006 to 6-30-2006 Employees reporting to this position are A,B,C,D

Position 1000 is valid from 7-1-2006 to 12-31-9999 Employees reporting to this position are A,E,F,G

When a user chooses the position 1000, and date range 1-1-2006 to 12-31-2006, it show the complete list of employees as

A,B,C,D,E,F,G.

Because the Keydate can only be a single value, and it is automatically taking today's date, and pulling the nodes based on that.

I have created a hierarchy node variable on the 0HRPOSITION infoObject, and entered the value 1000, with no value for the keydate.

The system is simply showing employees, A,E,F and G. That is my problem

My requirement is this: I like to be able to give a date range, (for the hierarchy) say from 1-1-2006 to 12/31/2006 and get the complete list of Employees, which is A,B,C,D,E,F,G.

Is this possible? Can I change the way this hierarchy is defined so that I can pull the possible values for a range?

[Former Member · Accepted solution]

Is this possible?

No.

Can I change the way this hierarchy is defined so that I can pull the possible values for a range?

I wouldn't think so.

[Former Member · Accepted solution]

Ajay,

I understand.

I am sure this problem must have been faced by many customers. Can you please comment on these two workarounds I am thinking about?

1. I was thinking that I could take datefrom and dateto values for every node, and make them as attributes of the infoobject 0HRPOSITION. These values are coming in from R/3, therefore available in the datasource. What if I store these values as attributes of 0HRPOSITION. If I make these two fields navigational attributes of 0HRPOSITION infoobject, can I then use them in a filter on the query?

2. Can I create a virtual characterestic on cube, which contains the field 0HRPOSITION? I have a Headcount cube, that contains 0HRPOSITION as a characterestic in cube, which also have time characteristic like 0CALDAY, 0CALMONTH etc. Is there way I can compute and store these values in a virtual characteristic?

Hope I explained the idea. Let me know if you need further clarification.

Thanks a lot for your help.

[Former Member · Accepted solution]

1 wouldn't work as you will have only one set of from/to values for a position (in master data). To have something like this, you will have to compound (not attribute) from/to dates - bad idea.

2 - same explanation as above. One HR position will get only one from/to values for any virtual char defined for these two fields.

It is not very clear what are you trying to achieve here. Do you want 'Employee' in your report? Is employee just in the hierarchy (and not in the cube?).

[Former Member · Accepted solution]

Thank you Ajay.

After some thinking, I have realized that these options will not work.

We have a position-to-position hierarchy that shows who reports to who in the organization. This hierarchy is built on the Infoobject 0HRPOSITION. Each node in this hierarchy has is time-dependent. Note that, the entire hierarchy is not timedependent. Only the individual position nodes are time-dependent.

This 0HRPOSITION infoobject exists in the Heacount cube as one of the characteristics. Here is my requirement.

1. I want to show in a report, all the employees (directly or indirectly) reporting to a manager for a period of say, 1 year?

I know that I can specify a key date for the hierarchy 0HRPOSITION, then the report will show all the employees (direct and indirect) reporting to a position say 6/30/2008. I don't want this for a specific date, I want to get ALL the employees (direct and indirect) reporting to a position in a range of dates( say 1 year)

Does that make sense? How do we achieve this goal?