【Excel Addin】Dynamics 365 Business Central Report on Excel Worksheet

This time, I will try setting up the connector (Datamodel) of [Dynamics 365 Business Central](https://dynamics.microsoft.com/ja-jp/business-central/overview/), connect to the demo database, and create a 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 **BUSCENTRAL - Dynamics 365 Business Central** from the tree view of the Datamodels page. A list of various items will appear on the right.
Find the **BusCentral** 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/8051daf1-abf9-ee09-bd80-a098eaed31cb.png)
## Getting the demo database ##
Databases are synchronized in a deliberate and precise manner using the interactivity with APIs built into this datamodel. This time, as an overview, we will use the demo database prepared in advance.

From the same item list, open the **Demo Database** folder and download the zipped demo database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0c1fd241-2a04-4766-813c-3951e2125960.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3213e509-99de-8ff0-db4b-a6b89071c56b.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/19a525a9-455f-0eef-4474-8aa9a28618d2.png)
## Restoring the demo database ##
Start Microsoft SQL Server Management Studio (SSMS) and connect to your database server.
Extract the backup files from the demo database ZIP file you downloaded earlier.
Install the demo database using the database restore function of SSMS.
(Backup was created and backed up on SQL Sever 2017)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a7c8a2ce-5d72-bc64-8e3f-63945a57e682.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 Dynamics 365 Business Central 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/7fb6569f-2259-cff7-726b-cb21b21c6641.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/026b5471-4ca5-46df-cc26-3aa346253171.png)
## Report creation ##
I'm trying to create a report in Microsoft Excel.
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.
### Filter settings ###
Select **Summary report** for `Mode`.
For Product, of course, select **Dynamics 365 Business Central**.
Double-click the value field for `Company` to bring up the search dialog and select **DEFAULT** from the list.
Select **Cust. Ledger Entry** for the `Table`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3980ad86-731a-c8e8-e0bf-37ef44ed91a7.png)
### Select output item ###
Double-click **Sell-to Customer No.** and **Amount (LCY)** from the tree list in the selection area to transfer them to the output area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9dadcdb4-ec98-231f-a78f-90ee1008e426.png)
Expand the `18\Customer` folder (representing JOIN) and set **Name** to the output area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ab269c94-336a-ba58-4e47-f4099bae8989.png)
Edit the field descriptions set in the output area for the report. Select each field, select 'Change Description' from the right-click menu, and change them to **Customer No**, **Customer Name**, and **Amount** respectively.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1785eae6-8edc-cfd0-1870-02cfa1237d10.png)
Run the query with the `Preview` button. The result looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/df121ce0-14d7-6808-23dd-a070219a3a2e.png)
Now save and execute with the `OK` button.
A query name edit dialog will appear, just press `OK`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/609e9fc9-6961-487f-fbb5-2bf432e6fe4b.png)
A table is created on the Excel sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c278e308-dd23-77bc-d592-d0dea9c2e6a0.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/2d4a8b10-e0ab-1cb1-5268-f9b0e774304f.png)
Double-click the cell **F5** to start the query builder again and edit the query.
### Setting Cell References to Filters ###
As it stands, you have to launch the query builder every time you want to change the filter value. Makes the filter value changeable on the sheet.
First, enter ```<DEFAULT>``` in cell **C7**.
Double-click cell **F5** to launch the query builder.
Leave the `Company` value field selected. Double-click cell **C7**. A cell reference formula is set to the value field of `Company`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e6da4586-842f-bead-14c3-24f8d4e7415e.png)
Save with the `OK` button.
Try double-clicking cell **C7**. The search dialog is launched and Company is now selectable.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2fe1b8c0-5ff8-617b-1262-2101fad9e2f2.png)
Let's edit the query a little more. Now add about 3 filters.
In the selection area, find the three fields below and drag and drop them into the filter area.
- Document
- Due Date
- Open

Set each filter value as shown below. Note that the Due Date filter uses cell references. As we did above, select the value field you want to set and double-click the cell you want to reference. In this case cell **C8** is referenced.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/acf701c5-240a-6a6b-5eb4-fcd6236f89d4.png)
Save with the `OK` button.
Double-click the cell you referenced in the Due Date filter, i.e. C8. The date options dialog is displayed. Try setting 30/06/2022.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4f68395b-10ef-c3fc-bc77-6c7ca484402f.png)
Immediately after selecting the date 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/d1d3c92b-b605-ca6a-c9fe-b6bd3e8e82b1.png)
The report is now ready.
Just a little tweaking and you're done :v:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/34e7e34a-8eab-d01c-5187-5deac4c2ea84.png)
## Synchronizing data ##
Dynamic 365 Business Central is a cloud-based solution.
In the article above, we used a sample database, but the Sharperlight Business Central datamodel is fundamentally designed to synchronize data from the cloud into a local database for reporting. However, the write-back function implements direct write-back to the cloud using the Business Central API.

Sharperlight scheduler is used for data synchronization.
By scheduling data synchronization tasks in the Sharperlight scheduler and synchronizing data at a fixed time every day, you can always report using the latest data.
You can also run sync tasks at any time whenever you want.

Launch the **Solution** from the Sharperlight application menu, select the ``Homepage'' of the ``Dynamic 365 Business Central'' node, and select ``Run'' from the right-click menu to open the main dashboard page. (Sharperlight Service must be running)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7c7be5b1-dbf5-4574-85bf-c9193f2d7475.png)
Select `Scheduled Tasks` in the `Scheduler` tab.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/55c32501-4a00-18e3-2938-3c270c99331b.png)!
A list of tasks for the scheduler is displayed. From this screen you can manually run data synchronization tasks.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/68202c18-a4ca-6a2f-14a5-d4024e962e3e.png)

Enjoy Dynamics 365 Business Central reporting with Sharperlight!!