For every graph or report required a data connection must be made so the relevant data will be retrieved from the SQL database and exported to a excel worksheet.
- Create a data connection for every list of data
1. Creating a data connection
In Excel, go to the DATA tab and define a new connection to retrieve data from the SQL server.
Specify the name of the SQL server where your Legrand CRM data resides and the connection credentials, either Windows Authentication or a SQL user name and password.
Note: a SQL user name and password is not the same as your Windows login.
This will populate the worksheet with the raw data from your Legrand CRM database.
2. Edit the data connection to specify only relevant data
It is likely you will not need all the information exported, just specific columns – e.g. date created, value, territory, account manager, etc…
The next step is to edit the data connection to specify just the data required
Go to the DATA tab in Excel and click on Connections menu to bring up the list of connections that are defined in your Excel workbook.
Then select the connection you wish to edit and click on properties
In the Connection Properties screen there are two sections: Usage and Definition.
In the Usage section you can specify the Refresh properties.
If you are creating a dashboard that will be projected on a screen to provide a live update to team members then you probably would want to set an automatic refresh for every nn minutes, say every 5 minutes.
If the main purpose of the Excel workbook is as a report that is printed or projected when in a meeting then you would enable the Refresh Data when opening the file setting.
The second section in the Connection Properties screen is called Definition and that is where you can define which information you want to retrieve from the CRM database.
By default when you first create the connection the SQL Connection wizard creates a connection of type ‘Table’ and retrieves all the columns from that table.
In reality you will only want to retrieve just those data columns you wish to report on and you will also want to replace any lookup IDs with their lookup value.
To do this change the command type from ‘Table’ to ‘SQL’, and then enter the SQL Query command that needs to be executed to retrieve just the data you want.
Our recommendation is that you use SQL Management Studio to create and test the SQL Query that will retrieve the data you want.
That SQL query will contain JOINs with other tables in order to retrieve Company name, Account Manager name and any other lookup value.
For instance, a SQL Query to retrieve all active Opportunities created after 2012 could be
SELECT oppref AS RefNo, yestvalue AS Value, forecast, companyid, dcreated,
mgrid, stagelu, csummary
WHERE iactive=1 AND YEAR(dcreated)>2012
But the result for that query produces data that contains a lot of IDs, not the actual values.
To see the real data you need to JOIN with the tables that contain the values for those IDs; e.g. perform a Join with the companies table to retrieve the company name, etc…
The full SQL Query then is more likely to be something like this
SELECT oppref AS RefNo, ISNULL(yestvalue, 0) AS Value, forecast,
USR.firstname + ' ' + USR.lastname AS OpportunityManager,
CY.ccyname As company, CONVERT(date, OPP.dcreated) AS datecreated,
STR(LU.iseqid, 2) + ' - ' +LU.luvalue AS StageName, OPP.csummary AS summary
FROM opportunities AS OPP
LEFT JOIN companies AS CY ON CY.companyid=OPP.companyID
LEFT JOIN lookuplist AS LU ON LU.luid=OPP.stagelu
LEFT JOIN lfapUsers AS USR ON USR.userid=OPP.mgrid
WHERE iactive=1 AND YEAR(OPP.dcreated)>2012
With the result in SQL Management Studio looking like
Use SQL Management Studio to develop the SQL query that returns the data you want for your reporting.
Once you are sure that the SQL Query is correct then go back to Excel to modify the Connection Properties.
Change the command type to SQL and copy/paste your SQL Query into the command text box.
When you save those changes a message will pop up asking you to confirm that you want to overwrite the existing connection definition. Click Yes.
When the updated connection definition is saved Excel will again retrieve the data from your CRM database and this time instead of seeing IDs you will see the actual values.
3. Create a graph or pivot table
Now you have an Excel data table that can be used to as the data source for charts and other dashboard widgets.
Select the data and create a graph or pivot table that you position in the first worksheet of the Excel workbook.
Example: Activity Notes of the last 3 months, by type and team member