Dynamics Community Forum Thread Details

Please I have the following scenario:

I need to construct a report using 4 tables:

1.department :A table in which i have a hierarchy of departments

2.Opportunity(Projects):contains the opportunities ,those opportunities are owned by users and attached to departments.

3.User :contains the users and the departments they are attached to.(some users can be attached to multiple departments.)

4.appointements:appointments that are owned by users.

in my report i need to extract the sum of appointements based in the user owning the appointement.(works fine)
i need to get the sum of revenues (columns in the opportunity table) based in the owner(user) when I have users attached to departments.
& also i need to get the revenue based in the column(my_dept_id),because in some case i have opportunities that are not attached to user.

I have 3 parameters :date,paramsecteur(department),paramBU(department)

that's the wanted result:

the problem i have is that my total revenue are not aware of the paramBU parameter.

this is my query :

DECLARE @FirstDayOfCurrentYear nvarchar(32)
 DECLARE @FirstDayOfNextYear nvarchar(32)

 DECLARE @num_semaine int

 SET @FirstDayOfCurrentYear = CONVERT(nvarchar, DATEFROMPARTS(YEAR(@DateDebut), 1, 1), 112); 
 SET @FirstDayOfNextYear = CONVERT(nvarchar, DATEFROMPARTS(YEAR(@DateDebut) + 1, 1, 1), 112); 

SET @num_semaine = DATEPART(ISO_WEEK, @DateDebut)

;WITH ETC_AF AS
(
SELECT
    fa.ownerid,
    SUM(fa.RPs) AS RPs,
    SUM(fa.RP_cumul) AS RP_cumul,
FROM
(
    SELECT
        ownerid,            
        CASE 
            WHEN statecode = 1 
                AND DATEPART(iso_week, actualend) = @num_semaine
                AND type_rdv = 100000000 
            THEN 1
            ELSE 0
        END AS RPs,
        CASE
            WHEN statecode = 1 
                AND DATEPART(iso_week, createdon) <= @num_semaine
                AND type_rdv = 100000000 
            THEN 1
            ELSE 0
        END AS RP_cumul
    FROM Appointement f
    WHERE f.createdon >= @FirstDayOfCurrentYear AND f.createdon < @FirstDayOfNextYear
        AND statecode IN (1, 3)
        AND type_rdv IN (100000000)
) fa
GROUP BY ownerid
),
FO_CTE AS
(
  SELECT
    fo.ownerid,
    SUM(fo.revenue) AS revenue ,
    SUM(fo.revenuecmd) AS revenuecmd 

FROM
(
SELECT
        ownerid,
    CASE 
           WHEN f.dateBegin >= @FirstDayOfCurrentYear AND f.dateBegin < @FirstDayOfNextYear
                AND f.prob = 100 
            then revenue
            ELSE 0
            END AS revenue,
            CASE 
           WHEN f.dateBegin >= @FirstDayOfCurrentYear AND f.dateBegin < @FirstDayOfNextYear
                AND f.prob = 100 
                AND agreement=284330001 
            then revenue
            ELSE 0
            END AS revenuecmd
    FROM Opportunity f
) fo
GROUP BY ownerid
),
FO_CTEE AS
(
SELECT
    fo.my_dept_Id,
    SUM(fo.revenue1) AS revenue1 ,
    SUM(fo.revenuecmd1) AS revenuecmd1

FROM
(
    SELECT my_dept_Id
        ,
    CASE 
           WHEN f.dateBegin >= @FirstDayOfCurrentYear AND f.dateBegin < @FirstDayOfNextYear
                 AND f.prob = 100 
            then revenue
            ELSE 0
            END AS revenue1,
            CASE 
           WHEN f.dateBegin >= @FirstDayOfCurrentYear AND f.dateBegin < @FirstDayOfNextYear
                 AND f.prob = 100 
                 AND agreement=284330001 
            then revenue
            ELSE 0
            END AS revenuecmd1
    FROM Opportunity f
) fo
GROUP BY my_dept_Id
),
 BHierarchy_CTE AS
(

SELECT 
    B.deptId AS deptId,
    B.Name AS deptIdName,
    B.ParentdeptId AS ParentdeptId,
    B.ParentdeptIdName AS ParentdeptIdName,
    B.sales_area AS Sales_Area,
    B.sales_areaname AS Sales_AreaName
FROM Departement AS B
WHERE B.IsDisabled = 0 AND b.sales_areaname IN (@parmSecteur)
),
RDV_CTE AS
(
SELECT
    fsu.fullname,
    fsu.isdisabled,
    fsu.YomiFullName,
    fsu.systemuserid,
    fsu.deptId,
    fsu.positionidname AS poste,
    bu.deptIdName,
    fa.RPs,
    fa.RP_cumul,
    fo.revenue,
    fo.revenuecmd,
    FROM User FSU
INNER JOIN BHierarchy_CTE bu ON bu.deptId = fsu.deptId
LEFT OUTER JOIN ETC_AF fa ON fa.ownerid = fsu.systemuserid
LEFT OUTER JOIN FO_CTE fo ON fo.ownerid = fsu.systemuserid 
WHERE fsu.isdisabled in( 0,1)
    AND bu.deptIdName IN (@parmBU) 
 ),
 DeptRDVCUML AS
(
SELECT
    B.deptId,
    B.ParentdeptId,
    SUM(rdv.RPs) AS SumRPs,
    SUM(rdv.RP_cumul) AS SumRP_cumul
    SUM(rdv.revenue) AS Sumrevenue ,
    SUM(rdv.revenuecmd) AS Sumrevenuecmd ,
    SUM(opp.revenue1) AS Sumrevenue1 ,
    SUM(opp.revenuecmd1) AS Sumrevenuecmd1 ,
FROM Departement AS B
LEFT JOIN RDV_CTE AS rdv ON rdv.deptId = B.deptId
LEFT JOIN FO_CTEE AS opp ON opp.my_dept_Id = B.deptId
GROUP BY B.deptId, B.ParentdeptId
),
DeptRDVCUMLR AS
(
SELECT
    init.deptId,
    init.ParentdeptId,
    init.SumRPs,
    init.SumRP_cumul,
    init.Sumrevenue,
    init.Sumrevenuecmd,
    init.Sumrevenue1,
    init.Sumrevenuecmd1,
    FROM DeptRDVCUML init
UNION ALL
SELECT
    parent.deptId,
    parent.ParentdeptId,
    child.SumRPs,
    child.SumRP_cumul,
    child.Sumrevenue,
    child.Sumrevenuecmd
    child.Sumrevenue1,
    child.Sumrevenuecmd1
FROM DeptRDVCUMLR child
INNER JOIN DeptRDVCUML parent ON child.ParentdeptId = parent.deptId
)

SELECT
B.deptId,
B.deptIdName,
B.ParentdeptId,
B.ParentdeptIdName,
COALESCE(totalRPs, 0) AS totalRPs,
COALESCE(totalRP_cumul, 0) AS totalRP_cumul,
COALESCE(totalrevenue, 0) AS totalrevenue,
COALESCE(totalrevenuecmd, 0) AS totalrevenuecmd
COALESCE(totalrevenue1, 0) AS totalrevenue1,
COALESCE(totalrevenuecmd1, 0) AS totalrevenuecmd1,
NULL AS OwnerId,
NULL AS OwnerName,
NULL AS OwnerPoste,
NULL As isdisabled
FROM
(
    SELECT
        deptId,
        SUM(SumRPs) AS totalRPs,
        SUM(SumRP_cumul) AS totalRP_cumul,
        SUM(Sumrevenue) AS totalrevenue,
        SUM(Sumrevenuecmd) AS totalrevenuecmd,
        SUM(Sumrevenue1) AS totalrevenue1,
        SUM(Sumrevenuecmd1) AS totalrevenuecmd1,
    FROM DeptRDVCUMLR
    GROUP BY deptId
) C
INNER JOIN BHierarchy_CTE B ON B.deptId = C.deptId
UNION ALL
SELECT
deptId,
NULL,
NULL,
NULL,
COALESCE(RPs, 0) AS RPs,
COALESCE(RP_cumul, 0) AS RP_cumul,
COALESCE(revenue, 0) AS revenue,
COALESCE(revenuecmd, 0) AS revenuecmd,
NULL,
NULL,
SystemUserId,
FullName,
poste,isdisabled
FROM RDV_CTE

Thank you

SSRS Complex query

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Community AMA December 12th

Leaderboard

Featured topics

Product updates