Astral Reports - Help V5:
Watch the video
How to create a simple Trial Balance report.
Firstly, begin by free typing 'Connection', 'Company', 'Department', and lastly, 'Start Date' and 'End Dates'. This information makes up the key to this Trial Balance report.
Figure 1 | Populate the Key
You can now begin to generate the report.
Select a cell in column A, where you will populate the General Ledger Account codes.
Then click on the Astral Reports ribbon and select the ‘Get Table’ drop down, clicking onto ‘General Ledger Accounts’.
Figure 2 | Get General Ledger Account Codes
As a feature, you are able to establish connections to multiple Business Central databases, but in this instance, connect to the default ‘Connection’, and select the ‘Company’ you wish to retrieve your General Ledger Accounts from.
Figure 3 | Establish Connection
‘Fit Across’ rows allows the data to populate across the sheet, ‘Include Totalling Accounts’ allows totalling account, ‘Copy Formula in Adjacent cell’ copies the formula from the adjacent cell down the page.
But for now, leave all the boxes unticked and click ‘OK’ and the General Ledger Accounts will populate in column A.
Figure 4 | Populated General Ledger Accounts
To populate the General Ledger Account names, select the next column (column B) and click ‘Insert Function’.
Figure 5 | Populate General Ledger Names - Insert Function
Select ‘Astral Reports’ for the ‘Category’ and the ‘BC_GLName’ function, then click ‘OK’.
Figure 6 | Select Insert Function
The Function Arguments form will open, with 3 arguments that need to be populated.
Specify the ‘Connection’, the ‘Company’, and the ‘GLAccount’ by selecting them from the key in the spreadsheet and click ‘OK’.
Figure 7 | Specify Function Arguments
The data will then be populated.
Figure 8 | Populated General Ledger Names
Double clicking into the cell, you can see the information that has been taken into account.
Figure 9 | Cell Formula
But before copying this formula down the page to populate the rest of the G/L Names, the formula for ‘$A$7’ (see in figure 9) needs to be changed.
By changing it to ‘$A7’, this will lock the column, but allow the row to move.
Now drag the formula down the column, and the rest of the G/L Account Names will populate.
Figure 10 | Amend Cell Formula
Now, to insert the Amounts, select the next column (column C), and select ‘Insert Function’ again.
Figure 11 | Populate Amounts - Insert Function
Select ‘Astral Reports’ for the Category again, and ‘BC_GLAmount’ for the Function, and click ‘OK’.
Figure 12 | Select Function
Specify the ‘Connection’ and ‘Company’.
Figure 13 | Function Arguments
Then the ‘Start Date’, ‘End Date’, and ‘G/L Account’. And click ‘OK’.
Figure 14 | Function Arguments
The information will populate in the first cell.
Figure 15 | Cell Formula
Change the argument again by amending ‘$A$7’ (see figure 14) to ‘$A7’ (figure 15), which again will lock the cell but allow the row to move so that the formula can be copied down the cell.
Figure 16 | Amend Cell Formula
Copy the formula down the cell and the information will populate.
Figure 17 | Populated Amounts