Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yavvie
Starting Member
46 Posts |
 Posted - 2009-07-27 : 09:01:53
|
| I have organizational structure for a company with history of a few months. Organizational structure can change - people can leave, join or change place in the structure from one department to another or go up/down the hierarchy (which is shown as Depth, going from 1 to 14 as the lowest possible position).I need to have on my report organizational structure for selected months, for each month I take into account the state from the last day of the month.For each person I have his production for the given month.What I need is for people who have moved in the structure to be displayed on both places.If I have person Anna and select months 06/2009 - 09/2009 and she moved like this:06/2009 Sales dept. level 5 under Oliver07/2009 Operations dept. level 4 under Claudia08/2009 IT dept. level 5 under John09/2009 Sales dept. level 5 under Oliverso I need to have her in the structure in 3 places (06 and 09 are the same places) and to have the according production in the right month/placefrom my dataset I receive the following:Element_ID Name Month_ID Depth Parent_ID ProductionI have 3 groups - first on Element_ID with recursive parent on Parent_ID, second on Depth with recursive on Element_ID and third on Element_ID (again for own production of people on higher positions - they have a total calculated from all the people below + own production)Using this I get each person in the structure only once and when the Parent_ID changes it makes his info into 2 or more rows in the same place.Any ideas how to do it better? I could also change / add fields to the data source if necessary. |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
|
|
yavvie
Starting Member
46 Posts |
Posted - 2009-07-27 : 09:24:00
|
| well when there are two forums that are almost the same and no reply on the first so i tried the second... :) |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
|
|
yavvie
Starting Member
46 Posts |
Posted - 2009-07-27 : 09:40:58
|
| Well i have a variable number of elements... I have the structure from the procedure via the Parent_ID, I just need to make the report display one person in more locations of the structure, they are returned from the dataset with different Parent_ID but the same Element_ID and I have groups based on Element_ID (which is of course used to compare with the Parent_ID if the subelements) and I dont see any way around this structure of the data... |
|
|
|
yavvie
Starting Member
46 Posts |
Posted - 2009-08-03 : 02:32:49
|
| OK if anyone needs this, here is the solution:I created unique ID for each combination of worker-parent as Unique_Element_ID, and then you have to look up who was the parent of your parent to create Unique_Parent_ID. You use those two IDs to match workers to their parents valid at the end of the month or as frequent as you need. On the report groups are based on Unique_Element_ID with recursive parent on Unique_Parent_ID. That way you can have your organization structure as deep as you want without having to adjust the report at all.Just make sure your Unique IDs are created correctly :) |
|
|
|
|
|
|
Create organizational structure for more months