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.
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.
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.
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.
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.
We would get a split worksheet per Customer.
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.
We can delete the node from which all split sheets are hanging by choosing the node and clicking the Delete icon.
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.
Query Combinations must have a query and it must return a list of states.
The values returned from the query can be refer with its Name.
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.
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.
Specifying the template workbook
We specify the target Excel workbook here in the task.
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.
This is achivable with this syntax here.
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.
Other Options
We make this enabled so when the result is saved all Sharperlight Formulas are removed.
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.
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.