【Excel Addin】SAP Business One Report on Excel Worksheet

[Sharperlight](https://www.sharperlight.com/) allows you to easily extract the data you need from SAP Business One onto an Excel sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cd22c399-79a8-8af4-0c6d-6a35ac2bbd85.png)
Let's take a quick look at a sample query definition.
## Query ## 
Query is defined in the form of a Sharperlight formula at the origin (upper left) of the generated table.
In this example the formula is defined in cell F5 like this:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/29a34029-d9a7-5780-2d32-c0ec6f809c98.png)
Double-click cell F5 to view the formula in Query Builder.
Here is the query definition.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1c7b4485-6930-ea49-5cb0-886f0bebb5a6.png)
Now, I will explain in detail in the order of the numbers assigned to the figure.
### 1. Filter: Mode and Product ###
You can choose from the following modes with `Mode filter`. Select **Detailed Report** if you want all the records queried, or **Summary Report** if you want summarized results.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1e716506-b01c-f6ab-da5c-6eaeebc98ca7.png)
This time, **Summary Report** is selected.
Of course, select **SAP Business One** for `Product filter` for this article.
When you open the Product lookup dialog, you can see the list of Products set in the Sharperlight you are currently using. 
Product registration with Sharperlight is defined in the `Client Setup`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0e23d5da-8bf6-329d-6984-287cfea6df68.png)
### 2. Filter: Company ###
`Company filter` specifies the company registered in SAP Business One. You can also select multiple registered Companies.
A cell reference is set and the value entered in the cell is passed to this filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5380da76-188b-c85a-c047-a20f4e759dc5.png)
### 3. Filter: Table ###
`Table filter` specifies which table in SAP Business One is the target table.
Here select G/L Transactions.
### 4. Filter: G/L Account Code ###
This filter is a **mandatory filter** attached to the G/L Transactions table. It is based on the SAP Business One datamodel definition. 
`<ALL>` is specified. This means that all account codes are subject to this query.
### 5. Filter: Base Period ###
This filter is also a **mandatory filter** attached to the G/L Transactions table.
A cell reference is set as well as `Company Filter` and the value entered in the cell is passed to this `Base Period filter`.
Also, the background color of this filter is light yellow, which means this filter is a prompt filter. Regular filters are used to form the WHERE clause during query generation, whereas prompt filters are used only to pass values, not to the WHERE clause.
The value entered in the referencing cell is passed through this prompt filter to the query, and that value is used in the rest of the query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/59340258-2020-f553-e6ca-d979030c6c8c.png)
### 6. Filter: Year To Date Period ###
This `Year To Date Period filter` was selected from the Selection list as it was required for this query.
This filter refers to the `Base Period filter`. This means that the value entered in the cell C8 is passed to the `Base Period filter` which is a prompt filter, and that value is used by this filter.
You could see 'oys' which is a special tag means **Start of Year - Before Offsets**, calculates the first period of the year from the given period. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6afded2f-a52b-d8d7-d926-fd2b3da30ed6.png)
### 7. Filter: P&L Account Types ###
**AR** and **AP** are selected for this filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d5d47874-62b5-f11b-bade-0198046c3435.png)
### 8. Filter: Carried Forward Balance Period ###
The values have a range between **1990/001** and **the period** given to `Base Period filter`.
### 9. Filter: BS Account Types ###
**Other** is selected to this filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c530e259-0c38-e4c5-1d7f-f8314bd886e2.png)
Also, the each of the four filters, i.e. `Year To Date Period`, `P&L Account Types`, `Carried Forward Balance Period` and `BS Account Types` are given brackets. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b4989fa0-8017-6bf0-fa60-1054b49d0fba.png)
Each of these brackets is set with this option of the filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/671da392-0b84-7d6f-07ef-1d3effcc4435.png)
There is also **OR** in front of `Carried Forward Balance Period filter`. This is set with this option of the filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/65792c3d-8b61-f218-51bc-2ea69897d88e.png)
Then the settings of those 4 filters make this sort of  WHERE clause in theory.
**(**  
    **(** `Year To Date Period` BETWEEN {@Period} oys AND {@Period} AND `P&L Account Types` IN (AR,AP)  **)**
**OR**  
    **(** `Carried Forward Balance Period` BETWEEN 1990001 AND {@Period} AND `BS Account Type` ='N' **)**  
**)**
### 10. Outputs
Here you specify which fields you want the query to return values for.
You can choose fields from Selecion area by dragging or double-clicking.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/80f45326-fa17-62bd-4a4b-c01b5ac2a0ad.png)
`Account No` and `Account Type` have **Sorting** attribute which is set with this option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/008879c5-b0e4-7b52-31dc-5914b0875692.png)
Also, `Balance` has **Sum** attribute, which is added to numeric field by Engine when **Summary Report** is chosen for `Mode filter`.
This option can be changed here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f23e18cb-e2f1-000b-e215-901f54c601c7.png)

This completes the query definition. Press the OK button to save the query and run it at the same time.

If the cells C7 and C8 referenced by the query's filters do not have values, the query will return nothing. Double-click cells C7 and C8 respectively and select the respective filter values from the lookup dialog. The Sharperlight formula is recalculated and the result is displayed like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/442cb966-444b-4fbb-1e97-46b8cbb0667e.png)

Try creating a report with a design suitable for your company's business with Sharperlight and Sharperlight SAP Datamodel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/27858b7f-1e29-a761-fbee-bd9300b959f3.png)
Thank you for reading this article.