【Excel Addin】SalesForce Report on Excel Worksheet

This time, I will try setting up the connector (Datamodel) of SalesForce, pulling data from my SalesForce demo environment, store it to a local database and creating Excel report.

If you do not have a Sharperlight license, you will find that you can easily extract data to an Excel worksheet, etc., although there are some restrictions.

## Obtaining the datamodel ##
Go to the Sharperlight home page.
https://www.sharperlight.com/
Click `Resources` to display the pull-down menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9b2a2204-5b24-a647-bdcd-92047118c18b.png)
Select the **Datamodels** menu item.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5b081214-a1a1-df75-b4af-5e750e522a04.png)
Select **SLSF - SalesForce** from the tree view of the Datamodels page. A list of various items will appear on the right.
Find the **SLSF** datamodel in the list and click the `Download` button. The download of the datamodel will start immediately.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f76ebc7c-34ae-fa86-137a-e6b92b801987.png)
## Installing the datamodel ##
From the Sharperlight application menu, select `Datamodel Installer`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/10db6d99-cefc-6ea6-09f9-0297c1a9ee74.png)
Click the `Add` button and select the datamodel you downloaded earlier.
Click the `Force Recompile` button to compile the data model.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5a6615c3-0a0e-653c-d344-47d69e18b507.png)
## Preparing a database ##
Start Microsoft SQL Server Management Studio (SSMS) and connect to your database server.
Create a new database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8ef9f4b0-269b-991a-62a9-491bc77bb77b.png)
## Datamodel and database connection ##
The data model and database connection is done in **Client Setup**.
Launch **Client Setup** from the Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1890dd7b-8bd4-e103-c695-2750f0280afa.png)
Configure connection details for SalesForce for local connections.
Select either Microsoft SQL Sever (Windows Authentication) or Microsoft SQL Sever (Database Server Authentication).
After completing the settings, use the `Test` button to check if the connection is successful.
Press the `OK` button to save the settings.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e9a9e170-53d6-13c0-dc3c-42c975fe01e1.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/026b5471-4ca5-46df-cc26-3aa346253171.png)
## Synchronise data ##
### Preparation ###
To synch we need to set some key values for the authentication.
Launch **Site Setup** from the Sharperlight application menu.
Expand `Product Settings` node and select `SalesForce` node to show some properties on the right.
Please fill in these properties with your SalesForce information. When you register your SalesForce app, you can obtain these information.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/083239f9-5358-8be2-888f-af10be32bb77.png)
Once you fill in all, close the Site Setup window.
### Start pulling data from SalesForce API endpoints ###
Start **Sharperlight Service** from Windows Service dialog or from Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7924c73e-3517-79f7-be23-4d8d2e5dab9f.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8ee0628a-1c03-af79-e559-62312a6971e7.png)
Then launch **Solutions** from Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57516b2d-00a4-5177-8543-a45144b34a64.png)
Right-click on `Sharperlight SalesForce UI` node and choose **Execute**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1619723c-106d-0212-369c-e0ab5cd8c73f.png)
SalesForce Dahsepage is launched.
Go for System -> Sync Settings menu
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ece57a27-18b0-d72d-3ce1-e7f88fd4c786.png)
Click `Execute Sync Now` button to run the sync process. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0063716a-cc05-50bc-8dfa-28538174fd0f.png)
It starts fetching data from your SalesForce.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/414639ca-a6a1-410d-fc18-defc17ed26af.png)
Once the process finished, the Settings page shows its **End Time** like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4b46dc86-342b-b349-fc51-35119695eb20.png)
Now you got your data into the local database so it's ready showing your data.
With the dashpage you can see some predefined reports. **I used my demo SalesForce so there is not much data.*
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c126b29b-77bd-d25a-3d6f-9cff37980414.png)
## Report creation ##
I'm trying to create a report in Microsoft Excel and define the query to show revenues by users.
Start Microsoft Excel. Go to the `SharperlightXL` tab.
The SharperlightXL ribbon will appear and you will see various function icons.
Select cell **F5** and click the 'table' icon. A **Query Builder** will launch that supports table creation.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c3560d90-c9d0-aa0f-0968-6318e85fda9d.png)
### Filter settings ###
Select **Summary report** for `Mode`.
For Product, of course, select **Sales Force**.
Select **User** for the `Table`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d82b124b-77cc-3a42-9bb2-cc6ea24f37ce.png)
Then three more filters are set and they are **Full Name** from `User` table and **Closed** and **Won** from a joining table `Opportunities`.
Type `<All>` to cell **C3** and keep the selection for the input field of `Full Name` filter and double-click the cell **C3** to set the cell reference. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/66291882-2257-74b0-38ac-6a7098fb09ec.png)
### Select output item ###
You can design what fields you want to output to the report here.
You will be able to look for the fields in the `Selection` area and possible to transfer them to `Output` area by double-clicking them. 
The following fields are set to `Outputs` first. `Contacts`, `Closed Opportunities` and `Won Opportunities` count how many contacts the employee have, how many closed opportunities and won opportunities the employee have.  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ac2fc43d-5e69-e22b-7198-d05b85c9a649.png)
`Contacts` is actually `Contact ID` field and **Distinct Count** option is set.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0c829f39-7273-0c79-7ec8-367af2d28336.png)
`Closed Opportunities` and `Won Opportunities` are actually `Opportunity ID` field and **Distinct Count** option is set for the both.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7a31cfc9-6eb4-5b39-cb97-c69c26748e35.png)
Then for `Closed Opportunities`, `Closed` filter is applied to this output. It is the reason why its background-color is blue.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6b410eb2-2a68-f0ca-1390-8bdf2491a4e5.png)
Then for `Won Opportunities`, `Won` filter is applied to this output.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/524fc425-e9ea-4fec-85a8-bd19a62d8310.png)
Then I want to get revenues for each emploees so add a sub query to get it.
To get it, the sub query needs `User ID` so I added `User ID` to `Outputs` area first. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0db890bc-841c-95f6-f52c-e9e75409840c.png)
Then select `Add Sub Query` from the right-click menu in `Outputs` area.
Sub Query dialog is opened.
Select **Aggregation** for `Mode`, **SalesForce** for `Product` and **Account** for `Table` filters.
Drag `Owner ID` field from `Selection` area to `Filters` area and set the reference tag of `User ID` in the parent query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9247b378-ba13-5f2e-7a50-c3c3e2d966f2.png)
Drag `Annual Revenue` and `Account ID` fields to `Outputs` area, and set **Sum** option to Annual Revenue and **Count** option to Account ID.
Then click OK to save and go back to the parent query.
Rename the sub query as **Revenue Generated**.
To extract returns values from the sub query, two expressions are required.
One for `Annual Revenue` and one for the count of `Accounts`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/da67d2a9-27b8-7477-ad77-2785f3f0b7b7.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1b9f14c2-1870-1d68-7c72-9a7b1e3c7837.png)
`User ID` and the Sub Query its-self don't need to be visible so hide them.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a3d0a0ee-9c40-ab42-5e59-fa5f502408ad.png)
The query is now complete.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/586f1f4c-19be-c8d3-f090-af0dc4a9bc98.png)
Click `OK` to save it and run it.
A table is populated on the Excel sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b8fcec8c-9a79-b4de-79fa-ccf14dc0903d.png)
Try reselecting the cell **F5** that you selected before clicking the Table icon and you will see the Sharperlight table formula set.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7025364a-4d85-a425-2492-a33ee6e9d7d0.png)
Double-click the cell you referenced in the Full Name filter, i.e. C3. The lookup dialog is displayed. Try selecting any user from the list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/38d911b7-9974-92ba-3e99-f57433a753ef.png)
Immediately after selecting a name and pressing the OK button, Sharperlight's table formula is recalculated and the table is updated.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/85ebefcd-9fec-4699-d496-1acac0c7f95b.png)
The report is now ready.
Just a little tweaking and you're done :v:
Sorry for the small amount of data. But I hope you understand the concept.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e243cac5-bc10-591c-cc29-0b3f0dc82df6.png)

Enjoy SalesForce reporting with Sharperlight!!