Menggunakan parameter bertingkat dalam laporan berpaginasi

Artikel ini menargetkan Anda sebagai penulis laporan yang merancang laporan terpaginasi Power BI. Artikel ini menyediakan skenario untuk merancang parameter bertingkat. Parameter bertingkat adalah parameter laporan dengan dependensi. Saat pengguna laporan memilih nilai parameter (atau nilai), nilai tersebut digunakan untuk mengatur nilai yang tersedia untuk parameter lain.

Catatan

Pengantar parameter bertingkat, dan cara mengonfigurasinya, tidak dibahas dalam artikel ini. Jika Anda tidak benar-benar terbiasa dengan parameter berskala, kami sarankan Anda terlebih dahulu membaca Tambahkan Parameter Berskala ke Laporan di Power BI Report Builder.

Skenario desain

Ada dua skenario desain untuk menggunakan parameter bertingkat. Keduanya dapat digunakan secara efektif untuk:

• Memfilter kumpulan besar item
• Menyajikan item yang relevan

Contoh database

Contoh yang disajikan dalam artikel ini didasarkan pada Azure SQL Database. Database merekam operasi penjualan, dan berisi berbagai tabel yang menyimpan penjual, produk, dan pesanan penjualan.

Tabel bernama Reseller menyimpan satu rekaman untuk setiap penjuak, dan berisi ribuan rekaman. Tabel Reseller memiliki kolom ini:

• ResellerCode (bilangan bulat)
• ResellerName
• Negara-Wilayah
• Negara Bagian-Provinsi
• Kota
• PostalCode

Ada tabel bernama Penjualan juga. Ini menyimpan catatan pesanan penjualan, dan memiliki hubungan kunci asing dengan tabel Reseller, di kolom ResellerCode.

Contoh persyaratan

Ada persyaratan untuk mengembangkan laporan Profil Penjual. Laporan harus dirancang untuk menampilkan informasi atas satu penjual. Pengguna laporan tidak boleh memasukkan kode penjual, karena mereka jarang mengingatnya.

Memfilter kumpulan besar item

Mari kita lihat tiga contoh untuk membantu Anda membatasi sekumpulan besar item yang tersedia, seperti penjual. Yaitu:

• Memfilter menurut kolom terkait
• Memfilter menurut kolom pengelompokan
• Filter menurut pola pencarian

Memfilter menurut kolom terkait

Dalam contoh ini, pengguna laporan berinteraksi dengan lima parameter laporan. Mereka harus memilih negara-wilayah, negara bagian-provinsi, kota, lalu kode pos. Parameter akhir kemudian mencantumkan penjual yang berada di lokasi geografis tersebut.

Berikut adalah cara Anda dapat mengembangkan parameter bertingkat:

1. Buat lima parameter laporan, yang diurutkan dalam urutan yang benar.

2. Buat himpunan data CountryRegion yang mengambil nilai negara-wilayah yang berbeda, menggunakan pernyataan kueri berikut:

   SELECT DISTINCT
     [Country-Region]
   FROM
     [Reseller]
   ORDER BY
     [Country-Region]
   

3. Buat himpunan data StateProvince yang mengambil nilai negara bagian-provinsi yang berbeda untuk wilayah negara yang dipilih, menggunakan pernyataan kueri berikut:

   SELECT DISTINCT
     [State-Province]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
   ORDER BY
     [State-Province]
   

4. Buat himpunan data Kota yang mengambil nilai kota yang berbeda untuk negara-wilayah dan negara bagian-provinsi yang dipilih, menggunakan pernyataan kueri berikut:

   SELECT DISTINCT
     [City]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
     AND [State-Province] = @StateProvince
   ORDER BY
     [City]
   

5. Lanjutkan pola ini untuk membuat himpunan data PostalCode.

6. Buat himpunan data Reseller untuk mengambil semua penjual terhadap nilai geografis yang dipilih, menggunakan pernyataan kueri berikut:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     [Country-Region] = @CountryRegion
     AND [State-Province] = @StateProvince
     AND [City] = @City
     AND [PostalCode] = @PostalCode
   ORDER BY
     [ResellerName]
   

7. Untuk setiap himpunan data kecuali yang pertama, petakan parameter kueri ke parameter laporan yang sesuai.

Catatan

Semua parameter kueri (diawali dengan simbol @) yang ditunjukkan dalam contoh ini dapat disematkan dalam pernyataan SELECT, atau diteruskan ke prosedur tersimpan.

Umumnya, prosedur tersimpan adalah pendekatan desain yang lebih baik. Ini karena rencana kuerinya di-cache untuk eksekusi yang lebih cepat, dan memungkinkan Anda mengembangkan logika yang lebih canggih, jika diperlukan. Namun, saat ini tidak didukung untuk sumber data relasional gateway, yaitu SQL Server, Oracle, dan Teradata.

Terakhir, Anda harus selalu memastikan indeks yang sesuai ada untuk mendukung pengambilan data yang efisien. Jika tidak, parameter laporan Anda bisa lambat diisi, dan database bisa menjadi terlalu terbebani. Untuk informasi selengkapnya tentang pengindeksan SQL Server, lihat Arsitektur Indeks dan Panduan Desain SQL Server.

Memfilter menurut kolom pengelompokan

Dalam contoh ini, pengguna laporan berinteraksi dengan parameter laporan untuk memilih huruf pertama penjual. Parameter kedua kemudian mencantumkan penjual saat namanya dimulai dengan huruf yang dipilih.

Berikut adalah cara Anda dapat mengembangkan parameter bertingkat:

1. Buat parameter laporan ReportGroup dan Reseller, yang diurutkan dalam urutan yang benar.

2. Buat himpunan data ReportGroup untuk mengambil huruf pertama yang digunakan oleh semua penjual, menggunakan pernyataan kueri berikut:

   SELECT DISTINCT
     LEFT([ResellerName], 1) AS [ReportGroup]
   FROM
     [Reseller]
   ORDER BY
     [ReportGroup]
   

3. Buat himpunan data Reseller untuk mengambil semua penjual yang dimulai dengan huruf yang dipilih, menggunakan pernyataan kueri berikut:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     LEFT([ResellerName], 1) = @ReportGroup
   ORDER BY
     [ResellerName]
   

4. Petakan parameter kueri himpunan data Reseller ke parameter laporan yang sesuai.

Menambahkan kolom pengelompokan ke tabel Reseller lebih efisien. Jika dipertahankan dan diindeks, akan memberikan hasil terbaik. Untuk informasi selengkapnya, lihat Menentukan Kolom Komputasi dalam Tabel.

ALTER TABLE [Reseller]
ADD [ReportGroup] AS LEFT([ResellerName], 1) PERSISTED

Teknik ini dapat memberikan potensi yang lebih besar. Pertimbangkan skrip berikut yang menambahkan kolom pengelompokan baru untuk memfilter penjual menurut pita huruf yang telah ditentukan sebelumnya. Ini juga membuat indeks untuk mengambil data yang diperlukan oleh parameter laporan secara efisien.

ALTER TABLE [Reseller]
ADD [ReportGroup2] AS CASE
  WHEN [ResellerName] LIKE '[A-C]%' THEN 'A-C'
  WHEN [ResellerName] LIKE '[D-H]%' THEN 'D-H'
  WHEN [ResellerName] LIKE '[I-M]%' THEN 'I-M'
  WHEN [ResellerName] LIKE '[N-S]%' THEN 'N-S'
  WHEN [ResellerName] LIKE '[T-Z]%' THEN 'T-Z'
  ELSE '[Other]'
END PERSISTED
GO

CREATE NONCLUSTERED INDEX [Reseller_ReportGroup2]
ON [Reseller] ([ReportGroup2]) INCLUDE ([ResellerCode], [ResellerName])
GO

Filter menurut pola pencarian

Dalam contoh ini, pengguna laporan berinteraksi dengan parameter laporan untuk memasukkan pola pencarian. Parameter kedua kemudian mencantumkan penjual saat nama berisi pola.

Berikut adalah cara Anda dapat mengembangkan parameter bertingkat:

1. Buat parameter Pencarian dan laporan Reseller, yang diurutkan dalam urutan yang benar.

2. Buat himpunan data Reseller untuk mengambil semua penjual yang berisi teks pencarian, menggunakan pernyataan kueri berikut:

   SELECT
     [ResellerCode],
     [ResellerName]
   FROM
     [Reseller]
   WHERE
     [ResellerName] LIKE '%' + @Search + '%'
   ORDER BY
     [ResellerName]
   

3. Petakan parameter kueri himpunan data Reseller ke parameter laporan yang sesuai.

Tip

Anda dapat meningkatkan desain ini untuk memberikan kontrol lebih bagi pengguna laporan. Ini memungkinkan mereka menentukan nilai pencocokan polanya sendiri. Misalnya, nilai pencarian "red%" akan memfilter ke penjual dengan nama yang dimulai dengan karakter "red".

Untuk informasi selengkapnya, lihat LIKE (Transact-SQL).

Berikut adalah cara Anda dapat membiarkan pengguna laporan menentukan pola mereka sendiri.

WHERE
  [ResellerName] LIKE @Search

Namun, banyak profesional non-database tidak tahu tentang persentase (%) karakter wildcard. Sebaliknya, mereka terbiasa dengan karakter tanda bintang (*). Dengan memodifikasi klausul WHERE, Anda dapat membiarkan mereka menggunakan karakter ini.

WHERE
  [ResellerName] LIKE SUBSTITUTE(@Search, '%', '*')

Menyajikan item yang relevan

Dalam skenario ini, Anda dapat menggunakan data fakta untuk membatasi nilai yang tersedia. Pengguna laporan akan disajikan dengan item tempat aktivitas telah direkam.

Dalam contoh ini, pengguna laporan berinteraksi dengan tiga parameter laporan. Dua yang pertama menetapkan rentang tanggal pesanan penjualan. Parameter ketiga kemudian mencantumkan penjual tempat pesanan telah dibuat selama periode waktu tersebut.

Berikut adalah cara Anda dapat mengembangkan parameter bertingkat:

1. Buat parameter laporan OrderDateStart, OrderDateEnd, dan Reseller, yang diurutkan dalam urutan yang benar.

2. Buat himpunan data Reseller untuk mengambil semua penjual yang melakukan pesanan dalam periode tanggal, menggunakan pernyataan kueri berikut:

   SELECT DISTINCT
     [r].[ResellerCode],
     [r].[ResellerName]
   FROM
     [Reseller] AS [r]
   INNER JOIN [Sales] AS [s]
     ON [s].[ResellerCode] = [r].[ResellerCode]
   WHERE
     [s].[OrderDate] >= @OrderDateStart
     AND [s].[OrderDate] < DATEADD(DAY, 1, @OrderDateEnd)
   ORDER BY
     [r].[ResellerName]
   

Rekomendasi

Sebaiknya susun laporan Anda dengan parameter bertingkat, jika memungkinkan. Karena parameter bertingkat:

• Memberikan pengalaman intuitif dan bermanfaat bagi pengguna laporan Anda
• Efisien, karena mengambil set nilai tersedia yang lebih kecil

Pastikan untuk mengoptimalkan sumber data Anda dengan:

• Menggunakan prosedur tersimpan, jika memungkinkan
• Menambahkan indeks yang sesuai untuk pengambilan data yang efisien
• Mewujudkan nilai kolom—dan bahkan baris—untuk menghindari evaluasi waktu kueri yang mahal

Konten terkait

Untuk informasi selengkapnya tentang dokumen resmi ini, lihat sumber daya berikut:

• Parameter laporan dalam Power BI Report Builder
• Menambahkan Parameter Berskala ke Laporan (Pembuat Laporan)
• Pertanyaan? Coba tanyakan kepada Komunitas Power BI
• Ada saran? Sumbang ide untuk meningkatkan Power BI