Pivot Table (3 - Pivot field)

---

Muatan :

• Kilasan tentang pivot field
• Menata field pada Row Labels atau Column Labels
• Menata field pada Values (value field)

---

Kilasan

Pivot field adalah kolom data source yang bisa ditampilkan atau sedang ditampilkan dalam pivot table. Pemanfaatan pivot table pada umumnya adalah dengan menata setiap field yang dibutuhkan untuk membentuk suatu summary report tertentu. Penataan tersebut antara lain berupa pengaturan (setting), filter, penyusunan grup, pembuatan field baru melalui suatu formulasi tertentu (calculated item), pembuatan field nilai ukur (calculated field), dan sebagainya.



Dengan karakteristik pivot table yang tergantung pada data source yang disimpan dalam pivot cache, maka dari sebuah pivot cache bisa dibuat berbagai pivot table dengan susunan dan penataan field yang berbeda-beda sesuai kebutuhan. File untuk mencoba seluruh yang dibahas kali ini dapat diunduh melalui link yang ada pada bagian File(s).

Gambaran singkat pivot table yang akan menjadi bahan bahasan kali ini adalah seperti gambar berikut :

---

Menata field pada Row Labels atau Column Labels

Setiap field yang diletakkan pada sisi Row Labels maupun Column Labels dapat diatur dalam beberapa hal seperti akan menampilkan subtital atau tidak, mengubah nama field, mengatur number format untuk field yang memiliki item-item bertype numerik, dan sebagainya.
Menampilkan atau menyembunyikan Subtotal
Setiap field yang diletakkan pada sisi Row Label maupun Column Label selain field buatan pivot table yang bernama Values dapat dimenampilkan nilai subtotal setiap item dari field tersebut. Untuk menampilkan atau menyembunyikan subtotal tersebut adalah dengan cara :

klik kanan nama field -> pilih Subtotal blabla

ketika hal ini dilakukan berulang-ulang, maka akan tampak bahwa subtotal setiap item dalam field tersebut tampil atau tersembunyi. Ketika menyembunyikan subtotal, sebenarnya terjadi pengaturan Subtotal menjadi None. Sedang pada saat menampilkan subtotal dengan cara ini, sebenarnya terjadi pengaturan Subtotal menjadi Automatic, yaitu menggunakan jenis agregat yang dipilih pada setiap measures-nya (yang diletakkan pada bagian Values).

Jadi, ketika sebuah measures, misal pada gambar diatas adalah nilai_usd di-summarize menggunakan fungsi agregat Sum (menjadi Sum of nilai_USD), maka proses penampilan Subtotal dengan cara ini akan selalu menampilkan nilai Sum dari nilai_usd. Bisa jadi pada measure lainnya menggunakan fungsi agregat yang berbeda dan akan ditampilkan berupa summarize menggunakan fungsi agregat dari measure tersebut.

Membuat grup data
Pada sebuah field yang diletakkan disisi Row Label atau Column Label yang berisi item-item ber-datatype selain text dapat dibentuk fields baru sebagai grup datanya. Pada gambar diatas, field yang memiliki item-item ber-datatype selain text adalah field bernama periode yang diletakkan pada sisi Column Label. Pada field periode bisa dibentuk grup data. Karena field periode ber-datatype datetime, maka bisa dibentuk grup data seperti Months, Quarters, Years, dan sebagainya.

Misalkan saja pada field periode akan dibentuk grup data Years dan Months. Caranya adalah :

klik kanan field periode -> pilih Group -> muncul dialog window seperti gambar

• Bagian Auto yang berisi Starting at dan Ending at bisa diisi manual sesuai kebutuhan. Kali ini, biarkan mengikkuti hasil perkiraan pivot table terhadap item-item periode data.
• Bagian By yang berisi part sebuah data ber-type datetime. Karena setiap item periode data mulai berbeda nilainya pada level bulan, maka part yang bisa dipilih adalah mulai dari Months sampai Years. Jika field terkait mulai berbeda setiap itemnya sampai pada sisi nilai menitnya, maka pilihannya bisa mulai dari Minutes sampai Years.
  
  Kali ini, field periode akan dipilih untuk Months dan Years saja, sedangkan yang Quarters tidak dipilih.
• Penataan grup data diakhiri dengan menekan tombol OK

Hasil dari proses pembuatan grup data adalah munculnya beberapa fields buatan pivot table yang menunjukkan setiap grup dari field yang digrup selain grup terkecilnya. Pada contoh pengaturan grup diatas, yaitu menyusun grup Months dan Years, yang muncul hanyalah field baru bernama Years karena Months adalah grup terkecil dari proses pembuatan grup pada field periode. Field baru ini dapat diperlakukan seperti fields yang dibawa dari data source.

Grup data yang telah terbentuk dapat dibuang dengan cara :

klik kanan nama field -> pilih Ungroup

Field Settings
Sebuah field bisa ditata secara lebih detil melalui Field Settings. Caranya adalah :

klik kanan nama field -> pilih Field Settings

Misalkan akan mengatur field bernama produk (lihat gambar pivot table diatas), maka klik kanan field produk dan pilih Field Settings. Akan muncul sebuah dialog window seperti gambar berikut :

Pada gambar diatas ada 3 bagian, yaitu :

1. Custom Name
   Bagian ini adalah untuk memberi nama baru pada field tersebut. Pada umumnya, nama kolom pada tabel data source belum berupa nama kolom yang sesuai kebutuhan tampilan. Custom Name menjadi sarana untuk membuat pivot table bisa menampikan nama-nama kolom yang sesuai kebutuhan tampilan. Isi Custom Name tidak boleh sama (case insensitive) dengan nama field bawaan dari tabel data source.
2. tab Subtotals & Filters
   Bagian ini (lihat gambar diatas) adalah ruang pengaturan Subtotals setiap item dalam field tersebut dan mengatur tindakan terhadap filter per item. Secara singkat dapat dijabarkan sebagai berikut :
   • Subtotals
     Opsi yang ada dalam part Subtotals adalah :
     • Automatic
       Menamplkan Subtotal berdasar fungsi agregat yang digunakan setiap measure field (yang diletakkan dibagian Values).
     • None
       Menyembunyikan Subtotals
     • Custom
       Memilih fungsi-fungsi agregat (bisa lebih dari satu) yang akan ditampilkan. Jadi, ketika setiap measure field (yang ada di bagian Values) menggunakan fungsi agregat tertentu, maka field ini bisa melakukan komputasi subtotal dengan fungsi agregat yang lain yang berbeda dengan yang digunakan oleh setiap field measure yang ada dalam pivot table. Subtotal dengan opsi Custom ini akan hilang ketika field dikenai aksi menyembunyikan atau menampilkan subtotal dengan cara yang sudah dibahas diatas (klik kanan nama field -> pilih Subtotal blabla).
       
       Misalnya pada pivot table yang digunakan pada bahasan ini (lihat gambar pertama dalam bahasan ini) diatur pada field 'produk' untuk diberi Subtotals Custom berupa Count dan Max (tidak memilih Sum) seperti gambar dibawah ini.
       
       
       Ketika tombol OK ditekan, maka pivot table akan tampak seperti gambar berikut ini.
       
       
   • Filter
     Bagian ini hanya berisi sebuah opsi bernama 'Include new items in manual filter'. Manual filter berarti item-item yang ditampilkan adalah pilihan user dengan cara mencentang item yang akan ditampilkan saja dan menghilangkan centang pada item yang tidak akan ditampilkan. Ketika opsi ini dicentang, maka setiap ada penambahan item baru pada field tersebut, maka item baru tersebut akan ditampilkan.
     
     Cerita singkat berikut ini adalah ilustrasi tentang proses pecntengan opsi 'Include new items in manual filter'
     1. Ada sebuah data source yang telah diberi nama range yang dirujuk secara dinamis. Artinya, setiap ada penambahan record pada area tersebut, maka nama range merujuk sampai ke record baru yang ditambahkan. Kemudian sebuah pivot table dibuat dengan data source berupa nama range yang telah dirujuk secara dinamis tersebut. Setiap kali ada penambahan record baru pada data source dan pivot table di-refresh, maka pivot table akan merujuk ke tabel data source secara utuh sampai dengan record baru yang telah ditambahkan. Tabel data source beserta pivot table-nya akan tampak seperti gambar berikut ini :
        
        
     2. Pivot table di-filter secara manual dengan cara mengklik segitiga kecil pada field kode, kemudian menghilangkan centang dari item berbunyi '(Select All)' dan dilanjutkan dengan mencentang item berbunyi 'B' saja. Setelah di OK, maka pivot table hanya akan menampilkan item berbunyi 'B' saja. Proses ini dapat dilihat dalam gambar dibawah, dimana sisi kiri adalah saat memilih item 'B' saja dan sisi kanan adalah tampilan pivot table setelah tombol OK saat proses manual filter ditekan.
        
        
     3. Field kode diatur dalam Field Settings pada tab Subtotals & Filter dengan mencentang opsi 'Include new items in manual filter' seperti pada gambar berikut ini.
        
        
     4. Pada tabel data source terjadi penambahan record untuk kode 'X'. Kemudian pivot table di-refresh. Data source yang telah bertambah record-nya beserya pivot table setelah di-refresh akan tampak seperti gambar berikut ini.
        
        
     5. Pada field kode, item baru berbunyi 'X' yang baru saja ditambahkan dalam tabel data source, akan ditampilkan dalam pivot table. Filter manual akan berubah, dari yang tadinya hanya tercentang item berbunyi 'B' saja menjadi ada 2 buah item tercentang, yaitu item berbunyi 'B' dan 'X'.
3. tab Layout & Print
   Bagian ini adalah ruang untuk menata letak item-item dari field terpilih relatif terhadap field yang menjadi pendetilan (drilldown atau breakdown-nya [yang ada di sebelah kanan field terpilih]). Selain itu, bagian ini juga menjadi penataan letak antar item dan ketetuan untuk menampilkan seluruh item yang dimiliki field tersebut untuk setiap item dari field yang didetilkan (yang ada disebelah kirinya). Secara singkat dapat dijabarkan sebagai berikut :
   
   
   • Layout
     Bagian ini memiliki 2 pilihan pengaturan field ketika disertai fields pendetilan yang dapat dipilih salah satunya. Pilihan-pilihan tersebut adalah (dipilih salah satu)
     
     • Show item labels in outline form
       Pilihan ini akan membuat setiap item dalam field memiliki sebuah baris nilai data. Pada field yang terletak disisi paling kanan (terbawah dalam area Row Labels atau area Column Labels) tidak akan menampakkan perbedaan karena tidak memiliki field pendetil lagi disisi kanannya. Contohnya adalah seperti gambar diatas yang merupakan gambar tab Layout & Print untuk field negara. Jika pilihan ini dipilih untuk field produk, maka item pertama dari field negara akan diletakkan pada baris yang baru (turun satu baris), sehingga setiap item produk akan memiliki satu baris khusus untuk item produk tersebut saja.
       
       Pilihan ini memiliki 2 (dua) opsi yang dapat dipilih (dicentang) bila diperlukan, yaitu :
       
       1. Display labels from the next field in the same column (compact form)
          Jika opsi ini dicentang, maka setiap item dari field pendetil pertama (field yang ada tepat dikanannya) akan diletakkan pada kolom yang sama dengan field terpilih. Artinya, setiap item field akan diikuti (pada baris berikutnya) dengan item-item dari field pendetil pertama. Jadi dalam sebuah kolom akan berisi item-item dari kedua field, yaitu dari field terpilih (yang diatur) dan dari field pendetil pertama (yang tepat ada dikanannya).
          
          Jika opsi ini tidak dicentang, maka susunannya akan mengikuti bentuk seperti saat dipilih layout berupa outline. Artinya, setiap item dari field terpilih akan memiliki sebuah baris khusus nilai data dan dalam sebuah kolom hanya berisi item-item dari field terpilih saja.
       2. Display subtotals at the top of each group
          Jika opsi ini dicentang, maka nilai-nilai subtotal akan diletakkan pada baris item tersebut. Jika opsi ini tidak dicentang, maka letak nilai subtotal adalah pada baris baru sebelum item selanjutnya milik field terpilih.
     • Show item labels in tabular form
       Pilihan ini akan membuat item pertama dari field pendetil pertama diletakkan pada baris item dari field terpilih. Hal ini dapat dilihat seperti gambar pertama dalam pembahasan kali ini.
     Selain 2 (dua) pilihan diatas, bagian ini juga memiliki 2 (dua) opsi yang berkaitan dengan penataan field terpilih, yaitu :
     
     • Insert blank line after each item label
       Jika opsi ini dicentang, maka akan ditambahkan sebuah baris kosong diantara dua item dalam field terpilih.
     • Show items with no data
       Jika opsi ini dicentang, maka seluruh item yang ada di field terpilih akan ditampilkan walaupun tidak memiliki data. Hal ini khusus bagi fields pendetil atau selain field pertama (terkiri) dalam area Row Labels maupun area Column Labels. Jadi, setiap item label dari field yang didetilkan (yang ada dikiri field terpilih) akan didetilkan oleh field terpilih dengan seluruh item dari field terpilih.
       
       Untuk memudahkan pemahaman, maka hal ini akan tampak terlihat ketika yang diatur adalah field negara. Jika opsi ini pada field negara dicentang, maka seluruh item (nama negara) yang ada dalam field negara akan ditampilkan di setiap item produk. Nilai data untuk negara yang tidak memiliki data produk dalam data source akan diberi nilai kosong (NULL atau Blanks).
   • Print
     Bagian ini akan tampak hasil pengaturannya ketika pivot table dicetak. Bagian ini hanya memiliki sebuah opsi, yaitu 'Insert page break after each item'. Ketika opsi ini dicentang, maka setiap lembar cetak hanya akan berisi satu item dari field terpilih.

Selain 3 (tiga) bagian tersebut, masih ada sebuah pengaturan yang dikhususkan (tombolnya akan muncul) untuk field yang seluruh itemnya memiliki datatype numerik atau datetime. Pengaturan tersebut adalah 'Number Format'. Pada pivot table yang digunakan dalam pembahasan ini, tombol pengaturan number format akan muncul ketika mengaktifkan dialog Field Settings untuk field bernama periode. Cara mengatur formatnya akan seperti mengatur format cells pada tab Number. Pengaturan number format akan berlaku pada field terpilih saja.

Misalkan field setting pada field periode diaktifkan dan tombol 'Number Format' ditekan, akan muncul dialog format number. Ketika dipilih Custom dan pada textbox yang ada disisi kanan dialog diberi format berbunyi MMMM-YYYY maka setiap item periode yang ber-datatype datetime akan menampilkan format nama bulan disertai tahunnya seperti January-2011 dan sebagainya.

---

Menata field pada Values (value field)

Setiap data field (yang diletakkan pada area Values) dapat ditata pada beberapa halnya melalui dialog window Value Field Settings. Dialog window ini dapat diaktifkan dengan cara :

klik kanan sebuah nilai dalam pivot table milik value field tersebut -> pilih Value Field Setting

Pada gambar diatas ada 4 bagian utama, yaitu :

1. Custom Name
   Bagian ini adalah untuk memberi nama baru pada value field tersebut. Pada umumnya, nama kolom pada tabel data source belum berupa nama kolom yang sesuai kebutuhan tampilan dan nama value field akan memiliki format dengan didahului oleh jenis komputasi agregat yang digunakan oleh value field tersebut. Misalnya adalah Sum of nilai_USD.
   
   Custom Name menjadi sarana untuk membuat pivot table bisa menampikan nama-nama kolom yang sesuai kebutuhan tampilan. Isi Custom Name tidak boleh sama (case insensitive) dengan nama field bawaan dari tabel data source. Pastikan juga bahwa nama value field mencerminkan jenis komputasi agregat yang dipilih untuk value field tersebut, seperti Total Nilai (USD) karena field nilai_USD dikenai jenis agregat Sum.
2. Number Format
   Pada setiap value field pasti akan muncul tombol Number Format karena isi dari value field pasti ber-datatype numerik akibat komputasi agregat terpilih. Pengaturan number format dilakukan seperti halnya memformat cells pada tab Number.
3. tab Summarize by
   Bagian ini hanya berisi Summarize value field by yang menjadi area pemilihan jenis agregat untuk value field tersebut. Pemilihan dilakukan dengan klik salah satu jenis agregat yang ada dalam daftar. Jenis agregat yang ada dalam daftar antara lain seperti Sum, Count, Max, dan sebagainya.
   
   Setiap value field hanya bisa menggunakan satu jenis agregat. Untuk menggunakan jenis agregat lainnya, maka field yang sama harus diletakkan ulang (drag ulang) ke area Values untuk membentuk value field baru. Kemudian value field baru tersebut ditata jenis agregatnya.
4. tab Show values as
   
   
   Bagian ini adalah area untuk memilih jenis komputasi lanjutan dari hasil agregat pada value field tersebut. Pengaturan dilakukan dengan memilih salah satu item dari combobox yang tersedia. Secara default, item yang terpilih dalam combobox Show values as adalah item Normal yang berarti tidak melakukan komputasi apapun alias menampilkan apa adanya hasil agregat.
   
   Beberapa item akan mengaktifkan daftar pilihan Base field dan Base item. Daftar Base field berisi nama-nama field yang akan menjadi field utama dasar pemrosesan yang dipilih dalam combobox. Daftar Base item berisi item-item dari field yang dipilih pada Base field. Pada daftar Base item juga terdapat pilihan yang merujuk relatif ke item Base field sebelumnya yang bernama (previous) dan relatif ke item Base field berikutnya yang bernama (Next)
   
   Sebagai contoh pada value field Sum of nilai_USD :
   
   • combobox Show values as dipilih Difference From
     Artinya, proses komputasi lanjutan setelah didapatkan hasil agregat Sum adalah menghitung perbedaan setiap nilai hasil dengan nilai hasil lainnya. Berarti dibutuhkan informasi akan dihitung relatif terhadap nilai yang mana. Oleh sebab itu, pemilihan ini akan mengaktifkan daftar Base field dan Base item.
   • daftar Base field dipilih field periode
     Artinya, komputasi Difference From (perbedaan antara dua nilai hasil agregat) akan dilakukan antar item dalam field periode. Tentu saja masih dibutuhkan informasi tentang item priode yang harus digunakan. Oleh sebab itu harus diadakan pemilihan item dari field periode yang akan menjadi nilai relatif perbedaan (difference).
   • daftar Base item dipilih (Previous)
     Artinya, komputasi Difference From akan dilakukan terhadap item milik field periode yang ada disebelum atau (Previous) item periode yang diproses. Jika saat ini sedang memproses hasil agregat pada item periode bernilai 2011-03-01, maka Difference From akan menghitung hail agregat item 2011-03-01 dengan item sebelumnya (dikirinya), yang misalnya bernilai 2011-02-01 ketika field periode terurut Ascending. Jika hasil agregat diperiode 2011-03-01 bernilai 1260 dan hasil agregat diperiode 2011-02-01 bernilai 6310, maka value field akan berisi nilai -5050.
   • bentuk penataan pada dialog window value field settings di tab Show values as akan tampak seperti gambar dibawah ini
     
     
   • setelah tombol OK ditekan, maka tampilan pivot table akan menjadi seperti gambar dibawah ini
     
     

---

Closing :

Pengaturan pada masing-masing field dapat membentuk berbagai susunan pivot table. Bisa jadi salah satu bentuknya sesuai atau setidaknya mendekati kebutuhan. Penataan masing-masing field menjadi salah satu bagian yang penting dalam penataan pivot table agar bisa memenuhi kebutuhan.

---

File(s) :

Pivot Table 3 - Pivot Field pada sheet 3_pvtTRN

---

Coretan terkait :

• Pivot Table (1 - Kenalan)
• Pivot Table (2 - Menata Data Source)
• Pivot Table (4 - Sorts & Filters)

---