【Scheduler】Splitting sheets with Scheduled task

# Introduction #
We are using SAP datamodel amd we would like to pupulate sheets by customers per state monthly and save them to the file system. 

- Datamodel: SAP Business One
- Database : SAP Bisiness One demo database (Microsoft SQL database) 

# What we achieve #
The template Excel workbook is recalculated with different parameters, and new Excel workbooks are created with run date and a State name on their file name like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/261b2701-4cf1-a89a-6619-999dbb112a26.png)
    1. Template Workbook
    2. Generated woorkbooks

# Implementing #
## 1. Preparing the template workbook for splitting ##
The base query needs to be prepared for splitting.
It returns summarised information for all customers from A/R Sales Invoice table as the base query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/dc5a1efa-fb6a-380a-57e5-f0a1511a02e8.png)
    1. A table formula is definied in Cell D3
    2. The definition of the table formula with Query Builder
    3. Filter: **BP Type** must be C for Customer only
    4. Filter: **Customer Name** referring to Cell B3 is the key filter for splitting
    5. Filter: **Document Date** referring to Cell B4
    6. Filter: **State** referring to Cell B5

### a. The list of Customers ###
We prepared the list of customer with Sharperlight table formula in the another sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/48eab3bf-c745-744a-1c64-7522d10dcf77.png)
    1. A table formula is definied in Cell C3
    2. The definition of the table formula with Query Builder
    3. Filter: **State** referring to Cell B2, the value in Cell B2 is passed from Scheduled Task. 
    4. A **Year/Month** value is passed to Cell B1 from Scheduled Task.
Additionally, we put **=Filters!B1** in Cell **B4** and **=Filters!B2** in Cell **B5** on ``Template`` sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/66f63236-6aa6-e33b-b16c-985686f8a10a.png)


### b. The Split Sheet formula ###
Definig a split formula in cell A1, the formula would be the following format.
```
=@mdSplitSheet("By Customer",AR_Sales_Invoice_Customer_Name[List of Customers],"",$B$3,"Green",0)
```
#### The 1st parameter ####
Giving a name of the split formula.
#### The 2nd parameter ####
A list of customers. The base sheet would be splitted with this value.
We prepare the list in the another sheet and mention it in the formula.
#### The 3rd parameter #### 
A lits of sheet name. You can specify the name of each split sheet. If it is left as a blank, the filter value is used. The customer name is used in this case.
#### The 4th parameter ####
Specify where each customer name from the list given is passed to.        
#### The 5th parameter ####
Specify the colour of the splitted sheet.
#### The 6th parameter ####
Split Sheet formulas by default extract as an immediate child of the parent worksheet which is level 0. Setting the level to 1 will force the Split Sheet formula to extract from the children and level 2 will only extract from the grand children. Any level number can be added to a Split Sheet formula but it will only extract when it reaches that hierarchical level.

### c. Testing ###
The definition has been now completed so try to run the formulas.
We set **QLD** in Cell B2 on ``Filters`` sheet and run the formulas by clcking **Worksheet - Recalculate** icon here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/96f11906-a906-cf84-958b-3df8546435e0.png)
We would get a split worksheet per Customer.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ed87fbd1-122e-d01b-4b07-1988b1ea3d62.png)
Once we confirm that the formulas work fine, we need to delete the split worksheets to use this as a template.
We can remove them by using Tree pane and it can be opened by clicking **Tree** icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c74d1dd3-bb31-b7f0-c697-9f89c3c5e8d8.png)
We can delete the node from which all split sheets are hanging by choosing the node and clicking the **Delete** icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cabd1142-527e-df7a-ba53-df1e4c83490e.png)
Once the split sheets were deleted, we can save the template workbook now.


## 2. Defining Scheduled Task runs monthly per state ##
We create a Scheduled Task which does recalculate the template worksheet and save as **Report_{State}_{YearMonth}.xlsx** to a nominated folder.
The task will run monthly, and **State** and **Yeae/Month** values are passed to the queries in the template workbook.

### a. Scheduled Task ###
Open Scheduler from the Sharperlight Application menu.
The task needs to be run monthly, and the workbook must be recalculated per state.

#### Adding Query Combinations and Query Combinations End ###
We choose **Query Combinations** and **Query Combinations End**. They work for repeating execute the workbook recalculation per state.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f5b296bf-61d0-96fb-db88-55bff8869789.png)
**Query Combinations** must have a query and it must return a list of states.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b56aedd1-1291-2c71-477d-1b9a112a72bd.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4512da91-d66f-0e95-9a32-1b37a54a767c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3ebc9c80-722c-5b43-67f9-3a1907576031.png)

The values returned from the query can be refer with its **Name**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5b2b96fe-383d-e6df-6eef-a52300cad6c9.png)

#### Recalculating the Excel workbook per state ###
We add **Excel Workbook** action between **Query Combinations** and **Query Combinations End**.
The Excel workbook must be in a folder which is nominated as a secure folder to Sharperlight.
It can be done with Sharperlight ``Site Setup``.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/030b2af3-ebde-d678-666e-7f19b6af0856.png)
In this case shown above, ``C:\QiitaSampleTemp\`` and ``C:\QiitaSampleCopy\`` folders can be accessible from Sharperlight. 
We can see the target Excel workbook is one of the secure folders.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bbed2888-7e29-c7e9-88c3-af14e1f95ce8.png)

##### Specifying the template workbook #####
We specify the target Excel workbook here in the task.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f12b4db2-f7cd-fec3-20f0-6e9cf5fa9791.png)

##### Configure parameters #####
We want to pass two parameters to the Excel workbook, one is **{Year/Month}** value and the other is **State**, and we can spcify them here.
**{Year/Month}** value should be passed to Cell B1 on ``Filters`` sheet.
**State** should be passed to Cell B2 on ``Filters`` sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fb881b2d-b41e-7d85-6f1b-d9b1ffc17730.png)
This is achivable with this syntax here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1a733058-1a8a-33bd-a4c5-d0628d871bd8.png)
The first one is passing current **Year/Month** generated by Sharperlight Expression to Cell B1 on Filters sheet.
The second one is passing a **State** value generated with the **Query Combinations**. 

##### Saving the result #####
After recalculating the workbook, we save it as an Excel workbook for each state.
It can be specified here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/85c78dc3-cb46-f504-ab17-d6ca54fa815d.png)

##### Other Options #####
We make this enabled so when the result is saved all Sharperlight Formulas are removed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b93fe18e-47d8-2843-c8de-8df53d404d57.png)


#### Adding a trigger ###
We set a trigger on Triggers tab.When Sharperlight itself and Microsoft Excel are on the same machine, it is better to use  **Service Timer** (Sharperlight Service), rather than Windows Scheduler for easier configuration.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c5699b0c-1101-eb2a-0287-41f93bc6dfc8.png)


# Conclusion #
This is a simple example of Office automation with Sharperlight Scheduled Task.
However, it is very hard to configure this type of automation as Microsoft Office may not be well designed for automation.
Therefore, we have had some experiences with some issues.
We will introduce about such solution/workaround in another post.