Hint: Mapping Data Tables for CRM Imports

When importing data into a CRM database, there is an easy way to create current mapping tables.

First take the url for your CRM server and append /sdk/list.aspx to it.
example.  http://crmserver1:5555/sdk/list.aspx

That will give you a current listing of all of your entities with customizations.
Then click a particular appropriate entity to get a screen like the one below.

That's very informative, but we want to re-purpose this for use in a data import mapping table.

1. Right click -> View Source
2. Ctrl A  ( select all of the raw HTML )
3. Ctrl C  ( copy it to your clipboard )
4. Now open up excel. Excel is really accepting of a lot of data formats these days and most customers like to work in Excel Spreadsheets
5. Paste that RAW HTML into a spreadsheet
6. Do a little formatting and remove what you don't need.

Now you have CRM Entity import tables reflecting any customizations that you have made.

Hint: Developing for Multiple MS CRM 4.0 Servers

Ideally CRM custom application development is not being done on production CRM systems. It would be helpful if our custom applications would automatically switch between the appropriate web services of development, staging and production CRM environments.

The method below creates the appropriate CrmServiceUrl using the ServerUrl value in the registry of the CRM server running the custom application.

using Microsoft.Win32;  // For registry access
using crmWebService;    // Your crm webservice reference

// <snip>

    // Create a web service for your organization.     
    CrmService service = InitCRMWebService("MicrosoftCRM");    
        
    //Use service

// <snip>

    
    /// <summary>
    /// Get the crmservice for the organization specified
    /// </summary>
    /// <param name="organizationName"></param>
    /// <returns></returns>
    private CrmService InitCRMWebService(string organizationName)
    {
        // Create the CrmServiceUrl from the registry
        RegistryKey key = Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\MSCRM", false);
        string CrmServiceUrl = (string)key.GetValue("ServerUrl") + @"/2007/crmservice.asmx";
        
        service = new CrmService();
        service.Credentials = System.Net.CredentialCache.DefaultCredentials;
        service.Url = CrmServiceUrl;
        service.PreAuthenticate = false;
        
        CrmAuthenticationToken token = new CrmAuthenticationToken();
        token.AuthenticationType = 0;
        token.OrganizationName = organizationName;

        service.CrmAuthenticationTokenValue = token;
        
        return service;
    }

For debugging:

1. Make sure that your local Active Directory account is a CRM user on all of environments that you will debug against.
2. Export a copy of the MSCRM registry from your CRM server and apply it to your development computer's registry.
3. Modify the ServerUrl value data to switch between the servers that you are debugging against.

 

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