• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Department Paths in Reporting
#1
Hello everyone! 

Has anyone had any success breaking out department paths into separate fields in a report? Currently, we have a very simple department structure: Company/Division/Business Unit/Location City. I keep getting requests to break out the separate entities from the path, however as I see it, I can only report on the full path, or the last level.

Thank you in advance.
mrajotte, proud to be a member of EV CONNECT FORUM since Jan 2016.

#2
Not sure if this is the answer to your question but have your tried grouping (breakpoiint) by the last level? This will build out each level in your report and allow you to navigate through the different paths.
Andrew E. Reynolds
Sr. Manager, Professional Services & Support

#3
Andrew, thank you for your reply. This only works on displaying results on screen and does not translate into the exported (CSV) report file.
mrajotte, proud to be a member of EV CONNECT FORUM since Jan 2016.

#4
I am on prem. Don't know if you are allowed to create views on a saas version.
I would do this.

Create a function that returns the label of a department of a given level that is parent to the current departement.


Code:
CREATE FUNCTION [50004].[E_DeptByLevel]
(
@Level AS int,
@DeptID AS int
)
RETURNS nvarchar (255)
AS
BEGIN
DECLARE @res nvarchar(255);
DECLARE @l_left int;
DECLARE @l_right int;
SELECT @l_left = AM_DEPARTMENT.LFT, @l_right = RGT
FROM AM_DEPARTMENT WHERE AM_DEPARTMENT.DEPARTMENT_ID = @DeptID;
SELECT @res = AM_DEPARTMENT.DEPARTMENT_FR FROM AM_DEPARTMENT
WHERE [LEVEL] = @Level AND LFT <= @l_left ANd RGT >= @l_right;
RETURN @res;
END;
GO



Then you can create a view that returns the different levels.



Code:
CREATE VIEW MyVieW AS
SELECT DEPARTMENT_ID, DEPARTMENT_FR,
[50004].E_DeptByLevel(2, DEPARTMENT_ID) Level1,
[50004].E_DeptByLevel(3, DEPARTMENT_ID) Level2,
[50004].E_DeptByLevel(4, DEPARTMENT_ID) Level3,
[50004].E_DeptByLevel(5, DEPARTMENT_ID) Level4
FROM [50004].AM_DEPARTMENT



Use the new views in your current report
Amine.






Users browsing this thread: 1 Guest(s)