Astral Reports - Help V5:
Watch the video
In this example, we’re starting with a standard Chart of Accounts report.
All we’ve done is modified the key to include the dimension range that we want to analyse by and report on.
Figure 1 | Modified Key
So what we want to know is if there have been any postings made against the combination of ‘SMALL’, ‘ENGLAND’, ‘MARKETING’, ‘M1 EXTENSION’, and ‘JOHNROBERTS’.
The first thing that needs to be done is to insert an ID Filter, so click on a blank cell below the key and go to ‘Insert Function’ in the ribbon.
Figure 2 | Insert Function
Select the ‘BC_GLDimSetIDFilter’ function from the list of options and click ‘OK’.
Figure 3 | Select Function
Fill in the Function Arguments, by selecting the related fields from the spreadsheet for ‘Connection’ and ‘Company’, locking them in place as we go.
Figure 4 | Fill in Function Arguments
Now, for ‘Dimension Code’ place the range of Dimension codes by selecting and dragging over the key.
Figure 5 | Fill in Function Arguments
Then do the same for ‘Dimension Value’.
Figure 6 | Fill in Function Arguments
Click ‘OK’.
There will now be a returned the ID Filter number. This is the combination of the postings of ‘SMALL’, ‘ENGLAND’, ‘MARKETING’, ‘M1 EXTENSION’, and ‘JOHNROBERTS’, and how Business Central has identified it by giving it an ID Filter.
Figure 7 | ID Filter Number
In the ‘Balance by Dimension’ column, this is where we want to know the amount against the ID Filter posting – so how much has been posted against these combinations.
Select the first cell in the ‘Balance by Dimension’ column and click ‘Insert Function’ again.
Figure 8 | Insert Function
Select ‘BC_GLDimSetAmount’ and click ‘OK’.
Figure 9 | Select Function
Fill in the Function Arguments again as before for ‘Connection’ and ‘Company’.
Figure 10 | Fill in Function Arguments
For ‘Dimension Set ID Filter’ – select the ID Filter we just created, locking it in place.
Figure 11 | Fill in Function Arguments
Select the ‘Start Date’ and ‘End Date’ which are the dates that we want to analyse against.
Figure 12 | Fill in Function Arguments
Now select the first ‘G/L Account’, and this time, lock the column but not the rows.
Then click ‘OK’.
Figure 13 | Fill in Function Arguments
Now drag the formula down to the bottom in the Balance by Dimension column, and you will see some information start to populate.
Figure 14 | Drag Formula
You can see in this example that there is the VAT Posting, AP Control Account Posting, and then individual expenses that were posted against.
Figure 15 | Populated Formulas
Within the filtering itself, you have the ability to change the key to update the returned results.
To do this, place the report into ‘Design Mode’.
Figure 16 | Enable Design Mode
Then change the value of a field – i.e. ‘SMALL’ to ‘LARGE’ and for ‘EMPLOYEES’ we’ll filter by using the symbol (see figure 17), which represents ‘blank’ in this function.
Figure 17 | Editing Key
You’ll notice (in figure 17) that all the values have changed to show ‘#DESIGN#’ in the ‘Balanced by Dimension’ field. However, once finished, deactivate ‘Design Mode’ and click ‘Refresh’ and the values in the ‘Balance by Dimension’ column will be updated to reflect the new filters. You will also see that it have allocated a new Dim Set ID to 16.
Figure 18 | Exit Design Mode & Refresh
So by using this symbol against any of these filters, you can exclude certain categories that have not been posted against or exclude one element of the range.