No display of closed organisational units in the query designer

[Former Member]

Dear BW gurus

I have a problem regarding closed organizational units not displaying in the query result of the Query Designer (7.0).

I have a query displaying FTE for each month in the columns, and organization units and employees in the rows. The problem is that the organisational units which has been closed down during the year is not displayed properly in the query result. E.g. we have orgunit B and C located below orgunit A, and orgunit D below C. Orgunit D is closed per 31.03.2011 and the employees moved to orgunit B. When I execute the query I would like to see orgunit D (and its employees) displayed below orgunit C for the first 3 months of the year, but orgunit D is only displayed below node Unassigned (and the FTE's in orgunit D are therefore not counted in).

A

I

- > B

I

- > C

I

- > D

(sorry, its not displaying very nicely; D should be located below node C and not next to C)

I have checked the master data for the employees, and these are correct (the orgunit for employee is changing for the correct month). I have checked master data for orgunit, and these are correct (the units are restricted to the correct time period). I have checked the content of the infoprovider, and this is also correct.

The problem seems to be the way the organizational hierarchy is displayed in the query result. The hierarchy is displayed as it is on execution date for the report (where orgunit D is closed down). Consequently, the closed orgunits is only located below an unassigned node.

Settings in the query:

0ORGUNIT:

Tab Hierarchy - Activate hierachy display = X

Tab Advanced - Access type for result values = Master data

We're on BW 7.0 SP05

Please advice how to make the org hierarchy display the closed org units at the position they actually had in the time period evaluated. Points will be awarded for valuable help.

Kind regards Nete

[former_member182470]

Hi,

After re-assigning Org Units in BW master data mappings, you need to run Attr Change run to reflect in the reports.

If you want to see the org units as per different time lines, you need to run your report by speecifying the Key Date in the Query designer. Because Key Date works on Time Dependant Master data.

Regards,

Suman

[Former Member]

Dear Adam and Suman

0ORGUNIT is set as time-dependent, attribute change run is executed at the end of our proces chains for both 0EMPLOYEE and 0ORGUNIT daily.

Regarding the query key date: If I change this date, I think it will show the hierarchy as it looked on the selected date. Correct? Actually i don't want that either, because the hierarchy on that date might not reflect all ORGUNITs during all of the months (eg. if i select key date 31.03.2011 the orgunits created in april will not show, and the orgunit closed in february will not show either). Is this correct understood?

KR Nete

[Former Member]

Yes Nete, you got it right. You can only see the hierarchy at one point in time -> months in columns will not work, each node will be shown / assigned only once.

One solution can be, to put only ORGUNIT name on rows and then the monthly FTEs will be shown correctly. It will work in BEx, but you will miss the hierarchy.

A solution that will give you expected results requires a change in data model: add two InfoObjects to the InfoCube that will explicitly store the orgunits and months (+data) for each row:

A, C, 01.2011, ...

B, D, 01.2011, ...

A, D, 02.2011, ...

B, C, 02.2011, ...

Then build the hierarchy manually in BEX Query, adding the first two on rows and use months along with other key figures on the columns.

Regards,

Adam

[Former Member]

Hi Nete,

As I assume 0ORGUNIT InfoObject is time dependent? From the description above it seems yes. When launching the report it will show the latest state of the hierarchy (as it is on the day of execution of the report). Try to set a key date on the Query Properties tab to set the Key Date which will be used for determining values of time dependent attributes.

This should help. If this is not the case, please update. Also what is worth mentioning Key Date is a Key Date and you can not use any other threshold (like week or month).. This needs to modeled at the InfoCube level..

Regards,

Adam