GL Tutorial: Saturn Report to Iterate Branches

This tutorial is not available for Accredo Mercury.

This tutorial shows you how to create a GL Financial Report that will iterate through branches, running the report for each branch.

1. Open the GL Financial Report Designer from Navigator > Setup > General Ledger > Financial Report Designer.
2. Click the Wizard button. The GL Design Wizard will open.
3. Enter a Report Title, such as Statement of Financial Performance.
4. Enter required details on the Iterators tab. Make sure you include the following selections:
   • Consolidate Branches - Selected
   • Consolidate Departments - Selected
   • Prompt for Branch - Selected
5. On the Bands tab, make the following selection:
   • Retain Existing Bands - Clear
6. In the Report Columns Grid, select the columns required from from the Contents, for example:
   • Account > Account Code
   • Account > Account Name
   • Actual > Period > Period Balance Current Period
   • Actual > Period > Period Balance Current Period Percent
7. Click Generate (F9). The wizard will create a report for you.
8. You can run the report to check the layout. Click Execute Report (Ctrl+R). You will be prompted to select a branch. If you leave this blank, the report will consolidate the figures for all branches.

   You could run this report several times, once for each branch. Instead, we will modify the report to iterate through all branches, automatically producing a report for each branch and a consolidated total report.

9. Add a band by clicking Insert Band (F4).
10. Set the Name of the new band to bandRestart. Set the band Height to 0.
11. Add a new Iterator by clicking Insert (F4).
12. Set the Band of the iterator to bandRestart. Leave all other details blank.
13. Select the Report, then by the BeforeReport code, click Open Code Editor (F2).
14. After the scForm = Nothing line, add the following code. This will find the first branch in the range, and determine whether the results will be consolidated:

    Dim tblCOBRANCH as Object
    tblCOBRANCH = OpenTable("COBRANCH")
    tblCOBRANCH.IndexName = "Branch"
    If Branch <> "" Then
      tblCOBranch.SetRange(Branch)
      tblCOBranch.First
      Consolidated = False
    Else
      tblCOBranch.First
      Branch = tblCOBranch.BranchCode
      Consolidated = True
    End If
    BranchName = tblCOBRANCH.BranchName

15. Delete the following lines of code, as we are retrieving the branch details in the code we added in the previous step:

      If Branch <> "" Then
        Data = CreateObject("Accredo.COBranchData")
        Data.FindExact(Branch)
        BranchName = Data.BranchName
      End If
      Data = Nothing

16. Save (F9) the changes to the code.
17. Select the Report, then by the AfterReport code, click Open Code Editor (F2).
18. Enter the following code. After the report is run, this code will re-run the report for the next Branch in the range, if one exists. The comments describe what the code does:

    tblCOBRANCH.Next
    If Not tblCOBRANCH.EOF then ' If there is another Branch in the range of branches selected, continue
      RestartIterators ' Restart the report from the first iterator
      bandRestart.ForceNewPage = True 'Restart on a new page
      bandRestart.ForceNewWorksheet = True 'Restart on a new worksheet
      Branch = tblCOBRANCH.BranchCode
      BranchName = tblCOBRANCH.BranchName
    
      Dim Iter as Object
      For I = 0 To Iterators.Count - 1 'Loop through each of the Iterators
        Iter = Iterators[I]
        ' Only modify iterators that select some accounts
        If Iter.BranchRange <> "" or Iter.DepartmentRange <> "" or Iter.Segment1Range <> "" or Iter.Segment2Range <> "" or Iter.Selection <> "" Then
          Iter.ExcludeNull = Not(IncludeNilBalances)
          Iter.BranchRange = Branch
        End If
      Next 'Repeat for the next Iterator
      Iter = Nothing
    
      If UntransferredBatches Then 'If there are untransferred batches, don't enable the bandUnTransferred
        bandUnTransferred.Enabled = False
      End If
      If UnpostedBatches Then 'If there are unposted batches, don't enable the bandUnTransferred
        bandUnposted.Enabled = False
      End If
    ElseIf Consolidated = True then 'If this is the last in the range, and the report is consolidated, continue
      RestartIterators ' Restart the report from the first iterator
      bandRestart.ForceNewPage = True 'Restart on a new page
      bandRestart.ForceNewWorksheet = True 'Restart on a new worksheet
      Branch = "" 'Set branch to blank, as the branches are consolidated
      BranchName = "Consolidated for All Branches"
    
      Dim Iter as Object
      For I = 0 To Iterators.Count - 1 'Loop through each of the Iterators
        Iter = Iterators[I]
        ' Only modify iterators that select some accounts
        If Iter.BranchRange <> "" or Iter.DepartmentRange <> "" or Iter.Segment1Range <> "" or Iter.Segment2Range <> "" or Iter.Selection <> "" Then
          Iter.ExcludeNull = Not(IncludeNilBalances)
          Iter.BranchRange = Branch
        End If
      Next 'Repeat for the next Iterator
      Iter = Nothing
    
      If UntransferredBatches Then 'If there are untransferred batches, don't enable the bandUnTransferred
        bandUnTransferred.Enabled = False
      End If
      If UnpostedBatches Then 'If there are unposted batches, don't enable the bandUnTransferred
        bandUnposted.Enabled = False
      End If
      Consolidated = False
    End If

19. Save (F9) the changes to the code.
20. You can now run the report. Click Execute Report (Ctrl+R). If you select a Branch, the report will run for the selected branch. If you leave the Branch selection clear, the report will run for each branch, and for the consolidated branches.

See also the GL Tutorial: Saturn Report to Iterate Departments.