Understand which types of accounts generate the most revenue for your company by viewing sales orders based on customer type and industry. This data can help you decide where to target your marketing and sales efforts.
To analyze your orders, you must be able to view data from two related Microsoft Dynamics CRM 3.0 record types: accounts and orders. This article shows two methods to get data from those Microsoft CRM record types into Microsoft Office Excel. One method uses SQL Query and the other uses the Microsoft Query Wizard. Both methods require advanced Excel skills.
You start by exporting account data and conclude by editing the SQL query directly to add the order data.
To export accounts
1.
In Workplace, under Customers, click Accounts. In the View box, select Active Accounts.
2.
Click Export to Excel.
3.
Select Dynamic worksheet, and then click Edit Columns.
4.
Click Add Columns, select Category, Customer Size, and Industry, and then click OK.
5.
Select Primary Contact, click Remove and then when you see the confirmation message, click OK. Repeat this step to remove Main Phone and Address 1: City.
6.
Click OK, and then click Export.
7.
Click Open, and then click Enable automatic refresh.
To edit the SQL Query
1.
Right-click anywhere on the data in the Excel worksheet, and then click Edit Query. Click OK twice to close the two messages that pop up.
2.
Microsoft Query opens. Click the SQL button. The following SQL statement appears:
SELECT account.name, account.industrycodename,
account.customersizecodename,
account.accountcategorycodename
FROM FilteredAccount as account
WHERE (account.statecode = 0)
ORDERBY account.name asc
3.
Replace the SQL statement with the following code, which joins the FilteredAccount and FilteredSalesOrder filtered entity views, and displays two fields from the FilteredSalesOrder entity, totalamount and submitdate:
SELECT a.name,
a.accountcategorycodename,
a.industrycodename,
a.customersizecodename,
o.totalamount,
o.submitdate
FROM FilteredAccount AS a INNER JOIN
FilteredSalesOrder AS o
ON a.accountid = o.accountid
WHERE (a.statecode = 0)
ORDER BY a.name
To view the data in Excel
1.
On the File menu, click Return Data to Microsoft Office Excel, and then click OK.
2.
Enter the values 'Total Amount' and 'Submit Date' as column headers in the top row for the last two columns on the right.
3.
To save your new report, on the File menu in Excel, click Save As, and then specify a file name. In the Save as type list, select Microsoft Office Excel Workbook to save the file with an .xls file extension.
4.
To refresh the data from Microsoft CRM, on the Data menu, click Refresh Data.
5.
If you prefer to see your data in a PivotTable, on the Data menu, click PivotTable and PivotChart Report , and use the wizard to create your PivotTable or PivotChart.
You use the Microsoft Query Wizard, a component of Excel, to define the query that pulls data from accounts and orders. Because this approach does not start with a direct export from Microsoft CRM, the first step requires you to create a data source for the Microsoft CRM database.
To create the data source
1.
In an empty worksheet in Microsoft Office Excel, on the Data menu, point to Import External Data, and then click New Database Query.
2.
Click <New Data Source>, and then click OK.
3.
In the Create New Data Source dialog box, type a name for the data source, and then, from the driver list, select SQL Server.
4.
Click Connect.
5.
In the SQL Server Login dialog box, in the Server box, type the name of the Microsoft SQL Server where the Microsoft CRM database is stored, and then click Options.
6.
In the Database box, select Organization_MSCRM, and then click OK three times. The Query Wizard opens.
To select the filtered views to include
1.
In the Available tables and columns list box in the Query Wizard, expand the FilteredAccount view. This view shows all the data from account records.
2.
Select the name field. Because the name field is a mandatory field in Account, there is data for every record. You can choose the other fields in your spreadsheet in a later step.
3.
Click Next three times. In the Query Wizard – Finish dialog box, select View data or edit query in Microsoft Query, and then click Finish.
4.
Microsoft Query opens. On the Table menu, click Add Tables, and then click FilteredSalesOrder. This view shows all data from order records.
5.
Click Add, and then click Close.
To define the connection between FilteredAccount and FilteredSalesOrder
1.
Adjust the width of the two filtered views so that the field names are completely visible.
2.
In FilteredAccount, adjust the vertical scroll bar so that the accountid field is visible. In FilteredSalesOrder, adjust the vertical scroll bar so that the accountid field is visible.
3.
Drag accountid from FilteredAccount to accountid in FilteredSalesOrder. Microsoft Query draws a line between the two fields, indicating that it has done a database join on the two filtered views based on data in these fields.
4.
Drag the accountcategorycodename, customersizecodename, and industrycodename fields from FilteredAccount to the column section of the query.
5.
Drag the totalamount and submitdate fields from FilteredSalesOrder to the column section of the query.
6.
On the View menu, click Criteria.
7.
In the first criteria column, expand the width to about three inches. Then, in the first Criteria Field field, select the FilteredSalesOrder. submitdate field.
8.
In the first Value field, type > 01/01/2006 . This specifies orders placed in 2006.
9.
On the Records menu, click Query Now.
To view the data in Excel
1.
On the File menu, click Return Data to Microsoft Excel, and then click OK. Select where you want to put the data, and then click OK.
2.
To save your new report, on the File menu in Excel, click Save As, and specify a file name.
3.
To refresh the data from Microsoft CRM, on the Data menu, click Refresh Data.
4.
If you prefer to see your data in a PivotTable, on the Data menu, click PivotTable and PivotChart report, and use the wizard to create your PivotTable or PivotChart.
Now that you have created the Microsoft Excel file that includes orders and accounts, you can add it as a report in Microsoft CRM so that it is available to all Microsoft CRM users from the Accounts or Orders areas.
Because you used filtered views when you selected the data to display, other users see accounts and orders that they have permission to view. For all users, data refreshes each time the file opens.
To add the report for all users, you must have a security role with the Manage Reports privilege, such as the System Administrator or System Customizer security role.
To add the spreadsheet as a report
1.
In Workplace, under My Work, click Reports, and then click New.
2.
Click Browse, browse to the file you saved, and then click Open.
3.
In Related Record Types, select Accounts and Orders, and then click OK.
4.
In Display In, select Lists for related record types, and then click OK. Because you selected Accounts and Orders in the Related Record Types, this report appears in the Report list available from the Accounts and Orders areas.
5.
Click Save and Close.
6.
To see how the report looks in Microsoft CRM, on the Go To menu, point to Sales, click Accounts, and then click the Reports icon.
With the data in Excel, you can now analyze your orders and take necessary action. And, by learning how to combine data from two record types in Excel, you’ve opened up many reporting possibilities.
