Decide, use parameters, and calculate

Source/Input: There is an ERP system that can export order/ledger information in a pre-defined report structure, on an Excel worksheet. The entries are listed per bookkeeping account and within an account in time order.

Target/Output: The order information must be collected by product type and aggregated by time periods, creating also several different totals (per time period, per product type).

The manual solution is to simply go through the rows of the ERP export and distribute the values one by one among as many columns as the number of product types; also manually create the necessary totals temporarily using the SUM function in separate cells. These totals will be copied again manually to the output sheet.

There are some caveats, though:

• Both the number of product types and also the number of time periods can change (in a month there are mostly four weeks but sometimes five) making the task each time a little bit different.
• Every time the task is executed the time period headers must be changed in the output report

These variations make the task not just boring but also challenging: too easy to make mistakes.

Let's see, how Djeeni can help.

The process uses three worksheets:

Input data: An imaginary export file from an accounting/ERP system, containing orders that are booked to different accounts.

Report: The output report that totals the orders by account, day, and grand total. The report structure is very flexible using several parameters that are discussed just below (DjeeniParameters).

DjeeniParameters: This worksheet contains the parameters that make the process flexible. Parameters with a yellow background are used by the process itself. The remaining parameters can be set freely.

• The report covers a certain period. Parameters from B2-B7 specify the start and end date of the period (inclusive). When we work with Excel data the best practice is to let dates be specified as separate year, month, and day values to avoid misunderstanding (by users and by MS Excel itself). Month values are 1 … 12 denoting January … December respectively.
• Parameter B10 specifies the starting column of the report. By default, it is B leaving column A for labels. The process does not touch columns before the given first column.
• Parameter B11 is a (by an MS Excel formula calculated) numeric value of the column letter in B10.
• Parameters B8-B9 and B12 are used by the process to calculate or store different values.

Download a workbook containing all three worksheets from https://djeeni.com/djeeni/demoprocess/decide_parameter_calculate . Save this workbook to any folder of your choice on your computer and make it editable (otherwise the output report cannot be created).

You must manually adjust the Workbook parameter of all three Wsheet Use process steps at the beginning of the Djeeni process to match the location where you have saved the file you just downloaded ⇒ Open the process in Djeeni and double click on each WSheet Use step to open the parameter popup window, where you can change the file path.

The automated process works in four phases:

1. defining the worksheets; initializing parameters; removing previous report version
2. the number of accounts that can be found on the input worksheet is counted; the account numbers themselves are collected;
3. the structure of the report is generated (according to the number of days in the period and to the number of accounts); and
4. the orders themselves are processed; the totals are calculated.

See the instruction-by-instruction description of the phases below.

• Step 1-3: The three worksheets are identified.
• Step 4: Explanatory comment.
• Step 5: The number of accounts is set to 0.
• Step 6: The account number list is cleared.
• Step 7: The old report structure is cleared.
• Step 8: The date value of the period start date is calculated.

• Step 9: Explanatory comment.
• Step 10: Loop through the rows of the input worksheet to count and collect account numbers. Note, that the last cell with data is sought in column A (unlike in Step 22). Steps 11-14 will be executed for every row on the worksheet.
• Step 11: Check if the current row contains an account number by checking if the cell in column A of the current row is empty. If yes, execute steps 12-13. If no, do nothing.
• Step 12: Increase the value of the account number counter by 1.
• Step 13: Collect the account number from the cell value in column A of the current row.
• Step 14: End conditional steps started at Step 11.
• Step 15: Next row in the list started at Step 10.

• Step 16: Explanatory comment.
• Step 17: Loop through the days in the given period (using Row List Start). Execute for each loop steps 18-19. Note: This loop goes from 0 to <number of days>, i.e. we need to:
  • Calculate the number of days within the period (parameters B5-B7 for the end date; parameter B12 for the start date). Note: if the start date and end date is the same day then there are 0 days in between but we have to generate the report for the only day (day '0'). This is the reason why we start counting from (day) 0.
• Step 18: Copy the list of account numbers (saved to the range D8:<last column>8 on the DjeeniParameter worksheet) to the header section belonging to the next day (calculated by <start column> + <number of accounts> * <current day> from the loop in Step 17). This is row 2 of the report.
• Step 19: Set the same header section range to the corresponding date. The day range is calculated by: from <current day column> to <next day column - 1>; where <current day column> is calculated exactly like in Step 18. This fills row 1 of the output report.
• Step 20: Switch to the next day in the loop started in Step 17.

• Step 21: Initialize the account number counter to the value of -1. Since at every account number we increase the counter by one, it will be zero for the first account. And zero is the best starting value at the first item in a list for further calculations.
• Step 22: The rows of the input worksheet are processed for the second time. Note, that the last row with data is sought in column B (unlike in Step 10).
• Step 23: Check if the current row is an account number row(column A in the current row is not empty). If yes then execute Step 24. If not, do nothing.
• Step 24: Increment account number counter.
• Step 25: End of the conditional steps started at Step 23.
• Step 26: Check if the order is fully paid (column E in the current row does contain 'Y'). If yes, then execute Steps 27-31. If not, do nothing.
• Step 27: Check if the order date (column B in the current row) is in the given period (falls between the start date and end date, inclusive). If yes, then perform Steps 28-30. If no, do nothing.
• Step 28: Add the order amount to the account number total.
• Step 29: Add the order amount to the daily total.
• Step 30: Add the order amount to the grand total.
• Step 31: End of the conditional steps started at Step 27.
• Step 32: End of the conditional steps started at Step 26.
• Step 33: Switch to the next row in the list starting at Step 22.
• Step 34: Finish and exit.