GL Tutorial: Saturn Report to Iterate Departments

This tutorial is not available for Accredo Mercury.

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

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 Department - Selected

1. On the Bands tab, make the following selection:

• Retain Existing Bands - Clear

1. 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

1. Click Generate (F9). The wizard will create a report for you.
2. You can run the report to check the layout. Click Execute Report (Ctrl+R). You will be prompted to select a department. If you leave this blank, the report will consolidate the figures for all departments.

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

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

   Dim tblCODept as Object
   tblCODept = OpenTable("CODept")
   tblCODept.IndexName = "Department"
   If Department <> "" Then
     tblCODept.SetRange(Department)
     tblCODept.First
     Consolidated = False
   Else
     tblCODept.First
     Department = tblCODept.DepartmentCode
     Consolidated = True
   End If
   DepartmentName = tblCODept.DepartmentName

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

     If Department <> "" Then
       Data = CreateObject("Accredo.CODepartmentData")
       Data.FindExact(Department)
       DepartmentName = Data.DepartmentName
     End If
     Data = Nothing

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

    tblCODept.Next
    If Not tblCODept.EOF then ' If there is another Department in the range of departments 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
      Department = tblCODept.DepartmentCode
      DepartmentName = tblCODept.DepartmentName
    
      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.DepartmentRange = Department
        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
      Department = "" 'Set department to blank, as the departments are consolidated
      DepartmentName = "Consolidated for All Departments"
    
      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.DepartmentRange = Department
        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

13. Save (F9) the changes to the code.
14. You can now run the report. Click Execute Report (Ctrl+R). If you select a Department, the report will run for the selected department. If you leave the Department selection clear, the report will run for each department, and for the consolidated departments.

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