【Excel Addin】Creating a Report in Excel

# Introduction #
I am writing a post about how to create Excel report with Sharperlight Excel Add-in here. 

# Creating a Report in Excel #
Open Excel and select **1. cell C5**, then click on the **2. Table** icon in the Sharperlight ribbon to launch the Query Builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8e67476b-dd43-553c-1354-4fb0470212b4.png)

## Specifying Filters ##
This corresponds to the `FROM` and `WHERE` clauses in SQL statements. In this case, we will use the **Dynamics 365 Business Central** as the data source, specifically focusing on the `Product`. Let's proceed with the `Company` set to **＜DEFAULT＞**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0b133e46-0701-0f86-8362-c161fd45ce3c.png)
Specify **Cust. Ledger Entry** for the `Table`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/412d9ae0-55b0-e910-c1b1-2a717e8317a6.png)
Upon selecting this table, two mandatory filters, namely `Customer No.` and `Posting Date`, will automatically appear. For both of these filters, we will keep the filter values as **＜ALL＞** for this instance.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/835c876d-99ee-6dea-db56-389965b821b0.png)
On the lower-left side, a tree structure will display the selected table's columns and JOIN information.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f405e8f-0cac-f030-b065-2ceff070c5cc.png)

From here, you can select the columns you want to use for output and the columns you want to use as filters. Let's proceed by dragging and dropping the **Document No.**, **Due Date**, and **Open** columns into the filter area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/21f5fa5f-02d5-1f80-91d5-11e04864097b.png)
Here are the specified values for each:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ee0dd6aa-5a0b-1152-0c4b-22b249fbeb25.png)

## Specifying Output Items ##
This corresponds to the `SELECT` clause in SQL statements. Double-click or drag and drop the columns you want to display as query results from the selection tree view to the output area. For this instance, we will output these three columns. Additionally, the `Amount` column has been sorted in descending order.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/29f45768-472b-8688-156d-687cf7124eb5.png)

## Preview and Confirm ##
Let's validate the query by pressing the `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fbc52c34-9345-3da9-b8d3-98be135a4326.png)
Everything worked correctly, so let's use the `OK` button to expand the data onto Excel. The Sharperlight table formula is defined in **Cell C5**, and a table has been created starting from the cell just below it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2ee0a87e-a738-7c97-d86f-e8e4c51f43b2.png)

## Improvement with Cell References for Filters ##
Let's make this report a bit more user-friendly. In **Cell A3** and **Cell A4**, enter the following values. Also, in **Cell B3**, enter **＜DEFAULT＞**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5d781d99-e24d-e10e-3c93-020c81178d7b.png)

Double-click on **Cell C5** to invoke the query definition.
First, **1.** Select the value field for the `Company` filter in the query builder. Then, **2.** double-click on **Cell B3**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f42715da-61e7-b545-d879-87f003757d56.png)
The formula referencing **Cell B3** will be set as the value field for the `Company` filter in the query builder.

Similarly, **1.** select the value field on the right side of the `Due Date` filter in the query builder. Then, **2.** double-click on **Cell B4**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8b782c8b-e884-71b0-4f52-81dac0bc8da1.png)
The formula referencing **Cell B4** will be set as the value field for the right side of the `Due Date` filter in the query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f7a68ba5-13c4-8b06-d6b6-de5f37b1aa63.png)
Set **Cell B4** with the date value **30/06/2022**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8427660a-ad46-b3db-a0a4-4d6e8cb040a7.png)

The query modification is now complete. Reflect the changes by clicking the `OK` button in the query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9938a2f1-a024-01be-caa4-17a1f0a929ac.png)
Now, the values entered in **Cell B3** and **Cell B4** will be reflected in the Sharperlight table formula (query) filters. Try double-clicking on **Cell B4**. This should open a date selection dialog.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ef950098-a3d1-56f7-c386-1539528e24c1.png)
If you select a different date, the query will be recalculated immediately.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d5fdd568-a46e-34ca-1dbc-51b7c6536ba0.png)

I will change the background color of filter cells and organize the report. I will use Excel's standard themes.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/be38df36-3eba-220e-93bf-fa12bc6d038d.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/308b95d9-63b2-809a-caa7-039f7313b28b.png)

# Conclusion #
What do you think? It seems like creating a report is easy.