Hierarchy Drill Down Issue

[#1]

Hey,

I have a table which relates employee to it's reporting manager and so on.

The hierarchy is as follows :

CM>ZM>RM>SM>UM.

I want to use pivot table to display data of sales according to this hierarchy.

PFA attached the reporting structure table.

Thanks for any help in advance.

[#2 · Accepted solution]

Hi,

Please see the attached.

I have used this loading script.

DATA:

HIERARCHY (Emp_code, RM_code, Emp_name, Manager, Emp_name, Path, '/', Depth)

LOAD Emp_code,

     Emp_name,

     Designation,

     RM_code

FROM

SalesHierarchy.xls

(biff, embedded labels, table is [Sheet1$]);

Regards,

Janzen

[#3]

Hi Janzen Jino B,

Thanks for the reply,

Did you make any changes to the SalesHierarchy.xls file??

how did you prepare the excel sheet?? Is it hardcoded or dynamically generated???

In case i add a new employee or a new hierarchial level to my application, do I need to reaarange everything or it will be done dynamically??

if you have changed anything please attach the excel file too.

Thank you

Regards,

Utsav

[#4]

Hi,

I didn't make any changes to the file. You can add employees as many as you want. Using the hierarchy function, Employee is now related to RM_code.

Regards,

Janzen

[#5]

Hi,

your answer solved my query,

Now i want to know how do i display the Designation of an emplyee beside his/her name in the same pivot table.

Regards,

Utsav

[#6]

Hi,

I tried doing this using 2 separate hierarchies so that designation will also be arranged based on employee hierarchy.

DATA:

HIERARCHY (Emp_codeE, RM_codeE, Emp_nameE, Manager, Emp_nameE, PathE, '/', DepthE)

LOAD Emp_code as Emp_codeE,

     Emp_name as Emp_nameE,

     Designation as DesignationE,

     RM_code as RM_codeE

FROM

SalesHierarchy.xls

(biff, embedded labels, table is [Sheet1$]);

DATA1:

HIERARCHY (Emp_codeD, RM_codeD, DesignationD, Type, DesignationD, PathD, '/', DepthD)

LOAD Emp_code as Emp_codeD,

     Emp_name as Emp_nameD,

     Designation as DesignationD,

     RM_code as RM_codeD

FROM

SalesHierarchy.xls

(biff, embedded labels, table is [Sheet1$]);

After that, you can show employee name and designation as dimensions.

Regards,

Janzen