Membuat Laporan Budget

Secara umum, budget ditampilkan dalam bentuk trend yang terdiri dari kolom kode rekening beserta nama, setelah itu berturut-turut adalah jumlah budget tiap bulan dengan disertai jumlah total budget. Namun untuk menampilkan bentuk laporan yang berupa trend di Access tidaklah sesederhana itu karena laporan yang disediakan sifatnya statis, sedangkan budget yang kita buat sifatnya dinamis. Untuk menyiasati hal itu, maka kita perlu melakukan semacam manipulasi data sehingga Access bisa menampilkan laporan budget yang dinamis, sesuai dengan keinginan kita.

Gambar 1

Laporan trend budget yang akan kita buat bisa ditampilkan di form maupun di report. Untuk menampilkannya, kita harus membuat form dari Blank Form, yang akan disimpan dengan nama frmBudgetReport, lalu isikan form control berupa range untuk periode, range untuk kode derivarif1, tombol proses serta subform untuk menampilkan hasilnya. Kita dapat mengkopi form kontrol yang ada di frmNeracaLajur untuk lebih mudahnya. Oleh karena itu, bukalah frmNeracaLajur, lalu kopilah kontrol berikut ini ke frmBudgetReport:

1. Text box LoginPgn dan logout
2. Kotak option group/frame yang mempunyai label Caption=Periode dan isi yang ada di dalamnya, yaitu text box drTglTransaksi, sdTglTransaksi, combo box PeriodeAkuntansi, text box keterangan untuk combo box, text box txtPeriodeAkuntansi.
3. Combo box drDeriv1, sdDeriv1, text box drDeriv1Nama, sdDeriv1Nama

Setelah mengkopi control dari frmNeracaLajur, tambahkan tombol perintah dengan properti Caption dan Name sama dengan Proses serta subform control dengan properti Name= frmBudgetReportSubform serta Source Object yang dikosongkan. Design form lengkap untuk frmBudgetReport dapat kita lihat pada Gambar 1.

Di dalam subform frmBudgetReportSubform, kita akan melakukan trik. Namun, terlebih dahulu, salinlah kode VBA di bawah ini ke dalam Form_frmBudgetReport.

Option Compare Database
Private Sub drDeriv1_Click()
  Me.sdDeriv1.Requery
End Sub
Private Function HapusBudgetQuery()
  HapusObjekYgTidakPerlu "qryBudgetTrend1"
  HapusObjekYgTidakPerlu "qryBudgetTrend2"
End Function
Private Sub Form_Close()
  Form_frmMenus.Visible = True
  HapusBudgetQuery
End Sub
Private Sub Proses_Click()
Dim intPeriode As Integer
  If Me.drDeriv1 > Me.sdDeriv1 Then
    MsgBox "Kriteria dari deriv 1 tidak boleh lebih besar dari sampai deriv 1"
    Me.drDeriv1.SetFocus
    Exit Sub
  End If
  If Format(Me.drTglTransaksi, "yyyymm") > Format(Me.sdTgl Transaksi, "yyyymm") Then
    MsgBox "Periode akuntansi dari bulan tidak boleh lebih besar dari sampai bulan"
    Me.drTglTransaksi.SetFocus
    Exit Sub
  End If
  Me.frmBudgetReportSubform.SourceObject = ""
  intPeriode = (Year(Me.drTglTransaksi) * 12 + Month(Me.drTglTransaksi))
  strCriteriaDR = Format(Me.drTglTransaksi, "yyyy") & Format(Me.drTglTransaksi, "mm")
  strCriteriaSD = Format(Me.sdTglTransaksi, "yyyy") & Format(Me.sdTglTransaksi, "mm")
  strSqla = "SELECT [tahun]*12+[bulan]- " & intPeriode & "+1 AS Periode1, Tahun, Bulan, Periode, " _
          & "KodeRek, Deriv1, Deriv2, JumlahBudget FROM qryBudget " _
          & "WHERE Periode Between " & strCriteriaDR & " and " & strCriteriaSD _
          & " AND " & "Deriv1 Between '" & Me.drDeriv1 & "' and '" & Me.sdDeriv1 & "';"
  BuatQuery "qryBudgetTrend1", strSqla
  strSqla = "TRANSFORM Sum(JumlahBudget) AS SumOfJumlahBudget SELECT KodeRek, Deriv1, Deriv2, " _
          & "Sum(JumlahBudget) AS [Total Of JumlahBudget] FROM qryBudgetTrend1 " _
          & "GROUP BY KodeRek, Deriv1, Deriv2 PIVOT Periode1 IN (1,2,3,4,5,6,7,8,9,10,11,12);"
  BuatQuery "qryBudgetTrend2", strSqla
  Me.frmBudgetReportSubform.SourceObject = "frmBudgetReport Subform"
  Me.frmBudgetReportSubform.Requery
End Sub
Private Sub Form_Open(Cancel As Integer)
  Me.Caption = "Laporan Budget Trend Bulanan " & Nz(IdPerusahaan("Nama"), "")
  Me.frmBudgetReportSubform.SourceObject = ""
  If Not IsNull(Me.LoginPgn) Then Me.logout.Visible = True
End Sub
Private Sub txtPeriodeAkuntansi_Click()
  Me.drTglTransaksi = Format(Me.PeriodeAkuntansi.[Column](0), Me.drTglTransaksi.Format)
  Me.sdTglTransaksi = Format(Me.PeriodeAkuntansi.[Column](1), Me.sdTglTransaksi.Format)
End Sub

Membuat Subform frmBudgetReportSubform

Pada dasarnya, subform untuk laporan budget ini sama dengan subform lain yang telah kita buat. Perbedaannya terletak pada judul kolom yang bisa kita ubah sesuai dengan kriteria pada text box drTglTransaksi di frmBudget Report. Kalau kita mengubah bulan yang ada di kriteria itu, maka judul kolom untuk budget per bulan juga akan berubah.

Subform frmBudgetReport Subform sebenarnya adalah tampilan crosstab query yang dibuat melalui kode VBA di atas, yaitu melalui perintah:

strSqla = "TRANSFORM Sum (JumlahBudget) AS SumOf JumlahBudget SELECT KodeRek, Deriv1, Deriv2, " _
        & "Sum(JumlahBudget) AS [Total Of JumlahBudget] FROM qryBudgetTrend1 " _
        & "GROUP BY KodeRek, Deriv1, Deriv2 PIVOT Periode1 IN (1,2,3,4,5,6,7,8,9,10,11,12);"
BuatQuery "qryBudgetTrend2", strSqla

Query di atas akan membuat kolom untuk bulan menjadi 12. Dengan query inilah kita akan melakukan trik. Bulan pertama bisa Januari 2013, Februari 2013,  atau April 2013 sekalipun dan dapat diakhiri pada bulan dalam tahun yang sama (Desember 2013) atau berbeda (Januari 2014 dan Maret 2014), tergantung dari kriteria yang kita masukkan di frmBudgetReport. Crosstab query itu lalu disimpan dengan nama qryBudgetTrend2 yang kemudian menjadi record source dari frmBudgetReportSubform. Kalau kita membuat form melalui Form Wizard, dengan Tables/Queries dipilih qryBudgetTrend2, dan layout yang dipilih adalah datasheet, maka tampilan Design view akan seperti Gambar 2. Selanjutnya, ubahlah nama label yang mempunyai Caption 1 sampai dengan 12 dengan nama Lbl1, Lbl2, dan seterusnya, sampai terakhir Lbl12, seperti pada gambar itu.

Gambar 2

Untuk mengubah nama label sesuai dengan nama bulan yang ada dalam kriteria, salinlah event procedure berikut ini pada properti form On Open:

Private Sub Form_Open(Cancel As Integer)
  Dim dtTgl As Variant
  Dim i, n As Integer
  Dim ctlName As String
  dtTgl = Form_frmBudgetReport.drTglTransaksi
  n = -1
  For i = 1 To 12
    n = n + 1
    ctName = "Lbl" & CStr(i)
    If i = 1 Then
      Me.Controls(ctName).Caption = Format(dtTgl, "mmm yy")
    Else
      Me.Controls(ctName).Caption = Format(DateAdd("m", n, dtTgl), "mmm yy")
    End If
  Next i
End Sub

Gambar 3

Laporan budget yang telah kita buat di form dapat juga dibuat di report. Record source yang kita gunakan pada report itu juga sama, yaitu qryBudgetTrend2. Kita dapat mengkopi bagian form header yang ada di frmBudgetReport ke page header dan isikan bagian kriteria dalam report itu dengan formula yang terhubung dengan form. Label yang ada di frmBudgetReportSubform juga dikopi ke page header di report yang kita buat. Jangan lupa untuk mengubah nama label seperti di frmBudgetReportSubform, lalu isikan juga kode VBA di atas untuk Private Sub Report_Open(Cancel As Integer).

Agar report budget mudah dimengerti, kita juga dapat membuat grup berdasarkan kode Deriv1 yang terdiri dari dua yaitu Deriv1 header dan Deriv1 footer. Bagian header untuk memberi subjudul, sedangkan bagian footer untuk menjumlahkan subtotal setiap deriv1. Dengan demikian, kita bisa mengetahui berapa jumlah budget untuk masing-masing item dalam deriv1. Simpanlah report itu dengan nama rptBudgetReport. Report design dapat dilihat di Gambar 4, sedangkan report design dapat kita lihat pada Gambar 5. Perhatikan bahwa label bulan seperti pada Gambar 2 diletakkan di bagian page header.

Gambar 4

Gambar 5