Skip to main content

How to Use the Balance Sheet Dashboard

Updated over 2 weeks ago

Prerequisites

  • Power BI Desktop is required to use the Coins standard dashboard.

  • Extract parameter "%GLEL2ANAL" must be set. A pipe separated list of GL Element 2 analysis set descriptions to be used (up to eight). If the entry is blank (or not entered) then the analysis field will be omitted from the extract.

  • Ensure "GL Account Balance Function" and "GL Account Movement Function" are both created.

Following entities must be generated, their corresponding SQL tables must be created and their data must be extracted.

  • CoCompany

  • GLPeriod

  • GLAccount

  • GLElement2Anal1

  • GLElement2Anal2

  • GLAccountYear

  • GLAccountPeriod

Create the required "Generic" views

  1. Go to System >> Extracts >> Views.

  2. Filter the Schema for "Generic".

  3. Select all the rows.

  4. Run "Create Views".

Generic.COCompany, Generic.GLPeriod, Generic.GLAccount

Create "BalanceSheet" views

  1. Go to "System >> Extracts >> Views.

  2. Filter the Schema for "BalanceSheet".

  3. Select all the rows.

  4. Run "Create Views".

Note: Views are created based on Coins SQL tables. This means all required SQL tables must already exist when you try to create the views.

Download the "PBIT" template and configuration "XLS" file for "BalanceSheet" report from System >> Extracts >> Reports.

Chart of Accounts set up follows SureStart structure

If you are using SureStart, or inherited the SureStart chart of accounts structure without any change to it, that is:

  • GL Account Element2 Analysis1

  • GL Account Element2 Analysis2

You can use the Balancesheet dashboard without making any change to the "BalanceSheet.xlsx" and "BalanceSheet.pbit" templates.

After downloading the "XLS" and "PBIT" files from the "Extracts/Reports" menu, follow the steps below:

  1. Place both files in the directory that they should reside.

  2. Open the "BalanceSheet.pbit" template, enter the server Name, Database and Path for Coins BalanceSheet Mapping spreadsheet.

    When this file is opened for the first time, after entering the required details you will also be asked to enter credentials for connecting to SQL server.

  3. Save the BalanceSheet file after loading the data to the template. The saved file will have the ".PBIX" extension.

Chart of Accounts setup is different from SureStart

If your chart of account is different from SureStart, you need to make some changes in both the "BalanceSheet" Excel file and the "PBIT" dashboard template file.

"BalanceSheet.xlsx" mapping file:

After downloading the "XLS" file from the "Extracts/Reports" menu, the following changes need to be made to the Excel file in order to reflect your Chart of Accounts setup in the dashboard:

  1. Run the following SQL query on "Coins_BalanceSheet.GLBSAccount" view to get the list of "Heading1", "Heading2", "Heading3" and "BalancesheetNormalized".
    ​
    Query:
    ​
    select * from Coins_BalanceSheet.GLBSAccount
    ​
    order by company, GLElement2Anal1, GLElement2Anal2
    ​

    1. "Heading1" shows description of AccountType

    2. "Heading2" shows description of GLElement2Anal1

    3. "Heading3" shows description of GLElement2Anal2

    4. "BalancesheetNormalized" is a concatenation of AccountType, GLElement2Anal1 and GLElement2Anal2

  2. In the "BalanceSheet.xlsx" file, columns B, D, E, F, G and H should be changed to reflect your Chart of Accounts setup based on the query provided in step 1 above.

    1. Column B: Replace its values with the "Heading3" column from the SQL query result. Extra Rows can be deleted from each section. More Rows can be inserted to each section if required.

    2. Column D: Replace 5,6,7,8 and 9 with the "GLElement2Anal1" column from the SQL query result.

    3. Column E: Replace its values with the "GLElement2Anal2" column from the SQL query result that relates to "Heading3" values displayed in column B.

    4. Column H: Replace its values with the "BalancesheetNormalized" column in the SQL result. Each row should be associated to its corresponding "Heading2" and "Heading3".

    5. Column F: This column shows the total of each "AccountType" as per the description in "Heading1" from the SQL query and the total of "GLElement2Anal1" value. Replace its values with "Heading1" and "Heading2" columns from the SQL result accordingly.
      ​Note: Both Excel's column F and column B should read the same for totals; for example, both columns should say "Total Fixed Assets", or both columns should say "Total Liability".

    6. Column G: This column has the sort order for all totals. If new totals are added to the Excel file and / or the existing totals are removed from the file, it should be renumbered.

  3. Once all steps above are completed, column A of Excel file must be Re-ordered (that is, copy row 2 of column A and paste in the rest of the rows. This will automatically re-number the rows).

  4. Save the Excel file.

"BalanceSheet.pbit" dashboard template

Open the "BalanceSheet.pbit" template and enter the server Name, Database and Path for the Coins BalanceSheet Mapping spreadsheet.

When this file is opened for the first time, after entering the required details you will also be asked to enter credentials for connecting to the SQL server.

Certain changes should be made to the measures if Chart of Accounts setup is different from SureStart. Measures that need to be amended have the following naming conventions:

  • "*-TotalHeading2-*"

  • "*-TotalHeading1"

Where the first asterisk (*) in the measure name represents the AccountType, i.e. "A" or "L", etc Examples are: "L-TotalHeading1" or "L-TotalHeading2-1".

The second asterisk represents the total number under each Heading2, e.g. "A-TotalHeading2-1" or "L-TotalHeading2-2".

Measures with the name "*-TotalHeading2-*" use the GLElement2Anal1 for calculating the total value for each one of them. You need to replace the value placed in double quotes in 'GLBSClosingBalance'[Analysis1] = "6" with the value from Column "D" of the Excel file, i.e. "Heading2", e.g. replace "6" with "Z6" so it reads 'GLBSClosingBalance'[Analysis1] = "Z6" in the measure.

Measures with the name "*-TotalHeading1" are the total of GLElement2Anal1 associated to each AccountType, i.e. Total Asset or Total Liability. You need to replace the value placed inside the {} in 'GLBSClosingBalance'[Analysis1] IN {"5","6"} with the values from Column "D" of the Excel file, i.e. "Heading2", e.g. replace {"5","6"} with {"z5","Z6"} so it reads:

'GLBSClosingBalance'[Analysis1] IN {"Z5","Z6"} in the measure.

Note: If a new section / total is added within AccountType in the "BalanceSheet.xlsx", you should define a new measure for it in the dashboard template. The new measure should be a copy of existing one, its name should be amended with a change to the second asterisk of the measure name "*-TotalHeading2-*", e.g. If a new section and its corresponding total is added to the AccountType Asset, the new measure name should read : "A-TotalHeading2-3". Also, in the formula the part saying 'GLBSClosingBalance'[Analysis1] = "6", the value of 6 should be changed accordingly so it uses the correct Analysis1.

The following points should be remembered:

  • There are four types of measures as listed below, any change should be applied to all four types:

    • "*-TotalHeading2-*"

    • "*-TotalHeading2-* AsAt"

    • "*-TotalHeading2-* PY"

    • "*-TotalHeading2-* PY AsAt"

  • If new measures with naming convention of "*-TotalHeading2-*" are introduced, they must also be added to the below measures

    • Current Year

    • Current Year AsAt

    • Prior Year

    • Prior Year AsAt

The amended Dashboard template needs to be saved. The saved file will have the ".PBIX" extension.

Did this answer your question?