Business Units, Opportunities and SQL Recursion

When generating reports in MS CRM you typically want to generate reports using the filtered Views so that all of the security rules are enforced. It is really powerful to be able to see everything you are allowed to see automatically. However in this example we are going to use the non-filtered views.

This started off as a simple question on the developer forums. "How do I total the Opportunity Value by business Unit totaling child business units?"

What it turned into was a SQL recursive CTE exercise. This basic framework could be used to total any entity by Business Unit hierarchy.

For this example we first need a function to total the EstimatedValue for each Opportunity in a Business Unit.

You probably want to look at the following function very closely and see what you need to customize. You may want to total something besides an Opportunity, or if you do want to accumulate Opportunities, you may want to filter on SalesStageCode, State or StatusCode. For example you may want all Opportunities with a Status Code of 1 and a StateCode of 0, which is what I have in the example below.  You may also want to look at OpportunityClose Entities to see which Opportunities closed in a certain time period. Here is where you use your imagination to create the cool information that you or your customer wants.

-- Function: OpportunitySum
--
-- Gets the Sum of Opportunity EstimatedValues by BUid
--
--
alter Function OpportunitySum(@buId uniqueidentifier)
returns float
as
begin
declare @sum float

select @sum = Sum(Opportunity.EstimatedValue) 
from Opportunity  
where OwningBusinessUnit = @buId
and StateCode = 0
and StatusCode = 1
and SalesStageCode = 1

return isnull(@sum, Cast(0 as float))
end

Then it would be nice to know the number of child Business Units to help order the hierarchy. So we will create our first recursive CTE. This one simply counts child business units.

-- Recursive counts Business units below a parent business unit
--
--
alter Function ChildBuCount(@buId uniqueidentifier)
returns float
as
begin
declare @count int;
with ChildBuCount ( Id )
as
(
select base.BusinessUnitId 
from BusinessUnit base Where base.ParentBusinessUnitId = @buid
union all
select  bb.BusinessUnitId 
from BusinessUnit bb 
join ChildBuCount cc on cc.Id = bb.ParentBusinessUnitId
)
select @count = count(*) from ChildBuCount
return @count
end

Next is something a little trickier. This function calls the OpportunitySum() function we created earlier as part of the recursion, so I broke it down by section.

--  Recursive sums Opportunity EstimatedValue for all child Business Units
--
--
alter Function OpportunityValue(@buId uniqueidentifier)
returns float
as
begin
declare @estimate float;
with OpportunityValue ( Id, estimate )
as
(
-- Anchor member definition
select initbb.BusinessUnitId, dbo.OpportunitySum( initbb.BusinessUnitId )
from BusinessUnit as initbb Where initbb.ParentBusinessUnitId = @buid
 
union all
 
-- Recursive member definition
select bb.BusinessUnitId,  dbo.OpportunitySum( bb.BusinessUnitId ) 
from BusinessUnit bb 
join OpportunityValue ov on ov.Id = bb.ParentBusinessUnitId
)
 
-- Statement that executes the CTE
select @estimate = Sum(estimate) from OpportunityValue
return @estimate
end

What is critical to this CTE is that the OpportunitySum function has to be included in the anchor for this to work. Now lets call all of this in a way that generates something useful.

-- Top level select that selects all Business Units
--
--
select             
        isnull( parent.Name, 'None') as 'Parent Business Unit',    
        bb.Name as 'Business Unit',
        dbo.OpportunitySum(bb.BusinessUnitId) + 
        isnull(dbo.OpportunityValue(bb.BusinessUnitId),0) as 'Total Opportunity Value',
        dbo.OpportunitySum(bb.BusinessUnitId) as 'My Opportunity Value', 
        isnull(dbo.OpportunityValue(bb.BusinessUnitId),0) as 'My Children Opportunity Value',
        dbo.ChildBuCount( bb.BusinessUnitId ) as Children            
from BusinessUnit bb
left outer join BusinessUnit parent on parent.BusinessUnitId = bb.ParentBusinessUnitId
order by Children desc

Finally we get the results. This shows 4 business units that are 3 levels deep, but it should work for any number or depth of BU's.

	Parent Business Unit	Business Unit	Total Opportunity Value	My Opportunity Value	My Children Opportunity Value	Children
1	None	MicrosoftCRM	45550	0	45550	3
2	MicrosoftCRM	East Coast	25000	25000	0	0
3	MicrosoftCRM	West Coast	20550	5550	15000	1
4	West Coast	California	15000	15000	0	0