›

Paling populer-sistem perdagangan Online-komoditi-trading-guide Teknik-forex-sebenar-v2-password Apa-yang-terjadi-pilihan-ku-kuota-jika-perusahaanku-diakuisisi Online-trading-card-game-for-android Signaux-de-trading-option-binaire-gratuit

Semua instruksi Langkah demi Langkah adalah dokumen PDF. Jika Anda belum memiliki pembaca PDF yang sudah terinstal di komputer Anda, berkonsultasilah dengan staf pendukung komputer di departemen Anda. Pengantar SAP Duke Basics Guide Logging Ke R3 SAP R3 Window Navigating R3 Menggunakan Kode Transaksi Menu Pengguna Bagaimana Saya Menemukan Kode Transaksi untuk Transaksi Tertentu Bagaimana Saya Menggunakan Kode Kode Transaksi Kode Beberapa Pesan Mengubah Pesan ke Tampilan dalam Dialog Kotak Apa Jenis Pesan yang Mengatur Profil Pengguna Bagaimana Saya Memasukkan Informasi Pengguna Saya Bagaimana Saya Mengatur Default Pengguna Saya (Format tanggal amp printer) Bagaimana Saya Mengatur Default Parameter (Nilai Default Field) Bagaimana Saya Menemukan Parameter ID (PID) Untuk Field Menampilkan Nama Teknis Folder Favorit Pembalakan dari R3 Fields dan Matchcode Searches Bagaimana Saya Memasukkan Beberapa Pilihan untuk sebuah Field Bagaimana Saya Melakukan Panduan Pencarian Matchcode untuk Menggunakan Tanda di Searches Tip dan Trik Bagaimana cara Menyembunyikan Taskbar Windows Bagaimana Apakah Saya Membuat Shortcut di Desktop Saya untuk Transaksi yang Sering Digunakan Bagaimana Saya Menggunakan Shortcut dari Desktop Saya Bagaimana Saya MemaksimalkanMinimalkan Jendela R3 Bagaimana Saya Menghentikan Transac (8220Hour Glass8221) Jika Diperlukan Suplemen untuk Pengguna Mac OS Bagaimana R3 Lihat dan Lakukan di MAC Bagaimana Saya Log Ke R3 di MAC Bagaimana Saya Mencetak Hardcopy (Layar) pada Mac Common SAP Language Accounts Payable Invoice Exception Workflow Nilai Pesanan Pembelian Melampaui Batas Harga Nilai Blok Melebihi Batas Workflow Kuantitas Blokir Item Workflow Tidak Sesuai Pemesanan Pembelian Workflow Lampiran untuk Alur Kerja Proses Piutang Usaha untuk Penelitian Sponsor Proses Piutang Piutang Usaha Program Sponsor Piutang Usaha Satu Baris Ringkasan Laporan (ZF109AR) Untuk menghasilkan laporan tentang Laporan Seleksi Program Sponsor Piutang Satu Jalur Ringkasan Ringkasan Untuk meninjau Program Sponsor: Laporan Ringkasan Satu Baris Untuk membekukan kolom untuk pengguliran horizontal Untuk mengurutkan dan subtotal oleh kolom Parent Untuk mengelas dan melihat rincian lebih lanjut untuk AR untuk satu Elemen WBS Proyek) Untuk melihat salinan faktur yang dikirim ke Sponsor dari Dokumen Tipe YI atau DR AR - Aged Debt Report (ZFAR003) Untuk membuat laporan pada layar seleksi AR-Aged Debt Report Untuk meninjau Laporan Utang Utang AR 8211 Untuk membekukan kolom untuk Tampilan Baris Pelanggan bergulir Horisontal (FBL5N) Untuk menambahkan bidang tambahan ke pemilihan (pilih dengan tujuh digit WBS Element Proyek) Untuk melihat catatan yang diberikan untuk faktur dengan Kode Koleksi yang ditambahkan di kolom Ekstra Teks Untuk melihat bagaimana pembayaran diterapkan ke faktur Untuk melihat informasi tambahan mengenai transaksi dari drill-down ke dokumen Untuk mengekspor Daftar Item Baris Pelanggan Petunjuk Bermanfaat Lampiran Koleksi Kode Terkait dengan Alat Anggaran Piutang Usaha Navigasi Umum Alat Anggaran Pengembangan Anggaran Anggaran Non-Jabatan (Non-Personil) Mengakses Tata Letak Non-Posisi dan Memasukkan Data Memasuki Data Anggaran di Tata Letak Buat Jalur Baru Masukkan Anggaran Baru oleh Unit dan Rate Change Spread Codes dan Manually Spread New Year Budget Masukkan TransferOffset untuk Alokasi Upload Non-Position Mengakses Upload Non-Position di t Alat Anggaran Menggunakan Template untuk Memasuki Posisi Pengunggahan Data Anggaran (Personil) Penganggaran Mengakses Tata Letak dan Pemuatan Data Menavigasi Tata Letak yang Didanai, Milik dan Siswa Memasuki Anggaran untuk Posisi Buat Posisi Baru Mengubah Gaji Tunggal Tambahkan Pendanaan Baru Menambah atau Mengubah SupplementsProvisions Salin Saat Ini ke Distribusi Biaya Baru Salin Semua Gaji yang Dikelola ke Tata Letak yang Dimiliki Ubah Posisi Data Master (Tata Letak yang Dimiliki) Upload Posisi yang Didanai Mengakses Posisi yang Didanai Upload di Alat Anggaran Menggunakan Template untuk Memasukkan Data Anggaran Mengunggah Posisi Milik Upload Mengakses Posisi Milik Upload di Alat Anggaran Menggunakan Template untuk Memasukkan Data Anggaran Mengupload Panduan Pelaporan Alat Anggaran Mengakses Laporan Tentang Alat Anggaran Melaporkan Langkah-langkah Umum untuk Mengakses Semua Laporan Menggunakan Panel Navigasi Terperinci Menggunakan Gb Garis Tertantang Laporan Berdasarkan Gudang Bisnis (BW) Memilih Kriteria dan Pelaksana SAP BW Berdasarkan Laporan Seleksi Varian untuk SAP BW Laporan Berbasis Permintaan Pilihan untuk Laporan Berbasis SAP Berbasis Pelaporan Mencetak Laporan SAP BW Mengekspor Laporan SAP BW Mempersonalisasikan Permintaan untuk Laporan SAP BW Menyegarkan Permintaan untuk Laporan Karakteristik SAP BW untuk Laporan Eksekusi SAP BW Berdasarkan Laporan Swapping View of the Report Menggunakan Karakteristik Menambahkan Karakteristik Menggunakan Bor dengan Menggunakan Fungsi Lain dalam Laporan Berbasis SAP BW Menggunakan Fungsi Kembali Menggunakan Fungsi Filter Menggunakan Fungsi Sortir Menggunakan Fungsi Hirarki Menampilkan Kolom untuk Informasi Tambahan (Bilangan, Names, dll.) Laporan Berbasis Web Langkah Untuk Memilih Data dan Melaksanakan Laporan Berbasis Web Menggunakan Fungsi Lain dalam Laporan Berbasis Web Ikhtisar Proyeksi yang Mengakses Proyeksi dalam Perangkat Anggaran Proyeksi Jatuh dan Musim Semi Memutuskan Proyeksi Data oleh Proyeksi BFR dengan Paket Menggunakan Filter Tambahan Filter Dana Grup Filter Filter Item Komitmen Memasuki Proyeksi Mendorong Masukkan atau Menyesuaikan Proyeksi Buat Jalur Baru Gunakan Unit dan Tarif untuk Memasuki Proyeksi Perubahan Transfer Offset Menyimpan dan Keluar Proyeksi Laporan Proyeksi Mengakses Laporan Proyeksi Langkah Umum 8211 Laporan Berbasis Bisnis (BW) Laporan Berbasis BW Tip dan Trik YE10 Tahun Akhir Musim Semi Musim Semi Proj. Pelaporan Dewan dengan Dana Lain Langkah Umum 8211 Laporan Berbasis Web Laporan Berbasis Webi Tip dan Trik SAP BW Upgrade 2015 ke BEx7 amp Panduan Transkripsi SAP Logon 740 Referensi Cepat 160 Revisi Terakhir: 04292015 Pengantar Panduan Gudang Bisnis (v7.0) untuk Duke University ( Kode Perusahaan 0010) Tentang SAP Business Warehouse (BW) di Duke Logging Ke SAP BW Alat Bantu Analisis BEx Analyzer v7.0 Toolbar BW Persyaratan dan Definisi Menggunakan Query yang Ada 8211 Fund Trial Balance (FTB) Cari dan Pilih Neraca Saldo Dana Query menggunakan InfoAreas Use Fungsi Temukan untuk Menemukan Kriteria Permintaan Masukan atau Memilih Kritik untuk Kueri Percobaan Kueri Permintaan Memeriksa Nilai Seleksi yang Dimasukkan Sebelum Melaksanakan Kueri Simpan Varian dari Nilai Seleksi yang Akan Digunakan Selanjutnya dan Simpan Langkah Gunakan atau Dapatkan Varian yang Disimpan dari Nilai Seleksi Jalankan Query dan Tinjau Hasil Menyimpan Permintaan Terformat Single ke Buku Kerja Menyimpan Hasil Query Terformat ke Buku Kerja di BW untuk Penggunaan Masa Depan Tip untuk Menyimpan Query Terformat sebagai Workbook Excel (Jaringan Lokal) Membuka Workbook di BW untuk Mengakses Permintaan Tersimpan di Tanggal yang Akan Lalu Mengubah Nilai Variabel Menyegarkan Buku Kerja yang Mengandung Satu Query Tertanam Tertanam Menu Konteks Dasar (Klik Kanan) Fungsi untuk Kueri dan Buku Kerja Sekilas tentang Fungsi Dasar Membatalkan Aksi atau Kembali Satu Langkah Navigasi ke Hasil Sebelumnya Hilangkan Deskripsi Teknis dari Objek Biaya Duke Memperluas Hirarki untuk Melihat Semua Tingkat dan Objek Biaya Duke Menonaktifkan Hirarki untuk Menunjukkan Daftar Lurus Objek Nilai Duke Filter dan Drill Down 8211 Contoh: Filter pada Satu Obyek Biaya dan Drill Down Dengan Item Komitmen (GL Acct.) Tambahkan Drill Down Menurut - Contoh: Tambah Drill Down Sesuai Item Komitmen (GL Account) Tukar Tampilan Hasil 8211 Contoh: Swap Duke Cost Objek Dengan Item Komitmen (GL Account) Query Properties: Menekan Baris atau Kolom yang Mengandung Kuota Nol Query Properties: Tambahkan Total (Hasil) Row jika Tidak Ditampilkan Query Proper Ikatan: Pindahkan Penempatan Kueri Baris Total Besar: Menampilkan atau Sembunyikan Kolom Permintaan Properti: Menekan Fungsi Tombol F Berulang yang Diulang Termasuk Drag dan Drop Ikhtisar Fungsi Tombol Filter Filter oleh BFR CodeOrg. Unit dari Tingkat Hirarki yang Lebih Tinggi melalui Saldo Tampilan Tombol Filter menurut Periode Sepanjang Laporan melalui Tombol Filter Menggunakan Drag dan Drop untuk Menjalankan Fungsi Swap melalui Tombol Filter Menggunakan Drag and Drop untuk Menambahkan Drill Down melalui Tombol Filter Nonaktifkan Fungsi Drag and Drop dari Tombol Filter jika Preferred Design Mode Toolbar: Menetapkan Pilihan untuk Query dan Workbook Ikhtisar dan Tip untuk Menggunakan Toolbar Design Mode (Baris Teratas) Hapus AutoFit untuk Mencegah Ukuran Kolom dari Menyesuaikan Setting Lebar Default Workbook: Pilihan Umum Fungsi Workbook untuk Variabel Nilai Fungsi Lanjutan untuk Kueri dan Workbook Beberapa Seleksi dan Mengimpor Nilai Variabel ke Layar Seleksi untuk Permintaan Segarkan Buku Kerja berisi Beberapa Query Tertanam Salin Query ke Lembar Kerja menggunakan Fungsi Copy Sheet (Mengganti Permintaan Salin di Versi Lama ) Daftar Panduan Pertanyaan yang Sering Digunakan untuk Koordinator Upaya Pengantar ECRT Untuk mengakses ECRT via DukeWork T O keluar Ikhtisar Sertifikasi ECRT dan Logout Tinjauan Sekilas Tinjauan Sertifikasi Sekilas 8211 Tinjauan Upaya Dasar 8211 Upaya Dasar dan Tambahan Memproses Pernyataan Upaya Untuk mengakses Pernyataan Upaya melalui Laman Rumah ECRT Untuk meninjau dan memproses Pernyataan Upaya Dasar Untuk Membuka Kembali Pernyataan Upaya untuk Pencarian Ulang Untuk Certifiers Processing Transfer Biaya Gaji via iForms Untuk Membuat Lembar Kerja Pengolah Biaya Penggajian Tugas Rekonsiliasi Penggajian Untuk mengakses Penyesuaian Gaji melalui Tugas Kelola Usaha Mengelola Koordinator Usaha Sekunder Untuk mengakses Org. Kode melalui Dashboard Kode Org Untuk mengendalikan Koordinator Upaya Sekunder8217s Mengelola Sertifikasi Upaya Mengelola Untuk menentukan apakah Staf Fakultas atau Staf yang Diperoleh memenuhi syarat sebagai Certifier Untuk memantau Status Sertifikasi di ECRT Untuk meninjau Certifiers for a Org. Kode Untuk menempatkan Certifier ditahan Untuk menetapkan Proxy ke Certifier Untuk menetapkan 8220Override8221 hak ke Org lain. Koordinator Upaya Utama Code8217 Meninjau Laporan ECRT Untuk mengakses laporan ECRT melalui Layar Awal Untuk melaksanakan Laporan Penggajian Untuk melaksanakan Laporan Ringkasan Penggajian Sertifikasi untuk melaksanakan Laporan Status Sertifikasi Untuk melaksanakan Laporan Ringkasan Status Sertifikasi Untuk menjalankan Laporan Tugas Tugas untuk melaksanakan Laporan Rekonsiliasi Penyesuaian Penggajian Meninjau Pemberitahuan Email Untuk memahami Pemberitahuan Triwulanan dan Pemberitahuan Email Total Upaya Profesional (TPE) Panduan untuk Koordinator Upaya Meninjau dan Menyetujui Total Upaya Profesional (TPE) Sekilas tentang Total Upaya Profesional di ECRT Untuk mengkonfirmasi Total Usaha Profesional (TPE) Meninjau TPE dari Pernyataan Upaya Fakultas untuk membuat atau mengkonfirmasi TPE untuk Fakultas yang tidak hadir Untuk meninjau dan menyetujui TPE untuk Fakultas Mengkaji Total Upaya Profesional (TPE) Laporan Untuk mengakses Panduan Upaya Dukungan TPE DUHS (ES-DUHS) untuk Upaya Koordinator Pengantar Ikhtisar ECRT Upaya S Mengangkat Sistem Kesehatan Universitas Duke (ES-DUHS) Pengenalan Umum ES-DUHS di ECRT Sekilas Proses ES-DUHS dalam Logika Sertifikasi Triwulanan ECRT Memeriksa Langkah Sertifikasi Upaya Tahunan yang dilakukan oleh Fakultas untuk sertifikasi kuartalan ES-DUHS Mengolah ES-DUHS Pernyataan Untuk mengisi kembali pernyataan usaha ES-DUHS untuk Fakultas Anda Untuk memproses konfirmasi kuartalan ES-DUHS Untuk memproses konfirmasi tahunan ES-DUHS Meninjau Laporan ECRT untuk ES-DUHS Untuk mengakses laporan ECRT Untuk meninjau Validasi Jam Kegiatan Klinis Triwulanan Laporan Untuk meninjau Validasi Jam Kegiatan Klinis Laporan Tahunan Untuk meninjau Laporan Lengkap Medicare Journal Entry Basics Guide Jurnal Entry Panduan Lanjutan Rekonsiliasi dan Panduan Dokumen Rekonsiliasi Transaksi Keuangan Rekonsiliasi Penuh vs. Penilaian Keuangan amp Management (FAM) Pentingnya Rekonsiliasi Transaksi Keuangan Melakukan sebuah Biaya Pendanaan Dokumentasi Rekonsiliasi Hibah (Penggajian) Amp Contracts Distinctions Tanggung Jawab dan Review Dokumen SAP Daftar Jenis Dokumen SAP (FI) Dokumen SAP Lainnya (CO, PS) Jenis Dokumen Yang Paling Umum Dalam Detil Lebih Banyak Menampilkan Dokumen via Laporan Drill Down Sebelum mengebor item dari laporan item baris Untuk mengebor Menurun pada item baris laporan Tahapan drill-down pada laporan Menampilkan Dokumen SAP (FI) 8211 FB03 Menampilkan File Terlampir untuk Dokumen Untuk mencetak salinan dokumen (dari Gambaran Dokumen 8211 Layar Display) Untuk melihat atau Cetak teks tambahan (catatan) untuk dokumen yang berlaku Entri Jurnal (SA, ZJ, ZB, AB, amp ZZ) Potongan informasi penting Lihat rincian tentang item entri jurnal tertentu Lihat teks tambahan (dokumentasi audit) jika berlaku Lihat informasi header Tentang keseluruhan dokumen akuntansi Tampilkan semua file terlampir (Word, Excel, dll.) Menampilkan ikhtisar persetujuan Workflow dari dokumen yang dikirim Vendor Invoice - NOT Reference PO (KN amp YD) Potongan informasi penting D Etermine jika Dokumen adalah dokumen asli atau dokumen pendukung Melihat informasi header untuk keseluruhan dokumen Melihat apakah ada cek dikeluarkan dan melihat informasi pembayaran Melihat lampiran faktur yang dipindai Faktur - Referensi PO (RN, RE, KG, YM, ZN, ZS, ZC, amp ZU) Potongan informasi penting Tentukan apakah Dokumen itu adalah Dokumen Asli atau Pendukung Lihat apakah Cek Diterbitkan dan Lihat Informasi Pembayaran Lihat Faktur yang Dipindai Lampiran Lihat Pesanan Pembelian Referensi untuk Item Faktur AP Periksa Permintaan Uang Kuliah (ZT amp ZQ) Potongan informasi penting Lihat apakah Cek Dikeluarkan dan Lihat Informasi Pembayaran Lihat Faktur yang Dipindai Lampiran Penelitian untuk InvoicesCheck Pembayaran Untuk melihat apakah cek dikeluarkan dan melihat informasi pembayaran Melihat lampiran faktur yang dipindai Lihat apakah faktur Telah dibayar menggunakan nomor Pesanan Pembelian Melihat apakah faktur telah dibayarkan menggunakan nomor faktur vendor Akuntansi Perjalanan: Karyawan amp Siswa dengan gaji (YT) ke Vi Ew Laporan Pelaporan Pelaporan Biaya dari Laporan Item Baris Potongan-potongan informasi penting lainnya Lihat apakah ada cek dikeluarkan dan tampilkan informasi pembayaran Lihat informasi header untuk keseluruhan dokumen dari layar manapun Pembayaran Perjalanan Lainnya (YU) Potongan informasi yang penting Lihat apakah sebuah Cek telah dikeluarkan dan menampilkan informasi pembayaran Melihat informasi header untuk seluruh dokumen Catatan Perekonomian Eksperimental (ZK) Potongan informasi penting Lihat apakah cek dikeluarkan dan menampilkan informasi pembayaran Lihat informasi header untuk keseluruhan dokumen Tagihan Piutang Usaha Dikirim ke Sponsor untuk Penelitian yang Disponsori (YI, DR, DG, dan DZ) Melihat salinan faktur yang dikirim ke Jenis Order Pembelian Pesanan Sponsor (YI atau DR) Menampilkan Order Pembelian di SAP (Nomor Dokumen Dikenal) 8211 Kode Transaksi ME23 Tampilkan Daftar Pesanan Pembelian di SAP (Nomor Dokumen Tidak Dikenal) 8211 Kode Transaksi ME2K Menampilkan Pesanan Pembelian dari Dokumen Faktur Mengacu sebuah PO Untuk Menampilkan Pesanan Pembelian dari Komitmen pada Laporan Kunci Potongan Informasi untuk Setiap Item Baris PO Cari Siapa yang Menciptakan Pesanan Pembelian Lihat Riwayat Pemesanan Pembelian (Dokumen Penerimaan Barang atau Faktur) Lihat Akun yang Dibebankan, Lokasi Pengiriman, dan Penerima Barang Lihat Informasi Vendor AddressPhone Lihat Permintaan Pembelian Referensi (HANYA KHUSUS) Permintaan Pembelian Jenis Permintaan Pembayaran Menampilkan Permintaan Pembelian (Nomor Dokumen Diketahui) Menampilkan Daftar Permintaan Pembelian (Nomor Dokumen Tidak Dikenal) Menampilkan Permintaan Pembelian dari Mengacu pada Purchase Order Menampilkan Permintaan Pembelian dari Komitmen pada Laporan Kunci Potongan Informasi Melihat Rincian Item Baris, seperti Harga per Item Lihat apakah Item Baris ini tunduk pada Release Temukan Siapa yang Menciptakan Permintaan Pembelian Lihat Akun yang Dibebankan, Lokasi Pengiriman, dan Dokumen Bahan Penerima Barang (WA amp WE) Potongan informasi penting Melihat biaya rinci oleh Pesanan Internal Pusat Biaya (Tata Kerja) Urutan Internal ActualPlanVariance Report Laporan Internal Order ActualPlanVariance Report (ZFR1G) Panduan Pelaporan Endowment Rincian Aktivitas Penggajian - Multiperiod (ZH333) Laporan Lembut Seminggu (ZH409) Pengaturan Gaji Duke University - DukeWork (Tab Kompensasi) Duke University Health System Pengaturan Gaji 8211 DukeWork (Tab Kompensasi DUHS) Manual Biaya Gaji Tranfer iForm Fakultas Alat Proyeksi dan Panduan Pelaporan Panduan Hibah Hibah Guru Mengumpulkan Panduan Pemeliharaan Data Master untuk Administrator Departemen Sekilas tentang Alat Mengakses Alat Menggunakan Alat Menyortir Data Dalam Kolom Mengekspor ke Excel Menggunakan Fungsi Mass Change Tool Grant Managers: Hibah Master Data Maintenance Keuangan Master Data Assignments Pusat Biaya Master Data Maintenance Guide untuk Administrator Departemen Sekilas tentang Alat Mengakses Alat Menggunakan Alat Menyortir Data Dalam Kolom Mengekspor ke Excel Dengan menggunakan fungsi Mass Change Tool Mass Perubahan Fin Ance Master Data Assignments untuk Cost Center dan Grants Advanced Sponsored Projects Pelaporan Pilihan Lanjutan - Daftar Ikhtisar Definisi Proyek (ZFR1AM) Ikhtisar Ringkasan Definisi Daftar Daftar Proyek: Mengakses Daftar dan Memilih Kriteria Dasar Menggunakan Variant Projectlist untuk Memilih Daftar oleh Lainnya Tersedia Spon. Proj. Bidang Menggunakan Variant Projectlist untuk Memilih Daftar Kode Induk atau Komposit Menyimpan dan Menggunakan Seleksi Pilihan Anda Variant Variasi untuk Penggunaan Masa Depan Mengubah Tata Letak Daftar Proyek Setelah Ditampilkan Sortir Daftar oleh Proyek Tanggal Berakhir untuk Mengidentifikasi Proyek yang akan Ditutup Mengekspor Proyek Daftar ke Excel Keluar dari Daftar Proyek Pengingat Opsi Lanjutan - ProjectWBS: Inception to Date wPlan (ZFR1E) Ikhtisar Laporan Gagasan Proyek sampai Tanggal (ITD): Mengakses Laporan ITD Proyek dan Memilih Kriteria Dasar Memilih Laporan oleh Proyek Begin and Tanggal Akhir Memilih Laporan oleh Nomor SPS untuk Membantu Menemukan Proyek yang Baru Dibuat Memilih Laporan untuk Proyek yang Membutuhkan Formulir CAS Non-Payroll Memilih Laporan untuk Proyek yang Membutuhkan Formulir Penggajian CAS Memilih Laporan untuk Meninjau Biaya pada Akun GL yang Dapat Diperiksa Ringkasan Departemen Pemulihan Biaya Tidak Langsung ( ZF111) Ikhtisar Ringkasan Departemen Pemulihan Biaya Tidak Langsung Mengakses Laporan dan Memilih Kriteria Dasar Menganalisis Data dalam Laporan Laporan Detail Pengalihan Biaya Non-Gaji (ZF420) Gambaran Umum dari Laporan Detil NKT (Non-Gaji) Rincian Mengakses Laporan dan Memilih Kriteria Dasar Menganalisis Data dalam Laporan Biaya Bukan Gaji Laporan Tren Transfer (ZF421) Ikhtisar Laporan Tren Non-Gaji Biaya Transfer (Transparansi Biaya Non-Gaji) Mengakses Laporan dan Memilih Kriteria Dasar Menganalisis Data untuk Angka yang Lebih Tinggi Kode BFR Menganalisis Data untuk Tingkat Terendah Kode BFR Menganalisis Data untuk Tingkat Dokumen Objek Objek Sponsor Penyelidik Pelacak Sampul Akibat Sebelumnya (ZF600) Ikhtisar Pelacak Penelitian yang Disponsori dari Penutupan Duke sebelumnya Mengakses Laporan dan Memilih Kriteria Dasar Meninjau Pelacak Laporan Tutup Akibat Sebelumnya Membekukan Kolom untuk Gulungan Horisontal Menggunakan Drill Down to Lihat Rincian Lebih Lanjut untuk Satu Elemen WBS (Proyek) AR 8211 Laporan Hutang Berumur Tua (ZFAR003) Sekilas tentang AR 8211 Laporan Utang Berumur Mengakses Laporan a Nd Memilih Kriteria Dasar Meninjau Ulang AR 8211 Laporan Hutang Berumur Membekukan Kolom untuk Tampilan Item Baris Gulir Horizontal Scrolling (FBL5N) Ikhtisar Transaksi Item Baris Pelanggan Mengakses Laporan dan Memilih Kriteria Dasar Menambahkan Bidang Ekstra ke Seleksi (Seperti WBS ElementProject) Meninjau Hasil Melihat Catatan Teks Tambahan Ditambahkan Untuk Faktur dengan Kode Koleksi yang Melihat Bagaimana Pembayaran Diterapkan ke Faktur Melihat Data Tambahan dari Drill-down ke Dokumen Mengekspor Daftar Item Jalur Pelanggan Menggunakan Fungsi Lain dalam Laporan Pelaporan Manajemen Aset Laporan Pelaporan Sistem Kesehatan Rumah Sakit dan Kesehatan Laporan Kriteria Seleksi: Persyaratan Utama Menampilkan Kelompok Pusat Biaya (KSH3) Menampilkan Kelompok Elemen Biaya (KAH3) R3 Pelaporan Objek Biaya Pusat Laba: Rencanakan Perbandingan Aktual CurrCumm. (Anggaran Varian ZFR1J) Pusat Biaya: Satu Baris Ringkasan MoYTD (ZFR1Q) Pusat Biaya: Perbandingan Kumulatif Saat Ini (ZFR1F) Pusat Biaya: Laporan AktualPlanCommitments (ZFR1R) DUHS Multi Tahun oleh Proyek w Stat Posting (ZFR1N) R3 Item Line Pelaporan Biaya Pusat: Menampilkan Item Garis Aktual Aktual (KSB1) Isu Material (Pusat Biaya: Menampilkan Item Garis Aktual KSB1) Pusat Biaya: Item baris komitmen (KSB2) Laporan Trend R3 Pusat Biaya: Tren Aktual oleh Pusat Biaya w Stat (ZFR1V) R3 Laporan Organisasi Pandangan Akuntansi Kegiatan Penggajian (ZH223) Rincian Aktivitas Penggajian (ZH262) Mencetak Laporan Mengekspor Laporan ke Excel University Pelaporan-Manajer Bisnis Pertanyaan yang sering diajukan mengenai BOB adalah: Bagaimana saya bisa menghitung jumlah hari kerja antara dua tanggal Jawaban yang paling mudah adalah menggunakan Tabel kalender dengan bendera untuk hari kerja karena itu mengurus semuanya dengan mudah. Anda bahkan bisa menandai liburan bersama dengan hari-hari akhir pekan dan membuat hasilnya lebih bermanfaat. Tapi anggaplah Anda tidak memiliki meja kalender. Misalkan Anda tidak peduli dengan liburan, Anda hanya ingin menghitung jumlah hari Senin 8211 hari Jumat antara rentang tanggal. Bisakah Anda melakukan itu Jawabannya akan datang jika Anda membaca sisa posting. Catatan: Teknik ini ditampilkan di depan umum pada hari Senin di konferensi GBN. Saya telah menulis posting blog ini beberapa waktu yang lalu namun menunggu untuk melepaskannya sampai setelah presentasi disampaikan. Presentasi telah diupload ke blog saya dan tersedia untuk diunduh dari halaman konferensi. Fungsi Web Intelligence Diperlukan Rumus ini akan menggunakan fungsi berikut, jadi saya akan mengenalkan dan menggambarkan masing-masing sedikit di sini. DaysBetween () Nilai input: dua nilai tanggal Nilai output: bilangan bulat (tidak ada pecahan) yang menunjukkan jumlah hari antara dua tanggal DayNumberOfWeek () Nilai input: tanggal Nilai output: Jumlah dari 1 sampai 7 yang menunjukkan hari dalam seminggu (1 Senin, 7 Minggu) Truncate () Nilai input: ekspresi numerik atau nilai, jumlah tempat desimal yang diinginkan Nilai output: nilai numerik terpotong pada jumlah desimal tertentu, misalnya Truncate (3.14159, 2) 3.14 Truncate tidak berputar, jadi Truncate ( 5.9 0) mengembalikan 5 yang merupakan apa yang dibutuhkan untuk solusi ini Mod () Nilai input: dua angka Nilai output: sisa nilai pertama dibagi dengan yang kedua Contoh: Mod (10 2) 0 dan Mod (11 2) 1 dan Mod (15 7) 1 Substr () Nilai input: string, posisi awal, panjang Nilai output: bagian dari string Contoh: Substr (82171234824221) 821628242 ToNumber () Nilai input: representasi string dari bilangan yang valid Nilai output: bilangan Contoh: ToNumber (821728242) 2 Menghitung Minggu Salah satu langkah pertama di solvi Ng apapun perhitungannya adalah mencari jalan pintas. Jelas akan ada sejumlah nilai masukan yang tidak terbatas untuk perhitungan saya jadi saya perlu beberapa cara untuk menghindari terlalu banyak pilihan yang mungkin. Karena ini saya akan mulai dengan menghitung jumlah minggu antara dua hari. Lihatlah kalender di sisi kanan blog saya dan coba eksperimen. Ternyata tidak peduli pada hari mana Anda memulai, jika Anda mencalonkan diri selama tujuh hari, Anda pasti akan mendapatkan tepat dua tanggal akhir pekan. Tidak lebih, tidak kurang, tepatnya dua. Cobalah dan lihatlah. Mulai hari Minggu dan hitung maju tujuh hari dan apa yang Anda dapatkan Tepat pada suatu hari Minggu dan satu hari Sabtu dua hari akhir pekan. Mulai hari Senin dan hitung maju tujuh hari dan hal yang sama terjadi. Mulai pada hari Selasa pukul 18:30 pada hari Jumat, atau bahkan hari Sabtu pukul 30.00 dan rentang tujuh hari selalu memiliki tanggal dua minggu yang tepat. Saya bisa memperpanjang ini ke kelipatan tujuh hari lagi. Jika saya memiliki 14 hari saya mendapatkan 4 hari akhir pekan. Dengan 21 hari saya memiliki 6 hari akhir pekan. Jika saya berkembang biak dengan lima, bukan dua, saya mendapatkan jumlah hari kerja di kisaran ini, oleh karena itu saya akan memulai perhitungan saya dengan menggunakan pola ini. Inilah awal dari rumus: Truncate (DaysBetween (Tanggal Akhir Tanggal Berakhir) 7 0) Biarkan saya menghancurkannya. Pertama fungsi DaysBetween () digunakan untuk menentukan jumlah hari antara tanggal mulai dan akhir untuk rentang saya. Selanjutnya saya membagi jumlah hari dengan tujuh yang akan menghasilkan hasil desimal. Misalnya, 15 7 2.14. Akhirnya saya menggunakan fungsi Truncate () untuk menghapus desimal, yang mengubah 2.14 menjadi 2. Itu memberi saya jumlah minggu penuh antara dua hari saya. Berikut tabel yang menunjukkan hasil untuk beberapa rentang hari sampel. Jumlah keseluruhan minggu Berdasarkan kesimpulan yang saya buat sebelumnya, saya bisa menggunakan jumlah minggu dan bertambah lima kali untuk mendapatkan jumlah hari minggu yang termasuk dalam kisaran. Untuk mendapatkan angka tersebut saya membuat satu modifikasi kecil pada formula yang disajikan di atas saya mengalikan hasilnya dengan lima. Hasilnya: Truncate (DaysBetween (Tanggal Akhir Tanggal Berakhir) 7 0) 5 Yang harus saya tinggalkan hanyalah sisa hari yang tidak dapat dinyalakan seminggu penuh. Bagian desimal dari 2,14 adalah 0,14 yang mewakili satu hari ekstra dalam rentang saya. Untuk mengetahui apakah menghitung hari itu atau tidak, saya harus tahu namanya (atau nomornya) pada hari dimana rentang saya dimulai. Berapa Banyak Hari Ekstra Ada fungsi yang disebut DayNumberOfWeek (). Ini nomor Senin sebagai 1 dan Minggu sebagai 7 sehingga saya akan berpura-pura bahwa kalender saya terlihat seperti ini: M Apa yang saya butuhkan selanjutnya adalah jumlah hari yang tidak seminggu penuh. Saya menggunakan perintah Truncate () sebelumnya untuk mendapatkan jumlah minggu penuh. Ini menghapus bagian desimal dari hasil pembagian. Yang saya butuhkan sekarang adalah bagian desimal, dan fungsi Mod () dapat digunakan untuk mendapatkannya. Fungsi ini melakukan pembagian dan mengembalikan sisanya. Argumen pertama adalah jumlah hari, dan argumen kedua akan tujuh karena ada tujuh hari dalam seminggu. Hasilnya akan menjadi angka yang berkisar dari nol (0) sampai enam (6) karena itu adalah satu-satunya nilai sisa yang mungkin saat dibagi dengan tujuh. Sebagai contoh, Mod (15 7) 1. 15 7 adalah 2 dengan 1 left over, oleh karena itu 1 adalah nilai kembalian dari fungsi Mod (). Untuk menerapkannya pada masalah saya saat ini: 15 hari terdiri dari dua minggu penuh dan satu hari ekstra. Dua puluh hari akan menjadi Mod (20 7) yang menghasilkan 6 hari tersisa. Mod (21 7) adalah nol karena ada tepat tiga minggu dan tidak ada hari ekstra. Dapatkah saya mengumpulkan dua informasi ini bersama-sama dengan mudah mendapatkan jumlah hari dalam seminggu, dan saya tahu berapa hari ekstra yang ada. Saya harus bisa melakukan beberapa math8230 96 Responses to 8220Menghitung Hari Kerja Antara Dua Hari Melalui Fungsi Laporan8221 Terima kasih banyak telah berbagi ini, Dave. Anda menghemat banyak jam banyak BOBbers. Mengikuti link anda (dari bob), semuanya jelas dan profesional di blog2 anda Dave, I8217m kipas anda. Terima kasih telah berbagi ini. Hanya sebuah klarifikasi yang bekerja pada garis ini, bagaimana kita bisa menentukan dan membuat kalender kita sendiri untuk webi, (kalender yang datang sebagai pemetik tanggal adalah default), yaitu applet, dari mana kita memilih tanggal pada prompt di webi di infoview. Ini mungkin pikiran keras. Bingung apakah kita bisa melakukan itu? BTW, tanggal dinamismu Saran8230 selalu membantu saya. Terima kasih banyak untuk info itu. Dengan rasa hormat, saya MEMILIKI 2 KOLOM, SATU FO THEM 8220SKU number8221, yang lain adalah pendapatan 8220Sales8221. INI MEMBAWA KU PENGHASILAN PENJUALAN BESAR DARI HASIL DARI 30 DOLLAR KE 5425687, DAN SETUJU AKU MENDAPATKAN 260 NILAI. APA YANG SAYA INGIN LAKUKAN ADALAH AKU INGIN MEMBUAT 3 KELOMPOK (A, B, C,), DARI 0 sampai 20000 HARUS DIBAYAR DI KELOMPOK A. Dari 20000 sampai 40000 HARUS PUT DI GROUP B. DARI 40000 KE 60000 HARUS DILAKUKAN PUT DI KELOMPOK C.. . . Dll. BSICALLY, I WANNA MEMBUAT KELOMPOKAN BASE PADA RUANG 20000. DAN RUANG TERAKHIR ANTARA 5420000 DAN 5440000. SAYA INGIN MENULIS FUNGSI DALAM RANGKA UNTUK MELAKUKANNYA. BISA ANDA HARAP MEMBANTU SAYA PADA INI (Bagaimana saya bisa melakukan itu) Hai, Sedat. Pertanyaan ini tidak benar-benar berhubungan dengan posting ini sehingga saya tidak akan menjawab di sini. Jika Anda melihat fungsi Lantai () (atau mencari di BOB), Anda mungkin mendapatkan beberapa gagasan. Terima kasih. IPS17m tidak dapat mengerjakan ini untuk tanggal berikut: Mulai: 4210 Akhir: 4910 Rumus menghitung 10 hari kerja daripada 5. Mulai: 41910 Akhir: 42610 Rumus menghitung 6 hari kerja dan bukan 5. Ini berlaku baik untuk saya. Banyak tanggal lain yang terjadi hanya dengan yang salah ini. Setiap saran Sebenarnya, saya menyadari sekarang bahwa formula tersebut tidak bekerja untuk tanggal saya yang harus menunjukkan 5 hari kerja. Contoh lain: Mulai: 9102007 Akhir: 9172007 Saya melakukan beberapa penelitian tambahan, dan sepertinya formula tersebut mungkin tidak bekerja saat hari kerja merupakan kelipatan dari 5. Sebagai contoh, untuk tanggal-tanggal ini: Start: 452010 Akhir: 4192010 Perhitungan harus kembali 10 Hari kerja agak, ia kembali 11 hari kerja. Saya menggunakan rumus ini untuk beberapa laporan saya hanya untuk mengetahui sekarang bahwa ada sebuah masalah. Setiap cahaya tentang bagaimana hal ini dapat diatasi akan sangat dihargai. Pada saat ini, saya berpikir untuk mengecualikan paruh akhir dari rumus jika babak pertama sama dengan 5 meskipun ini memerlukan pengujian lebih lanjut. Paruh pertama: (Truncate (DaysBetween (Tanggal Berakhir Tanggal) 7 0) 5) Setengah setengah: ToNumber (Substr (822112345551234445123334512223451112345001234501234558243 ((DayNumberOfWeek (Tanggal Mulai) -1) 7) Mod (DaysBetween (Tanggal Mulai Tanggal) 7) 1 1 )) Hai, dan terima kasih atas masukan anda Saya pikir saya telah menguji kasus yang tepat tetapi akan menyiapkan sesuatu dan melihat apakah saya dapat menciptakan masalah ini. Dave terima kasih Anda menyelamatkan saya berjam-jam. Aku hanya perlu membuat beberapa perubahan kecil. Untuk (dan untuk. Untuk Objek Bisnis Ini bekerja seperti pesona. Saya tertawa saat melihat solusi ini sebagai, a) sangat pandai, dan b) kikuk (IMO). Bukannya mengatakan kikuk adalah buruk 8211 jika mencapai hasil maka bagusnya 8211 tapi saya yakin ada cara yang lebih sederhana dan lebih elegan untuk mencapainya. Saya harus mengakui kekalahan, setidaknya 8211 saya pikir saya memiliki solusi yang berhasil dan tidak menggunakan indekslookup, namun menggunakan 4 Ifs (yang jumlahnya kurang dari 49). Untuk menyederhanakan sedikit, I8217ve menciptakan 2 variabel yang disebut Hari Akhir amp Hari Akhir: Hari Mulai HariNumberOfWeek (Tanggal Mulai) Hari Akhir Hari Hari Akhir untuk Ditambah, di mana Hari untuk Tambah Mod (Hari Antara (Tanggal Mulai Tanggal) Tanggal Mulai adalah nomor Antara 1 amp 7, Days to Add adalah angka antara 0 amp 6, jadi End Day adalah angka antara 1 amp 13. Nomor akhir hari gt 7 berarti akhir hari ke minggu depan. Kita perlu mengatur jumlah hari untuk menambahkan tergantung pada apakah hari akhir pekan jatuh dengan rentang Hari Awal sampai Akhir Hari. Jika kita memikirkan hari kerja mengenai kisaran yang mungkin seperti angka hari dari 1 10, maka secara efektif kita ingin memetakan hari sebenarnya 1 13 sampai hari kerja 1 10, dengan mengabaikan atau menghapus akhir pekan. Pada hari kerja, hari ke 6 adalah hari senin demi hari 1 dan seterusnya. Jika kita mengecek jumlah hari pada Hari Awal dan Hari Akhir kita dapat melakukan penyesuaian yang sesuai dengan jumlah hari kerja, sebagai berikut: Hari Mulai Disesuaikan Jika Hari Mulai gt 6 maka 6 hari lain mulai Hari Akhir sedikit lebih sulit karena bisa menjadi actual day number from 1 to 13: Adjusted End Day If End Day 6, then 5, else If End Day 13 then 10 else If End Day gt 7 then End Day -2, else End Day End If End If End If The Adjusted Days to Add Adjusted End Day Adjusted Start Day 1 (The 1 is needed because the BetweenDays() function actually returns 1 less than the number of workings days BetweenDays() is the number to add to Start Date to result in End Date so does not include the first day). The full formula is: (Truncate(DaysBetween(Start Date End Date) 7 0) 5) If(End Day6 5 If(End Day13 10 If(End Daygt7 End Day-2 End Day))) 8211 If(Start Daygt6 6 Start Day) 1 You can substitute in the formulae for Start Day and End Day if you want to express the whole thing in just in terms of Start Date and End Date. Not a particulalrly elegant solution, but the best I could come up with without using a lookup. Hope this post is not too long. What is actually vexing me currently is how to use a Calendar of workingnon-working days. I agree that that must be a better solution, and I have a system with a Calendar of non-working days, but I cannot work out how to use it Im sure its simple Maybe Ill put that in another post. Hi, no1jjv, I took a more detailed look at your bug report. It may be that we have a difference in how Business Days are defined. In one of your examples you suggest that the range from 452010 to 4192010 returns 11 business days but should return 10. I disagree. 452010 was a Monday. 4192010 is also a Monday. Monday 8211 Monday is 11 days, not 10. Monday 8211 Friday is five days. The same is true of your other question. Basically the start day is included, otherwise 452010 to 452010 would return zero days, and in my definition is should return 1 business day. Thanks Dave. This detailed explanation was quite useful for me. excellent Dave, nice post which will be really useful in realtime. Keep it up. and send if u have any docs related to Bo-Designer and webi reports Your formula was nice and is it really working8230..can you clarfy me82308230.is it working without any issues for Business days in all cases like random dates823082308230..please clarify me82308230. i have to apply for my reports, please help me sir8230823082308230any one of our group members can also explain me is it working82308230the dave8217s formula very first one in this page.. Hi, the formula has worked for all test cases that I checked. thanx for sharing the above solution. I need your help in working out the process time with business hours. I am using webi and dont have access to the universe so unable to upload a table for the dates start date 8211 04032011 16:00 end date 8211 07032011 09:30 office hours is from 08:00 8211 18:00 office is closed weekends from the above scenario, the process time should be 3:30 hours what is the best formula please I tested your formula to calculate working days excluding sat and sun Here is my Input : start date. 04022010 End date. 04092010 I should get output 5 days but its giving me 10 days. can you help me on this I8217m sorry I should get 6 days but its giving 10 days. Everyone, I been tring for days to get this solution to work. I too have a requirement to calculate the time between two date, exclude week-end, holidays, and only counting time during business hours 7:00an 8211 5:00pm. I8217m tring to do this BOXI 3.1 WEBI. I first tried to do it in the universe (designer) and couldn8217t figure out how to build formulas there. The data source is a SQL Server db with dates stored as unixi time (sec from 1970). Any additional help would be greatly appreciated. I8217m going crazy8230. Thank you for this elegant and simple solution. I never thought of using an array-like structure. You saved me many hours of work Heres a fairly simple function which returns the 49 diffent extra-day values. Its in Basic (sorry) but that does have the advantge that if you paste it into an Excel module, it becomes available as a custom function which you can then use to check that the return values are as required. D1 is the Day Number in Daves 49-possible-results table. D2 is the number of Days to Add. Public Function eDays(D1, D2) As Integer D2 (D1 D2) Mod 7 If D1 lt 5 Or D2 5 Then D1 5 If D2 gt 4 Then D2 4 eDays D2 - D1 1 End If If D2 lt D1 Then eDays eDays 5 Sorry. I messed up the code tags. Public Function eDays(D1, D2) As Integer D2 (D1 D2) Mod 7 If D1 lt 5 Or D2 lt 5 Then If D1 gt 5 Then D1 5 If D2 gt 4 Then D2 4 eDays D2 - D1 1 If D2 lt D1 Then eDays eDays 5 Hello Dave, thanks a lot 8211 you saved me about quite some time trying to figure this one out Now I only have to convince the customer that trying to include bank holidays is a BAD idea, especially in a world-wide company Hi all, Just a simple fix for those who are getting 10 days instead of 5 days. Let me give you a quick example Any date ranges that do not have a remainder: ie: jan 1jan 8 jan 1jan 15 jan 1jan 21 The reason why you are getting 10 days instead of 5 days is because in the formula, we are still adding the 8220tonumber(substr..8221 section when in reality, there is no remainder. Therefore a simple fix is to do this 1. Create a variable 8220X8221 for 8220ToNumber(Substr(822112345551234445123334512223451112345001234501234558243 ((DayNumberOfWeek(Start Date)-1)7)Mod(DaysBetween(Start DateEnd Date)7) 1))8221 2. And on the final formula you can do if(Mod(DaysBetween(Start DateEnd Date)7) 0) then 0 else X That will solve your problem of showing 10 instead of 5 work days Hope that helps David Also Thanks Dave for the awesome solution The array idea is brilliant Ive found a workaround to count the holydays too8230 Ill give an example with the e-fashion universe which, I think, will get things easier. First of all, create an extra query filter with Store Name. This must be a prompt. Make the filter like Store Nam less than Dates (the name of the prompt). For the prompts values enter the value xxxdate1date2date38230 Since all soters names begins with an 8220e8221, your query will always run. Create a report variable (lets say holydays) that holds the SubStr(UserResponse(8221Dates8221)5Length(UserResponse(8221Dates8221)). You now got a array of dates separated by 82208221. Do get the individual dates, what i did was create a table with five columns :Year,Month,CountRunningCount(Month), a variable called HollydaysInBusinessDays, and another to test if the HollydaysInBusinessDays is between InitialDate and FinalDate (lets say Sum) My date formatting is 8220ddMMyyyy8221. HollydaysInBusinessDays If(ToDate((SubStr(Dates(Count-1)11110)8221ddMMyyyy8221)lt5ToDate((SubStr(Dates(Count-1)11110)quotddMMyyyyquot)). Now, you got in each row of the table, the dates entered as hollydays. Test the dates against the indiviual HollydaysInBusinessDays, like If(InitialDateltHollydaysInBusinessDaysandHollydaysInBusinessDaysltFinalDate10). Finally sum up the column Sum, and youll have the number of HollyDays between InitialDate and FinalDate. Sorry for the long post, and hope this will help. I l ove all the information here and found a simpler format for the variable to just count the weekdays between two dates without having to use Mod or Truncate: Looks very simple. Thanks for sharing. David, be carefull with your calculation. Using Week() Instead of ModTruncate is fine while having dates within the same year. But this doesn8217t work as soon as you use dates of different years. I am using SAP Business Objects WEBI XIR2 for basic report building. I have taken some of the above suggestions and help build a days between counting calendar. In my scenario8230our client starts their fiscal year in the 32nd week of our year. Now problem I built a routine that will return The Client8217s Fiscal week number based on Today8217s date. (Our client has not paid for the extra calendaring others have mentioned). Is there a way to build a Range of Weeks An define that range8230 For example my client is trying to build moving ranges of weeks that were 3-5 weeks ago, 7-9 weeks ago, 15-23 weeks ago8230.and have these weeks floating8230.so every new week the ranges would change. Hi Dave, I saw so many comments on your research. Could you please help me out to put date minus formula I want to minus dates and for last specific dates I want numbers in Item Count field and the value should be dynamic. for example I put Item CountWhere(COB DateMax(COB Date))-Item CountWhere(COB DateMax(COB Date). I am not getting correct result its just adding the number of different dates. I have tried using the simple formula suggested by David. (Week(Closed)-Week(Opened))5DayNumberOfWeek(Closed)-DayNumberOfWeek(Opened) unfortunately it gave unexpected results. On analysis I found that Week(ToDate(822101012012 12:0182438221ddMMyyyy hh:mm8221) was giving output as 52 instead of 1, but Week(ToDate(822103012012 12:0182438221ddMMyyyy hh:mm8221) was giving correct output as 1. Can someone explain why npn, I believe it8217s because Web Intelligence treats Sunday as day 7, meaning it8217s the end of the week. So Jan 1, 2012, is the end of week 52 for the end of 2011. If you use the more complex version of the formula, I believe you8217ll get the expected result. Hi, Thanks for the detail steps. here i am unable to compare two date values. I have two data Providers. based on the date provided by the user i t has to select the data from data Providers. For Example: if the user provides 24-Oct-2011. Than it has to consider the data from 1st Data Provider till 24-Oct-2011 and 25 Onwards it has to consider second Data Provider. ( in second data Provider it extract the data only for Prompt Date. so that values has to repeat for the 25-Oct-2011 to 31-Oct-2011). I have used If and Days Between and tried all the options. it will be great helpful for me. from past 3 Days am working on the same case.. Not working correctly date from 1 feb 2012 till 31 march 2012. formula gives 42, however, it should be 43 And how to adjust the array my business week is from monday till saterday. (excluding sunday) Tried to modify the number array, but doesn8217t work.. Maruthi, I don8217t think you need DaysBetween() you simply need to check to see if a date is greater than or less than a provided date. It sounds like you8217re making things more complicated than you need8230 or you8217re not providing all of the problem definition. You can retrieve the user8217s entry for the date via the UserResponse() function, and then convert it to a date using ToDate(), and then simply compare the two dates. You don8217t need to think about weekends in your scenario, so it8217s not really related to this post at all, at least as far as I can tell. I created some sample data in a spreadsheet and used it to test the difference between Feb 1 and Mar 31 and got 43 days. This was in Web Intelligence 3.1. Perhaps you8217re in a version that changes how one of the date functions works Here8217s the output I got from my test: (Edit) I replicated the same in BI 4 and it also works. You may need to check your formula, or perhaps your data, and see where its failing. Indeed, apologies, it8217s working, got also 43 days. i did somewhere an error but now fine. How to exclude only the sundays I already modified the array (delete the last string of sunday), but my result is not ok. You won8217t delete anything. You have to update the array with the proper counts. The array as it stands eliminates Saturday and Sunday from the count you need to refill the array with the proper numbers to fit your requirements. The description on how I generated the array to ignore two days is in the post. You just need to adjust the process so that you count Saturday and ignore only Sunday. I8217m not going to do the work for you. Hi Dave- Great blog. Quick question. Calculating a fixed number of business days from a start date..So, I need to calculate 34 and 40 business days (respectively) from my starting date attribute. Obviously this is easy with calendar days8230business days8230not so much. Any guidance you can provide would be great. We had a request to exclude only Sunday, I made few change to array it is working absolutely fine. Now the business is requesting to have the Turnover Time in actual hoursdays Date 8211 Time Actual Time Utilized Start Date Friday 72712 3:30 PM 8:30 Saturday 72812 12:00 AM 24:00:00 Sunday 72912 12:00 AM 00:00 End Date Monday 73012 10:30 AM 10:30 Total Time 43:00:00 If I use the formula i get 3 working days (Excluding Sunday). However the actual time utilized is 43 hours which 1.79 day i.e. 2 days approx. I tried few things, but nothing is working out. Is there a way to get this. Dan, that8217s an interesting twist on the question I think this is how I would approach it: First, you know that there are 5 business days in a week. So if you are advancing 40 business days, that8217s easy because 40 business days 8 regular weeks, so 40 5 7 56 net calendar days. With 34 days you need to figure out how many even weeks there are. Take 34 5 and truncate the result, which gives you 6 total weeks. The remainder from 34 5 is 4, so you have to add four additional days. Here8217s where the tricky part starts8230 if your starting day is Monday, and you add four days, they8217re all business days. If your starting day is Friday, then you have to add 6 days because of the weekend. Basically you could create a new formula matrix that works like the one in this post. There is a post around somewhere that shows you how to get the difference in hours between two dates. It involves using DaysBetween() to get the number of full days between, and then subtracting the two times to get the rest. I suspect that if you substitute the Business Days Between logic for the DaysBetween() function you can get the answer you need. Hi Dave (or anyone who can give solution), I would like to display a report with a daily transaction, but to have a subtotal by weekly, and to stop the week on Saturday instead of Sunday.i am currently using LastDayOfWeek(Amount.Production Day). but it shows Sunday as the last day Please help, Thanks, farra For those of you who want to calculate a variance of Monday to Tuesday as 1 day, instead of as 2 days, below is your answer: (Truncate(DaysBetween(Start Date End Date) 7 0) 5) ToNumber(Substr(822112345551234445123334512223451112345001234501234558243 ((DayNumberOfWeek(Start Date) 8211 1) 7) Mod(DaysBetween(Start DateEnd Date)7) 1 1)) 8211 1 For those of you who want to calculate a six day work week, which includes Saturday, below is your answer: (includes variance adjustment from above as well) (Truncate(DaysBetween(Start Date End Date) 7 0) 6) ToNumber(Substr(822112345661234556123445612334561223456112345601234568243 ((DayNumberOfWeek(Start Date) 8211 1) 7) Mod(DaysBetween(Start DateEnd Date)7) 1 1)) 8211 1 Dave, awesome post. A real life saver. Terima kasih. I8217m now a Logistics department hero thanks to you Many thanks for your support and fantastic explanation Thanks Dave, it is awesome post. I am trying to create these formulas as objects in the universe. database is db2 if the object is substr(Select(PICKConsolidator Code),5,2) 8211 then i don8217t have parse errors but when i use substr(822112345551234445123334512223451112345001234501234558243,5,2)then i get error in the universe. What is the error Also, if you8217re working in DB2, there are likely to be other ways to accomplish what you need rather than resorting to the string-array trick8230 In the universe i am getting 8220Parse Failed: Exception: DBD IBMCLI Driver CLI0118E Invalid SQL syntax. SQL STATE37000State: 37000 8221 error. I tried to create a derived table and use it Like this - Select 1234555123444512333451222345111234500123450123455 as Array from SysIBM.SysDummy1 and then use it in substr(Select(DatesArray),char((((DAYOFWEEK(Select(PICKHeaderDate 8211 Start Ship Date)))-1)7)(Mod(Select(PICKHeaderDate 8211 Start Ship Date)-Select(PICKHeaderDate 8211 Shipped Date),7))1),2) but still i get 8220Parse Failed: Exception: DBD IBMCLI Driver AS SQL0171N the data type,length or value of argument 822028243 of routine 8220SUBSTRING8221 is incorrect. SQL STATE428158243 error. The second argument for the substr() function should be a number. Perhaps you need to take the long expression and make sure that it is converted (via cast() perhaps) to a numeric value Hello Dave, Your post helped a lot to give me the number of business days between a date range. I used variable 8220DAYS8221 for getting number of business days. Unfortunately I am facing issues while using the same variable 8220DAYS8221 in new tab report where I want 8220Sum(DAYS)8221. It gives me wrong values. we will be grateful if you could at least throw some light on the actual formula for difference in hours. We keep getting an error using DaysBetween() function to calculate for difference in hours between two dates. The formula for calculating this 8220Actual Start Time and Actual Finish time8221 has been a long outstanding subject. Can you kindly add your input which has been very valuable. As this is an old comment I am just now getting to answer, hopefully you have found a solution. With the only thing I have to go on being 8220wrong values8221 without any further indication, I suspect that it8217s because you8217re using the data out of context, meaning one or both of the days involved in the calculation are not present in the block. Hi Dave, Need your help I want to get last 10 business days data from my table. I have tried your previous old post and i got data including saturday and sunday. I want to exclude that..help me. The code which i tried is: DATA: lidx LIKE sy-tabix, wdate TYPE sy-datum. READ TABLE ltrange WITH KEY fieldname 8216ZKLSDAT78242. lidx sy-tabix. wdate sy-datum 8211 7. ltrange-sign 8216I8217. ltrange-option 8216BT8217. ltrange-fieldname 8216ZKLSDAT78242. ltrange-low wdate. ltrange-high sy-datum. MODIFY ltrange INDEX lidx. ltrange-fieldname 8216ZKLSDAT78242. ltrange-sign 8216I8217. ltrange-option 8216BT8217. ltrange-fieldname 8216ZKLSDAT78242. ltrange-low 0. ltrange-high 0. append ltrange. psubrc 0. I8217m sorry, but I have no idea what that code is. I have a bizarre roundabout way of doing this, but it checks out against networkdays(. ) in Excel. I am using it to do calculated SLAs, so please persevere through the variable names. My approach is to first find the number of weeks, subtract two days for each week that is found, and then look at the remainder. Find out how many days in the remainder, and depending on what day the start date falls on, subtract an additional 2 days or leave it unchanged. Required functions: Truncate DaysBetween If elseif else SLA Duration 8212821282128212 DaysBetween(Submit DateRequired Resolution Date) SLA Weeks 821282128212 Truncate(SLA Duration 7 0) SLA Remainder 8212821282128212- (SLA Duration 7 - Truncate(SLA Duration 7 0)) 7 Result: If DayName(Submit Date) Monday And SLA Remainder lt 4 Then SLA Duration - (SLA Weeks 2) ElseIf DayName(Submit Date) quotTuesdayquot And SLA Remainder lt 3 Then SLA Duration - (SLA Weeks 2) ElseIf DayName(Submit Date) quotWednesdayquot And SLA Remainder lt 2 Then SLA Duration - (SLA Weeks 2) ElseIf DayName(Submit Date) quotThursdayquot And SLA Remainder 1 Then SLA Duration - (SLA Weeks 2) ElseIf SLA Remainder 0 Then SLA Duration - (SLA Weeks 2) Else SLA Duration - (SLA Weeks 2) - 2 8230 this seems to work for all durations that are not negative or 0. Would be great if one of you guys could double check this I am using SAP BusinessObjects 12.1.0 Using a big 8220if8221 statement was one of the strateg ies I mentioned in my post. It8217s certainly workable. I just wanted to find a shorter way to do it. thanks for the formula for calculating number of business days between two days ,could you please do one more favour of providing the formula for the below requirement Thanks in ADVANCE start date 04032011 16:00 end date 07032011 09:30 office hours is from 09:00 18:00 office is closed weekends from the above scenario, the process time should be 2:30 hours Hi, in my opinion, that sort of complex requirement would be better suited to a custom database function. Thanks for making life easier for BO developers. I was working on the same problem and deduced following code to find business days between two dates:- Floor(DaysBetween(startdateenddate)7)5 (Mod(DaysBetween(startdateenddate)7)- If (Mod(DaysBetween(startdateenddate)7)) gt DayNumberOfWeek(enddate) Then (If (Mod(DaysBetween(startdateenddate)7)) - DayNumberOfWeek(enddate) gt2 Then 2 Else (Mod(DaysBetween(startdateenddate)7)) - DayNumberOfWeek(enddate))Else If (Mod(DaysBetween(startdateenddate)7))lt DayNumberOfWeek(enddate) Then (If DayNumberOfWeek(enddate) lt5 Then 0 Else (Mod(DaysBetween(startdateenddate)7) -5))) It has worked for all the test dates till now. Need your help in finding a scenario where in above variable might fail. actually I am getting 8220Age8221 from database dateDIFF(dd, startdate, GETDATE ()) which I replaced with following in the code I previously posted but I realsed that there is difference of 1 day so the more gerneralized formula would be Floor(age7)5 If Mod(age7) 0 Then Mod(age7)- If Mod(age7) gt DayNumberOfWeek(enddate) Then (If Mod(age7) - DayNumberOfWeek(enddate) gt2 Then 2 Else Mod(age7) - DayNumberOfWeek(enddate))Else If Mod(age7)lt DayNumberOfWeek(enddate) Then (If DayNumberOfWeek(enddate) lt5 Then 0 Else If Mod(age7) 1 Then 1 Else DayNumberOfWeek(enddate) -5) where age is difference in days between two dates Hi Dave. I8217ve tried your solution based on a six days week, and it works fine just as usual. But8230 I have, I think, an interesting challenge for you. My company send goods worldwide and they asked me to compute the elapsed days between the departure date and the delivery date, in working days meaning8230 excluding the holidays of the countries of destination. And, of course, in a report(for some reasons difficult to explain here). Any idea. Thanks, and thanks again for your so amazing blog and solutions How many holiday 8211 country combinations are we talking about here Of course the best solution would be to do this in the database8230 but I am sure you already know that. Indeed8230 I know it MUST come in the database, but actualy it won8217t in an acceptable time . We8217re just talking about all countries around the world, with an average of 15 holidays per country, and yes over years (up to 2030). Even with a calendar or with an excel file as dataprovider, I don8217t see how to have it in the report (BOXI 3.1). If I had to do this, what I might try is to build a spreadsheet to substitute for my calendar table. The spreadsheet would have country calendars by day and would be embedded within the document as a personal data provider. (You would need to use Rich Client to start development on this report, and then use the technique outlined in the last page of this blog post to keep that XLS file static for future refreshes.) Thanks a lot, Dave. I came across the same conclusion this morning . What I missed in my spreadsheet was to use a full calendar per country (I8217ve tried with only the list of the holidays, and of course didn8217t work). This makes the computation on the working days easier, as I will also use a flag to define the sundays. Thank you for the link as well, I wouldn8217t have thought to check the boxes. Your blog is such a great source of inspiration and often help me to solve complex problems. Don8217t hesitate to keep posting Required functions: Truncate DaysBetween If elseif else SLA Duration DaysBetween(Submit DateRequired Resolution Date) SLA Weeks Truncate(SLA Duration 7 0) SLA Remainder - (SLA Duration 7 8211 Truncate(SLA Duration 7 0)) 7 Result: If DayName(Submit Date) 8220Monday8221 And SLA Remainder lt 4 Then SLA Duration 8211 (SLA Weeks 2) ElseIf DayName(Submit Date) quotTuesdayquot And SLA Remainder lt 3 Then SLA Duration 8211 (SLA Weeks 2) ElseIf DayName(Submit Date) quotWednesdayquot And SLA Remainder lt 2 Then SLA Duration 8211 (SLA Weeks 2) ElseIf DayName(Submit Date) quotThursdayquot And SLA Remainder 1 Then SLA Duration 8211 (SLA Weeks 2) ElseIf SLA Remainder 0 Then SLA Duration 8211 (SLA Weeks 2) Else SLA Duration 8211 (SLA Weeks 2) 8211 2 this seems to work for all durations that are not negative or 0. I used this setup from Casey and it was the closest to our result we need. We039re over by one day. I039m trying to find what to tweak to remove a day in this overall calculation, but I039m not seeing the adjustment. Anyone have any additional ideas Thanks a lot for the Workdays formula8230 I need your help to the same for Calculating Business Hours or Working Hours. I have 2 time stamp columns. and I need to calculate DAYHORUS (06:00-21:00) AND NIGHTHOURS (21:00 8211 06:00) Thanks a lot in advance Can we change the system UDF data type for an instance 8211 Since I have issue in using DaysBetween() function. DaysBetween() requires same data type to calculate the days and I find one of the date has the data type string. How can we over come this There are functions to convert from one data type to another. For example, ToDate() converts a string to a date, and FormatDate() converts a date to a string. This is an awesome post. Thank you Dave. Did anyone tried excluding holidays. Your help is much appreciated Holidays are the exception to the pattern, and would have to be accounted for individually. The best solution for this ultimately is to have a 8220business day8221 indicator on a calendar table in your database. I have a calendar table in the database with a business day indicator. I have another table that has two date fields (X and Y). There is no join between these two tables. I need to calculate days between X and Y and excluding holidays. To exclude the holidays, I created a variable () in my report, and then used the DaysBetween() function Where the holidayflag 1 as the formula to calculate the number of holidays between the two dates. However it was not able to determine the no of holidays between X and Y. Your help is much appreciated. What database are you using In the past I have solved this by writing a database function, often called BUSINESSDAYSBETWEEN(). Catchy, right The function is responsible for taking two input dates, checking the range of dates in the calendar table, and counting the number of rows where the holidayflag 0 and returning that value. If you can8217t write a database function, you could still do this at the universe level but you8217re going to need to be able to create a join from your date fields to the time dimension table. Can you do that Thanks for the response. I am using oracle db. Yes I would be able to create a join from the date field to the date field in the calendar table. However in my case, I have two dates field X and Y in one table. I tried using a join like the one below but it doesn8217t seem to work. Order.X Cal.Datevalue Would I have to duplicate the calendar table and then create separate joins for different date values. But it seems cumbersome and error prone so I am not sure. Not sure if I am making much sense here. Any inputs on this would be highly useful . Would you be able to help with the formula how to calculate a duration between two date excluding weekends (based on specific business hours) and also excluding wekends and holidays again (based on specific business hours). For example: startdate: 882014 6:54:45am and enddate: 1182014 9:00:00am and working hours are Mon-Fri (7am-7pm). the duration here is around 2hrs and 5min. Terima kasih sebelumnya. Thanks Dave. I created a Business days between function and it worked like a charm I have this requirement to create a WEBI report having 2 date prompts(START DATE,END DATE). Client wants to have a condition on the prompt so that START DATE should be always less than END DATE and if the user enters a date for END DATE which is less than the START DATE. it should throw a ERROR message. Is this functionality achievable in BO I am using BO3.1 Unfortunately we can8217t do this, as the prompts do not have to be processed sequentially. What I mean by that is the user could enter the End Date before they enter a value for the Start Date. Ah, you say, but why can8217t I validate the prompts at query run time Unfortunately we don8217t have tools to do that either. It8217s one of the primary requests that I have for the next version of Designer or IDT. Hi Dave, I have a requirement to display data between previous months first day and previous months last COB date on passing magic date to start and end date prompts. I am retrieving the previous months lastr COB date using a custom oracle function and the report retrieves data perfectly for date range between the previous months first day and previous months last COB date . I need to display these dates in the report too. I would like to know if there is any method to retrieve previous months last COB date in the report to display in the WEBI report .I can use the custom oracle function to retrieve it but i dont wanna use it in SELECT clause of query due to performance issue. eg: when i pass magicdate for startdate prompt8211gt99999999,enddate prompt8211gt99999999,then i need to display 01-SEP-2014(first day of previous month) and 30-SEP-2014 (last COB date of previous month) in the report. I know the logic to display 01-SEP-2014 but i need to logic to display 30-SEP-2014 . Here8217s what I did to calculate days between excluding weekends DaysBetween( RelativeDate(Date1 -DayNumberOfWeek(Date1)1) RelativeDate(Date2 (7 8211 DayNumberOfWeek(Date2))) ) 1 - 2( DaysBetween( RelativeDate(Date1 -DayNumberOfWeek(Date1)1) RelativeDate(Date2 (7 8211 DayNumberOfWeek(Date2))) ) 1 )7 -(DayNumberOfWeek(Date1)-1) -(If 5 8211 DayNumberOfWeek(Date2) lt 0 Then 0 Else 5 8211 DayNumberOfWeek(Date2) ) -1 Thanku so much Dave for sharing this I have achieved your results Now I need to extend the results I have data with weekends cut out. I need to exclude holidays as well. I will do it in hardcode way with elseif and manually modified it year by year. I don8217t know where to start, can you give me some guides Thanks Your table 8220Starting DayDays to Add8221, contains 7 colums with 8220Days to Add8221-values. But there will never be more than 6 days in the extra range (after the 7-day periods), so 6 columns is sufficient When I put in the following formula: (Truncate(DaysBetween(ToDate LastDayOfMonth(ToDate)) 7 0) 5) ToNumber(Substr(822112345661234556123445612334561223456112345601234568243 ((DayNumberOfWeek(ToDate)-1)7)Mod(DaysBetween(ToDateLastDayOfMonthToDate)7)1 1)) I get the following error message: Invalid character 82161.23456612345561e0488242 as position 84. (IES 10080) (WIS 10080) Do you have any ideas on what the issue might be I have Report Month 1,2,3,4,5,6,7,8,9,10,11,12 amp Report Year 2011,2012,2013,2014,2016 as separate fields. How do I calculate number of days in a month for a given year For e.g for 2016 Month Feb, Number of days 29 Need a variable calculation formula There are string functions that would let you concatenate the month and the year, so you can combine Feb with 2016. Then I would add 8216018242 as the day, and then use the LastDayOfMonth() function to determine the number of days in that month. I have a requirement to calculate the business days(excluding weekends) from a given two dates and I came across your formula and it worked like a charm, But further extending I need to show the Average days when tried it is giving me wrong results I know this might be the issue with calculation context since I am no where using the respective calculated dates in the block I am using. could you please help me with the calculation context. Formulae I used my dates are Assigned Status Date and Completed Status Date DaysBetween(RelativeDate(Assigned Status DateFloor(DayNumberOfWeek(Assigned Status Date)6)(8-DayNumberOfWeek(Assigned Status Date)))RelativeDate(Completed Status DateFloor(DayNumberOfWeek(Completed Status Date)6)(8-DayNumberOfWeek(Completed Status Date))))-(Floor(DaysBetween(RelativeDate(RelativeDate(Assigned Status DateFloor(DayNumberOfWeek(Assigned Status Date)6)(8-DayNumberOfWeek(Assigned Status Date)))(DayNumberOfWeek(RelativeDate(Assigned Status DateFloor(DayNumberOfWeek(Assigned Status Date)6)(8-DayNumberOfWeek(Assigned Status Date))))1)-1)RelativeDate(Completed Status DateFloor(DayNumberOfWeek(Completed Status Date)6)(8-DayNumberOfWeek(Completed Status Date))))7)2) Sum(Date Diff)In(Ticket NumberUser name) Sum of daysTotal Tickets The cross tab I am using contains user name. department name so the information is spread out like each user name by each department total tickets completed and the final column will have Average days. please help me out with the calculation context formula or any work around for this scenario Hello, I didn8217t manage to create the correct matrix 8211 days to add - I need to consider weekends as fridaysaterdaysundaymonday This is a great help but I found the matrix needed adjustment because DayNumberOfWeek() for Sunday is showing as day 1 in BO 4.1 Moving the Sunday numbers to the beginning looks to have solved my problems. Used 0123455123455512344451233345122234511123450012345 instead of 1234555123444512333451222345111234500123450123455. Great, Lee, thanks for sharing your update. My requirement is ,I have 5 date object,like AC open date,AC close date,AC Frozen date,AC X date,AC Y date in a report.First 3 objects will come from Universe and remaining 2 objects will be calculated in runtime. I have a calender table whit flag indicating whether it is holiday or working day. I have to calculate daydiffernce excluding holidays for all the 5 date objects in report. Like in report I have 5 columns LIKE TAT1DAYSBETWEEN (date1,date2) Tat2DAYSBETWEEN (date2,date3) Tat3DAYSBETWEEN (date3,date4) Tat4DAYSBETWEEN (date4,date5) Tat5DAYSBETWEEN (date5,date1) ,all are excluding holidays. Backend is sqlserver 2008,using deski reports3.1. Can anyone help me on this how solve to achieve DAYSBETWEEN for multiple date objects . Leave a ReplyOne of the hardest concepts to explain when introducing new folks to Business Objects is context. I now imagine blog readers who have been working with the tools for a while have started nodding their heads in agreement, only to stop and say, 8220Hey, wait a second, is Dave talking about universe contexts or report calculation contexts8221 And the answer is, of course, yes. Both concepts can be quite complicated and take a while to fully understand. Since I have already written a number of blog posts about universe contexts I thought it was time to turn my attention to the report technique of calculation contexts. Note that this concept is used in Desktop Intelligence and Web Intelligence but not in Crystal. This post is intended to be an overview only. I have plans for a detailed post on each of the various context operators where I will go into much more depth. For this post my goal is to introduce the concept and provide some basic definitions. Introducing Calculation Context The default calculation context is defined by the dimensions that participate in a report block. It is this feature that makes the dynamic nature of measures possible. When dimensions are removed from or added to a block the context changes and the measure amounts also change. This is controlled by the projection function set on each measure by the universe designer. However, a report writer sometimes wants a measure to be fixed at a particular value. Percentages are a really simple example. To calculate a contribution percentage I need to divide each individual value by the overall grand total. In order to do that, I need to know how to generate the grand total anywhere I want it. In order to do that . I need to understand how to use the various calculation context operators. Context Operators There is a simple (but powerful) set of context operators that I can choose from. I intend to briefly define each one in this blog post and then dive into more detail in future posts. As I mentioned when I started, the default context for a measure is based on the dimension values present in the block structure. I can alter which of those dimensions impact the calculation using one of the following three context operators. In is used to specify exactly which dimensions to include in a context. Other dimensions in the block are ignored. Adding removing elements from the block does not impact this calculation unless a removed dimension was specified in the context. In that case a MULTIVALUE error is displayed. ForEach is used to include a dimension in a context. The calculation context is still affected by other values in the block. ForAll is used to exclude a dimension from a context. Other dimensions of the block will still be considered. Adding or removing values from a block might change the value, but it will always ignore the ForAll items. The operators listed above all require one or more dimensions as part of their syntax. However, calculation context can be specified by more than a set of dimension values. There are a series of report structure keywords that can be used as well. In Report sets the context at the report or 8220grand total8221 level. Any formula with these keywords for the context will return an overall total. Note that the total may still be affected by report filters. In Block sets the context for each block section. For years I have been waiting for this setting to be renamed to reflect how it really works, but it hasn8217t. I suppose it8217s for backwards compatibility. If there is only one block on a report then 8220In Block8221 and 8220In Report8221 are going to be the same. But when a block is broken up into sections, then this context will generate a total for each section value. For that reason I submit that a better name might have been 8220In Section8221 instead, but as long as I know what it does the specific word does not really matter. In Body is the standard default context for each row of data. With Desktop Intelligence we have the option of In CurrentPage which can be quite useful and unfortunately not yet available in Web Intelligence. I probably don8217t have to explain how the 8220CurrentPage8221 total is derived. Finally, these keywords can appear in several places in a formula. The most common are the 8220input8221 and 8220output8221 context settings, but certain functions (like RunningSum() for example) also have a 8220reset8221 context. Input context is used to determine the input values for the formula. Output context is used to determine the output scope of the formula. Reset context is used to determine when a running function starts over at zero. Dimensions referenced in the input context do not have to appear in the block. Output and reset dimensions must appear in the block in order to function correctly. For many calculations the default input context works just fine. I will show an example where it8217s crucial to understand input context later on. For now I would like to move on to a simple example. Simple Calculation Context Example It8217s hard to fully grasp some of the subtleties of calculation context without good examples. For this post I will provide one simple but common example that I mentioned earlier: contribution percent. I will start with a simple block with one dimension (resort), one measure (revenue), and a grand total as shown here. This block already has a total on it. If this were a typical spreadsheet I could reference the total value using a rowcolumn address, but that doesn8217t work in Web Intelligence. That means I have to use another technique to generate the grand total. I mentioned the solution earlier8230 all I have to do is create a simple formula that looks like this: Here8217s the formula again: Revenue Revenue In Report Remember that the In Report context projects the measure up to the overall report total. By using that calculation context operator in the denominator of my division I ensure that I will always be dividing by the grand total in this formula. The results What8217s Next This post barely scratched the surface of the calculation context question. It provided a few brief definitions and one simple example. I have several other posts planned that will dive into much greater detail about this often confusing subject. First I plan to talk more about In versus ForEach versus ForAll and how they are different. Next I plan to cover input versus output context and when each should be used. As I write this post neither of those two are much more than an outline for now, so I don8217t have an estimated publish date. But they8217ll get published. Eventually. 125 Responses to 8220Calculation Context Part I: Overview8221 Thanks for this excellent post, I am really looking forward to future posts on this topic. Calculation contexts are often misunderstood and guessed at, even after years of experience with using them. Hopefully this will increase the understanding of all webi and deski users. Hi Dave. If you were a DJ I8217d be saying love the show. Every time I need to use calculation contexts I have to go back to first principles and work through examples to aid my understanding. So more in-depth posts will be great. Anyway, in your example above, you have no aggregate function and no brackets. My simple way of remembering syntax is that input contexts are within the brackets e.g. sum(measure in context) and output contexts are outside the brackets e.g sum(measure) in context. With this in mind, is the IN REPORT in the denominator technically the input or output context Hi Dave, Thanks for choosing Calculation contexts as subject to this post.I just can8217t stop myself mentioning that I was asked the difference between For Each and For All in my previous interview.I answered but will see how much of it is right by reading your next post8230 I am looking for some stuff on Integra solutions but couldn8217t find them, Quoram is popping up. I understand that Integra merged with Quoram but where can i find the materials. Hi, Grantie, thanks for your comment. The truth is you don8217t have to use aggregate functions for the formula if the projection function is defined correctly. In the case above I left it out for simplicity. Input context is always inside parenthesis, so to answer your question the 8220In Report8221 is the output context. Jus, the company Integra Solutions and the associated web site no longer exists. When I went to get a link to the downloads page on the Quorum site, it seems they have redesigned the site since I last looked. I searched, and there are pdf files still available, but they don8217t seem to be indexed on a page anywhere. I don8217t know what to suggest, other than to try using their contact page. Is there a specific item you were looking for If it8217s something I created it8217s possible that I could post it here on my blog for reference. Hi Dave. Yes I understand the projection and that8217s what I was getting at I suppose. Without it explicitly being there, I never know whether the SUM function (as defined by your object8217s projection) would encompass the In Report within the input or output context i So I always end up explicitly putting the aggregate function in just so that I can understand whether I8217m referring to the Input or Output context. Sorry to drivvle on. I know in this example it8217s neither here nor there, but it8217s something that has bothered me and I8217ve never got an answer to. So from what you8217re saying your Revenue In Report is the same as Sum(Revenue) In Report. Goodness me. See why I struggle. And I8217m trying to get my head around a fan trap problem at the same time Great site. Thanks for sharing your brains. Grantie, on any given row of a block, the default input and output context values are always 8220In Body8221. If a value is on a footer of a break, then the output context is based on the structure of the break. To make matters more confusing when you do a Sum() projection the input rarely makes a difference. Suppose you have detailed row values of 1, 2, 3, 4, and 5. Suppose that 1, 2, and 3 are detailed values for 8220A8221, and 4 and 5 belong to B, something like this: If I do a break on the dimension value, I get this: If I want a report total, it doesn8217t matter if my input context is the dimension value (1 2 3 4 5) or the break value (6 9) as both results are still 15. Things get more interesting when averages or other operations are used though. Hi Dave, I always have to check with the notes when I have to deal with Input and output contexts. Could you tell me the difference between Input and Output contexts. One is for input, and the other is for output. In all seriousness, that8217s a topic for the next blog post. It8217s a bit much to cover as a comment, so stay tuned for more8230 Hi Dave, I need to use calculation context in my report. I need the data into one report from four different universes which are at different grainshierarchy..It would be great if your could post more details with examples about In ,For Each,For All,In Report,In Block and In Body calculation contexts..thanks in advance.. Hi, Yogesh, I do have plans to write more about ForEach and ForAll and so on. I have a post in draft status now that I need to finish. I hope you can be patient as sometimes things move slow. You are always good with your post and it helps me a lot. I would love to see more posting on calculation context. A particular question I have is, Can we use dimensions which are not there in the block but available in the query to alter the calculation of the measure using extended calculation context further if we can how we should be careful when there are multiple queries (dataproviders). Hi, Mathew, I have more posts in 8220draft mode8221 on this subject. I hope to get some of them completed soon. To answer your question, any dimension that participates in the Output context has to be on the block. A dimension that participates on the Input context does not have to be present. Hi, Dave. Thanks for the post. I8217m finding it really helpful. I have encountered a problem, though, which I believe requires the use of calculation context that I8217ve been wracking my brain on for the past week or so with no luck. Imagine we8217re breaking down revenue by customers, but each customer could live in multiple states. The universe is built with one context containing our customers and measures, and a second context that joins customers to states. When I bring them into a block together, everything works out fine (imagine the first column is state, second is customer, and third is revenue): A X 10 A Y 5 A Z 12 B Y 5 C Z 12 total: 27 (the sum of the three customers) Now, if I remove the customer from the block, I get: A 27 (which is correct) B 27 (we would want this to be 5) C 27 (we would want this to be 12) t: 27 Likewise, if I put breaks on the states, I would get: A X 10 A Y 5 A Z 12 total: 27 C Z 12 total: 27 I8217ve tried every combination if input and output calculation contexts that I could think of, and invariably get the above results. Any thoughts BTW, if it makes a difference, we8217re on BOE XI r2 (hopefully moving to 3.1 later this year). Hi, Darius, and welcome. What you have described is what I call 8220unbalanced data providers8221 as you have dimension values at different levels. State is at a higher level than Customer, and you are using customer to pull the two data sets together. I think if you read the following post you will see an exact description of your problem along with a solution. Now for the bad news: The ForceMerge() function does not exist until XI 3.0. If you are working in Web Intelligence then you8217re out of luck until you upgrade. If you are using Desktop Intelligence then you can try using the MultiCube() function in a similar solution. Terima kasih. From what I read, that looks like exactly what I8217m looking for. The only hiccup is everything in my example is coming from a single data providor (albeit with multiple contexts), but I8217ll give it a try when we move to XI 3.0. In theory, I think it8217ll do the trick. Luckily my customers haven8217t asked about it yet, and I don8217t anticipate them trying to build a report using the data that would cause this issue. I8217m just trying to stay one step ahead of them8230 I have data below Now in my report I want below dimensions and count of unique RiskIDs at the Entity level. as below. i.e. I want to extend the Count() function to include Entity in the input context. Region Country Count(RiskIDs) Asia India 5 Kindly let me know how to write the formula for above. Thank you, Poonam N. Hi, and thanks for your question. From what I can see, this issue doesn8217t require context. Simply create a new variable that combines Entity with Risk ID and then count the results. while merging a vendor name only one value is duplicated . example: vendor contains hfc, uhc ,and aetna from table A.claim vendor contains uhc aetna and cobra when merging i got hfc uhc aetna aetna cobra aetna is repeating twice in the merged dimension.could you please help me in resolving it. Hi, this really isn8217t a context issue. As mentioned elsewhere. this blog isn8217t intended to be a source of personal support. You might want to try BOB or contacting technical support with your questions if they are not related to a post here. Terima kasih. We were finding problems with age calculation on a drilled report XI R2 SP4. The total we were dividing by always got filtered. XI 3 feature NOFILTER( object drill) works in SP4 8211 this 8216drill option8217 is undocumented in SP4 help 8211 so previously we had settled for two data sources, but this is far better. Is calculation context is used only for aggregation If not what are functions we can use pls give clear definition for calculation context I taught last week Web Intelligence courses and among the most important things I think are inputoutput contexts and track data (and the new formulas). I wish we had the capability in Web Intelligence to evaluate a formula in its contexts (as we could with old DesktopIntelligence). I really appreciate your style of explaining the concepts. This blog has really helped me a lot. Cab you please throw more light on input and output contexts Thanks amp Regards, Girish Jawale I am trying to create formula context, I have 3 measures to come up with. measure 1 8212 PY Total Spend measure 2 8212 Cy Total Spend measure 3 8212-CY Total Spend Total (till the end of the previous month) I have been a ble to create the first 2, my problem is getting the 3rd one created. Terima kasih atas bantuan Anda. Hi, Sunil, you don8217t have a context issue in your scenario. It seems you need a calculation that includes everything except for the current month. I would experiment with code that looks something like this: Sum(Spend) - Sum(Spend) Where (Month Current Month) That will get you an overall total and then 8220back out8221 the amount for the current month, leaving a current year total up to the end of the previous month. That8217s just an idea and not the actual syntax, as I am sure you realize. A nice, clear and concise summary of the context operators. My ques. is in regards to calculations using details of the dimensions e.g. qryDatadim userFiledim Date Area Zone Zone GeographicId (detail of city) Site Site 82128212821282128212821282128212821282128211 NumCalls DenCalls Two calculations required are Date and GeographicID. However, calculation for Date is dependant on GeographicID as two Sites share a common GeographicID. misalnya goodCalls if (numCallsdenCalls) IN geographicID lt 2 then numCalls While the above example calculates correctly, the values do not transfer correctly upwards to Date level. Re-posting the dimensions8230 qryDatadim8211userFiledim Date821282128211none none821282128211Area Zone821282128211Zone none821282128211GeographicId (detail of city) Site821282128211Site 821282128212821282128212 numCalls8212-none denCalls8212-none NumCalls DenCalls I have a specific situation wherein you might be able to guide me. I have a webi report wherein I have breaks and section set on certain columns. As a part of calculation I am required to do average on certain columns and produce the report average by break, section and grand total average. Now when I apply average function inside the section on break column and section column the averages turns out to be right8230..but when I need to display grand average of all the column values outside the section in another block 8230.the value just gets summed up8230.no average calculation takes place8230as recommended I tried applying context operators In Report, In Section 82308230but to no success 8230.could you let me know if there is a way to achieve this. Hi, Thanks for the topic and usefull information about the context, i am having one simple requirement that need to calcuate the total ouside the section. say i have a section on the peoduct and inside the section there wile subproduct count and those subproduct will be dynamic based on the prompt values. once they are generated i need to calucated the count of product out side the section, i am able to get the cout inside the section, but i am not able to get the cout ouside the section, do you have any idea8217s in this issue. Darsh, can you please try to do a sum count rather than using the average function Hi Dave. I often enjoy bumping into your site whenever I8217m googling along for some BO questions. Are you planning of writing your next blog on forEach and forAll soon These topics are as you pointed out quite difficult to explain, and also get familiar with. Best Regards Ingrid Dave 8211 what can I say8230..thank you Hi Dave, Thanks for this write up. I8217ve never went empty handed after reading your blog. Always get an answer here:) Thanks again Resending it seems few of the formulas didn8217t published correct.. Hi Dave, I have hierarchy Division-gtMarket-gtCenter. I created section on Market dimension with sales as measure. I wanted to see sales at Division level not for each Market even though it is sectioned by Market. I tried using input and ouput contexts still doesnt work. I tried using Sales ForEach Division I get multivalue error, If I use Max(Sales in Division) I get max of Sales out of all Divisions. Any help on what calculation contexts or syntax to use would be appreciated. Can you please explain in detail about the issue which poonam has posted 8220Comment by Poonam N April 15th, 2010 at 6:40 am 8220. I wanted all the dimesions in the report and want 5 to be displayed in the total. Can you please explain how to do it. Create a variable that combines Risk ID and Entity and count the unique results. You would get Sales-1001 Sales-1001 Sales-1002 Marketing-1001 Marketing-1001 Marketing-1003 Education-1001 Education-1001 When you count the unique values, you will be counting this: Sales-1001 Sales-1002 Marketing-1001 Marketing-1003 Education-1001 That will get you 5. Hi Dave, You have explained it clearly.Would like to know more about BO. Since I am new to this tool could to please advice me a few steps to get start with BO. While reading your blogs always it gives some idea about BO. Thanks a lot Hi Dave. Just wanted to extend the discussion to ranking. I8217ve successfully used calculation contexts and ranking to show the top x customers, with the remaining customers grouped into 8220Others8221. But when I place a break on this dimension, the ranking is reset within the break, meaning that each customer is now ranked as 1. I can8217t see a way of telling my variables to ignore the reset on break. Have you cracked this onegt This blog helped me a lot in understanding the basic concepts of Calculation Contexts. Thank you, Dave. I so wish you had published some more blogs on this topic that were in drafts8230 Thanks for this post and blog in general. It has really helped me with some tricky concepts that don8217t seemed to be covered in books (especially the post on unbalanced data providers). After reading this I seemed to be using contexts well for most things but have hit a brick wall when using counts, in some circumstances. In particular using CountAll on a dimnesion object. I wrote up the issue on BOB: If you have any comments on that issue it would be great but in general I8217d love to see part 2 of this post Has the 2nd part of this post been published yet I8217m looking forward to it. Eagerly waiting for your next post on this topic. I hope you will take some to write on In,ForALL,ForEach contexts soon waiting for the next post Hi Dave, I read your blogs very frequently. They are very informative and easy to understand. The way you explain the complex things, really make our lives very easy. Eagerly waiting for your next posts on Calculation Contexts. I restared to read all your blog posts since wake up. I should complete, infact grasp all the BO things effectively. I8217ve recently published a few reports using the ForceMerge() function which works great in the column. The column I have this applied to has a filter on the first report and a different filter on the second report. The third report combines the two. The third report total is correct, but the first and second report column totals are not 8230 it displays the whole report total for the ForceMerge() field. What am I missing I tried: If(Provider Type Group 8220Physician8221) Then Sum(fmRTS) Else 0 but this still gives the whole report total, not just the total where 8216Physician8217. Thoughts Any help is appreciated. Your blogs are very helpful and making me understand the concepts well. This blog is also well written. I am eagerly waiting for your detailed blogs on for each and for all. Hi Dave, I8217m in desperate need of your expertise. This multivalue has me stumped. I have a report where I am calculating the amount of time that it takes to resolve a service request, in weeks. So I have a variable that calculates weeks. the example is for those that take 0-4 weeks. Variable name Count0-4 IfWeekslt5 Then Count(CompletedInstallTurnaround.SR Number) I then also have a variable that counts how many records were in that count. Variable name Total0-4 RunningSum(Count0-4) This gives me a correct total of how many service requests took between 0-4 weeks. I039m trying to display the variable Total0-4 on a second report and I get MULTIVALUE I have then tried to use the max function and the last function I039ve also tried adding quotin Reportquot to the variable above. I still get the error MULTIVALUE on each one of them. How to I get the total from the first query to display on the second query Hi, Karen, your issue is probably because you8217re referencing a dimension object in an 8220If8221 statement. If that dimension object 8211 Weeks in your case 8211 is not in the block, then the measures roll up (project) beyond the level of Weeks and therefore are no longer in the same calculation context. There are too many weeks, thus you get a MULTIVALUE error. You might try creating a couple of variables to address this. First, create a flag variable with a syntax something like this: If Weeks lt 5 Then 1 else 0 Then use this flag in the 8220Where8221 clause of another variable, like this: Count(. ) Where (Flag 1) That8217s not the exact code, but you might see where I8217m going. By using the Where clause you can reference something that isn8217t in the block, which sometimes allows you to work around a multiple value error. Dave, that worked perfect. I8217m so happy I found your site. I8217ve found so much helpful information. I had resorted to putting the calculations in the universe which is not what I wanted to do. I can pull those back out now and do it the proper way. Hi Dave, As an ex BOBJ employee and BOBJ trainer I do find your explanations very easy to understand 8211 and although a lot of what you have here i know, as you say 8211 there is always something Thank you for your sharing Hi sir Dave Rathbun can you please demonstrate how to create dynamic measures i want to calculate difference between revenue of any two year from available 3years with only 1 measure and display the difference in the other column. Thanks in advance for giving your precious time. Hi, this isn8217t really a calculation context issue, and I have seen you have posted the same question on BOB which is probably the best place. Terima kasih. Hi sir That isn8217t me. i saw that post but did not work. please help. waiting for your reply. As I said, it8217s not a calculation context issue, and therefore isn8217t really appropriate to address in comments on this blog post. If you can provide additional details in the topic listed, what you have tried, how your situation is different, and what you ultimately need, I suspect you8217ll get better results. Terima kasih. Hi Dave, I always find that your posts improve my knowledge and often my product and this post, about calculation contexts is one that I8217ve found most useful. However I have a continuing problem with contexts for median and other percentile functions. I am easily able to test the results by pushing the source out to excel, sorting and then finding the middle row so I know what I should see and can compare with what I get. When I use median function the results for each row in a block are correct but when I try to get the median for the whole block in a table footer the result has never been correct. What context should I use to fix my mistakes Thanks in advance. I have never experimented with the median function that much, and I don8217t think I8217ve ever tried to use it with a context operator. Normally the footer context is whatever the break (or breaks, if multiple) are. If I have a block with Resort, Year, and Revenue, and I break on Resort, then the footer context is In (Resort) as you probably know. Dave, you come highly recommended by members of the Bus Obj Boards. I desperately need your expertise. I need to create a report that is a top 10 ranking (which has been completed) but the report is too large to export to Excel or CSV. The crosstab report contains items on the left and individual store numbers across the top (each store number also belongs to a 8220group8221). The data is the number of cases each store has sold since the beginning of the year. My real problem is that I can get the report reduced down to an exportable (manageable) amount of data by using a filter that will limit the results for that tab to show only the results for a particular 8220group8221 of stores, but each time I use the filter, the order re-sorts based on the sales of that 8220group8221 of stores selected instead of using the grand total. I need to measure the items against the grand total because we are trying to show distribution voids. I can not get it to reflect the grand total, only the individual group total. Any help on this would be appreciated. Hey Dave , If I want to calculate the grand total of revenue for three years where there is section break in a year and break in a state how can I do it A grand total can always be generated using the context 8220In Report8221 in the formula. Glenn, you can use the NoFilter() function as part of the formula to get the grand total even after you have applied filters to the report. Awaiting for the sequel of 8220Calculation Context Part I: Overview8221 Hi Dave, I8217m triying to implement a margin of error and raise a flag if that margin is pass. I implemented this within Webi but the user will want to filter by flag ( Y N). I created a variable that will hold that flag but when I use it as a filter, it will only display one value Y, not N When the user selects that only value, Y, there8217s no data to display. I think Im missing something but cant figure what it is8230Any advise will be greatly appreciated. (Thanks) Can you post a few details about the data and the formulas you are using I suspect that what is happening is your numbers 8211 without context 8211 are being rolled up. The 8220Y8221 is then being calculated at the top 8220grand total8221 level, resulting in a single value. More details would help clarify your question. X amp Y are columns that sum up values. Those columns are in a table with the flag variable. Flag variable. If(Abs(x 8211 y) gt Abs(Margin of Error) Then 8220Y8221 Else 8220N8221 The above variable is working fine but when I set the Flag variable as a filter in the report. It would only show Y and when selected, no data is displayed. I hope this helped. Data exaple: Error Margin: 0.03 X Y F 1.1 1.2 N 1.3 1.9 Y Any thoughts Webi8217s filters cant use contexts even if you set them as dimension. Hi Dave, I have a report which will display top five projects and their revenue percentage. Percentage is of a project revenue is calculated as project revenueAll projects revs. Here i m not able get the All project revenue in each record to calculate percentage. Could you pls suggest. Sample data: project rev percentage 1 500 (5002000)10025 2 400 (4002000)10020 3 300 8230.. 4 200 82308230 5 100 82308230 Total of top five 1500 Total of all projects 2000 (i calculate using formula: sum(nofilter(projrev))) thanks in advance Satya You need an 8220In Report8221 somewhere to get the overall total. Also some times I have to play with the NoFilter() function and decide if it works better as Sum(NoFilter()) or NoFilter(Sum()) instead. Satya,You can use this formula 8220Measure(Sum(Measure) In Report)8221 Hi Dave, I would like to exclude a particular object in the context (file) and than take the sum with a different object as output (cons) and than sum again on file level. I8217ve tried every combination of ForAll and ForEach and even tried something with In, but it doesn8217t seem to work. I8217ve also considered using 2 different queries such that the cost is gathered on cons-level, but when using forcemerge() the cost of a consignment is only used for one file that the cons is on. I hope you understand my question. Below an overview of the data I have in my report and the results I would like to retrieve. Required summary overview: Could you please help me with this problem Or is this not possible I thought I followed your question, but then when I looked at your sample data I got confused again. How many data providers do you currently have Is your final output going to have 3 rows (as in your second example) or 5 (as in the first) Because based on the 3 rows, it seems like you can simply create a block with 8220file8221 and 8220Cons costs8221 and based on the 5 rows of raw data you showed, the cost should naturally roll up when you remove the 8220Cons8221 object. Or is this the final output you8217re looking for If what you8217re looking for is what you posted, I created your sample data in a spreadsheet, created a Webi report, and generated this output: My formula was this: Sum(Cost ForAll(File)) In (Cons) Hi Dave, Thanks for your quick response. I would like to solve my problem without using an extra data provider. Meaning that all information is available in one data provider. Currently, my raw data looks like: Cons file Cost 1 A 10 2 B 20 3 B 30 1 B 5 2 C 25 After creating a variable called 8220Cons cost8221 I can created the following table (same as raw data but including the new created variable. This variable calculates the total cost for each consignment. Cons file Cost Cons cost 1 A 10 15 2 B 20 45 3 B 30 30 1 B 5 15 2 C 25 45 Now, what I would like to do is create a block that provides me the below overview. This overview should sum all Cons Costs of each Cons on a particular file. For file B: Cost(cons 1)Cost(cons 2)cost(cons 3)15453090. file Cons costs A 15 B 90 C 45 What I get as output is the below block which is not what I need. For some reason I get the total cost if I remove the cons Object. file Cons costs A 90 B 90 C 90 I hope this clearifies my question. You get that because the 8220file8221 value is no longer available, which apparently in this case makes the 8220In File8221 act the same as 8220In Report8221 and you get the overall total. B asically it works this way: anything in the input context does not have to be in the block. Anything in the output context has to be in the block. You can8217t sum by 8220file8221 and use it to drive a calculation unless it8217s in the output block in some fashion. I also tried to create a break and fold the results, but the break total doesn8217t work because of the MULTIVALUE error that occurs. Maarten, i think your SQL should be as below to get the desired result. select B.file,sum(A.CONSCOST) from (select cons,sum(COST) as CONSCOST from TableA group by CONS ) A,TableA B where A.consB.cons group by B.file TableA has the data as below I am not sure whether we can achieve that at report level. Thanks a lot for this page 8211 I8217ve learnt a lot from it. There is something I8217m currently stuck on though and was hoping you might be able to give me a pointer: I have a report sectioned by a date dimension, but I need to have all the dates of the dimension available in each section. I8217ve tried nofilter(date) but this doesn8217t help and 8216in report8217 gives me a multivalue error. Is there a context operator that could help me with this please Hi, Rob, that doesn8217t sound like a context problem because you8217re missing dimension values rather than adjusting how measures work. There are a couple of other blog posts here that talk about making up data, perhaps one of those will help. I am currently using webi 4.0. I have two reports in one file. First one contains the overall sales of all regions and the second one is broken down by region. In the region report I need to calculate some expenses where i need to find the ratio by taking the region expenses divide by overall expenses, my measure give me UNAVAILABLE error.. This is the how my measure roughly look like : (ifExpenses1 Then Region Expenses earned profit) (ifExpenses1 Then overall Expenses earned profit) in report I used flag to avoid Multivalue error Appreciate your assistance. Hi Judy,to understand the problem clearly could you please provide a small data set and the desired result you wish to achieve Hi, I need some ideas to accomplish something. I am working on a table that needs to show the Top 10 Incidents by Configuration Item (not complicated), where I am stuck is in the column that needs to show the percentage, as I need it to show the percentage based on the total Incidents and not on the ones shown in the table: CI01 116 11.42 CI02 86 8.46 CI03 59 5.81 CI04 47 4.63 CI05 47 4.63 CI06 47 4.63 CI07 43 4.23 CI08 38 3.74 CI09 35 3.44 CI10 29 2.85 The total Incidents for this example is 1,016 and if I show the table with all data, all percentages are calculated properly. When I add a ranking or somehow filter the table, the percentages are calculated based on the information shown by the table, like this, which represents the percentage of the Top 10 only: CI01 116 21.21 CI02 86 15.72 CI03 59 10.79 CI04 47 8.59 CI05 47 8.59 CI06 47 8.59 CI07 43 7.86 CI08 38 6.95 CI09 35 6.40 CI10 29 5.30 The percentage is being calculated by this formula: CR - Ticket Count(Sum(CR - Ticket Count) In Report) CR 8211 Ticket Count is constant 822018243 sent by the database for each ticket and is defined as SUM measure in the Universe. Any ideas are appreciated. Terima kasih banyak. A rank or filter are two different ways that data can be reduced. If you want the overall total ignoring all filters then the NoFilter() function does that for you. A good initial guess might be: CR - Ticket CountNoFilter((Sum(CR - Ticket Count) In Report)) Dave Rathbun, you are a genius. I did not know that one of the 8220NoFilter()8221. I got exactly what I was looking for. Terima kasih. Hi Dave, I need to see sum of all rows for one of my measure column even when a dynamic report level filter is applied in webi.i tried using noFilter() function but that is not working. You8217ll have to provide more details than 8220not working8221 in order to get any useful replies. Thanks a ton for the post. It really helped me accomplish a very detailed level report. I am hoping you could suggest a solution for one detail of my report. I have a Region- Location 8211 Status 8211 of applications table in my report. I have a Horizontal stacked Chart(x-axis Location, Y-axis of the count) by status. The problem is the is being calculated for the entire report or any value given through input control whereas I want a for each Location by Status. Example: location Bay Area I want my to be count for each statuscount of Bay Area. Whereas I get to be Count of each Status for Bay Area for the entire report or any filter I select which changes constantly. Is there a way I missed where the calculation context can be applied to the Charts Thanks much Avanthi Just waiting for your detailed post on imput vs output context. Could you please post it as early as possible. Please post the part II for calculation context, waiting eagerly to see it from you. As always, you seem to explain things where I finally have the 8220ah ha8221 moment I8217ve been missing for a very long time. Thanks Once again this one page has helped me get past a problem point. I kept trying to use ForEach and ForAll and what I need was IN Delivered 100 I am working on a report where I need to use calculation contexts8230I have the following dimensions. Users, Dates and Comments. And my table looks like this: U1 D1 C1 U2 D2 C2 U3 D3 C3 U3 D3 C4 U4 D4 C5 U4 D4 C6 U4 D4 C7 U4 D4 C8 But the Comments with the same date and username are actually the same comment8230and needs to be counted for only once. I used calculation contexts using Count(CommentIn(UserDate)). Its not working and its is giving me the following results8230 U1 D1 1 U2 D2 1 U3 D3 0 U4 D4 0 Please help ASAP Is a section considered to be a filter I have report with sections and I need to calculate a rolling sum for the previous 3 months, but I need to filter the resulting table so that it shows months 4-6 but calculates the running sum using months 1-3. Because of the version that I am running I need to calculate the sum as hours previous(hours) previous(previous(hours)). I have added the NoFilter() function around the sum calculation. When I look at the first section, the sum is correct, but when I look at the next section, the sum is being calculated using the hours figure from the first section also. So I can only assume the NoFilter() function is removing the section in the calculation. Is there any way of using NoFilter() but resetting the calculation for each section Your post is really helpful..Inspired by all your work..I myself wrote one here: gauravkalra.wordpress Let me know how does that look. I have included very less detail in my first post..and tried to keep it interesting too:). I hope to follow up with more of them. Glad to have helped. Yes, a section is definitely a filter. But all is not lost. When I have needed to do this in the past, I have done something along these lines. Assume that our section is on the Resort object, and there are three years of data within each section. I want a running total of 3 years, but only where the resort is the same. I might build three objects. The first is called 8220Previous Revenue8221 and it has this formula: If Previous(Resort) Resort Then Previous(Revenue) else 0 The next is called Previous Previous Revnue: If Previous(Previous(Resort)) Resort Then Previous(Previous(Revenue)) else 0 Finally the running 3 years of revenue: Revenue Previous Revenue Previous Previous Revenue The total will then compare the current Resort value to the previous two rows, and only include the revenue when the resort values are the same. At the same time, you could use the RunningSum() function which already has a reset function built in. So what you really have is this: U1 D1 C1 U1 D2 C2 U3 D3 C3 U3 D3 C3 (this should be C3, not C4, as it8217s the same comment) U4 D4 C4 U4 D4 C4 U4 D4 C4 U4 D4 C4 (the last four are the same) What is the desired output from this Hi Dave, I keep reading your blog to find a solution for a data calculationanalysis problem. I have a measure called result value for A4,3,2,lt2,lt2 and for B4,3,lt2,lt2,lt2. The system proveds a measure called numeric value for A4,3,2,2,2 and for B4,3,2,2,2 by removing quotltquot sign.The system also give a measure calledcalculated value for A4,3,2,1,1 and B4,3,1,1,1 by using half the value after quotltquot. I need to use a rule for my calculation. The rule is: if total count of quotltquot is less than the total count of data number, use half of the value after quotltquot. if total quotltquot is more or equal to total count of data, put the data with quotltquot to 0. I create a measure calculated valuenewin tab quotreport 1quot which gives A4,3,2,1,1 and B4,3,0,0,0. When I calculate average and percentile for calculated value and calculated valuenewfor A and B, there is no problem. But when I create another tab quotreport2quot to summarise the average and percentile, there is no problem for the average and percentile for calculated value but gives multivalue for the calculated valuenew. I try everything and not working. Tolong bantu. Regards, Shaohua Hello, I8217m having the next problem and I want to see if you can give me some advice about it, let me explain. I have two data providers, this because the data comes from distinct databases. TABLE1 TABLE2 Date id detail Detail Cost 29413 A A1 A1 5 29413 A A2 A2 1 29413 A A3 A3 3 29413 B B1 B1 4 29413 B B2 B2 2 29413 C C1 C1 7 29413 C C2 C2 4 29413 C C3 C3 3 The report I need to launch must look like this: REPORT Date id Cost 29413 A 9 29413 B 6 29413 C 14 But what I get is something like this one: REPORT Date id Cost 29413 A 29 29413 B 29 29413 C 29 I tried to use the next combinations: Sum(cost) ForEach(Date,Id) RunningSum(costRow) RunningSum(costDate,Id) RunningSum(cost) ForEach(Date,Id) Can you help me with this calculation Hi Dave, When we use Foreach in aggregation functions with Where clause then do we need to follow specific order for specifying Foreach clause PLease see below example: My Formula is Average( Session Average Where(KP1822118243 And(Session Status8221C8221) And(Year822120138243) ) ForEach(Course CodeSession Number) ) This above formula gives right rsults but if i specify Foreach clause before 8220where8221 clause then it gives wrong result. Average( Session Average ForEach(Course CodeSession Number) Where(Kp1822118243 And(Session Status8221C8221) And(Year822120138243) ) ) PLease help me to understand the difference. Thank you, Mani Your posts are so informative and I8217m a big fan. I8217m sure this is simple and I8217m missing something, but I8217ve tried every variation of calculation context to no avail. I have four columns (shown in 8216table8217 below). I want the percentages to calculate correctly as shown in 8220COLUMN48243, but for some reason, they will not calculate as needed. So basically, I want to determine how many times a particular car feature is being used in unique models within the subset of unique Makes in the table. I then want to rank the Top 3 Car Features based on the USAGE RATIO column. It seems simple enough in theory, but I8217ve literally spent hours on this and cannot figure it out. Please let me know what I8217m missing. I would TRULY appreciate it. For the USAGE RATIO column, I8217ve used the following calculation contexts (in every variation): COLUMN3COLUMN2 Foreach(MAKEMODEL)in Block COLUMN3COLUMN2 In(MAKEMODEL) COLUMN3COLUMN2 In(CAR FEATUREMAKEMODEL) COLUMN1 COLUMN2 COLUMN3 COLUMN4 CAR FEATURE MAKE (Unique) CAR FEATURES IN UNIQUE MODELS USAGE RATIO (Column3Column2) Navigation System 50 15 30 Rear backup Camera 15 6 40 Heated Seats 30 12 40 Moon Roof 45 30 67 Top Air Bags 20 10 50 I posted a calculation context dilemma earlier, but it hasn8217t shown up yet on the site (hope it went through Please let me know if you did not receive it.) I wanted to provide a cleaner screenshot of the table in question, but can8217t figure out how to attach one to this forum. Basically, I8217m just trying to get Column 4 (USAGE RATIO). It8217s so simple on paper, in Excel, but for some reason, not in BO due to, I believe, calculation contexts, but none work for me. For some background, if you look at the data hierarchically, 8216Make8217 is at the top level, Unique 8216Model8217 is a subset of Make, and 8216Car Feature8217, the most detailed level, is offered in some 8216Models8217 by the associated 8216Makes8217. I want to show how many times a Car Feature is used in the unique Models of a given Make (which I have), and then divide that number of Models by the number of Makes for each Car Feature (which I can8217t figure out how to do and render correct results). I literally have no other folks to consult who have any BO knowledge, and BOB has produced no working suggestions so far. Hoping you can help8230 Thanks. say if I have a measure object in my report and I create a variable out of it(with no context defined) Example categorizing the revenue measure(1-100 million,100-200 million) . what will be the default context on that measure would it consider all the dimensions within the report while creating the variable The default context is based on the dimensions that are included in the block (or section) containing the new variable. For example, if the measure is in a stand-alone cell the output context would be 8220In Report.8221 If the measure is within a block, then the dimensions in the block determine the output context. If you include the specific context 8220In Report8221 within the formula, and place that measure inside of a block, then the specified context will override the dimension structure and you8217ll get the overall report total instead. Thanks, Dave, for the quick response. But the problem for me is, I have to create a section on the new variable. Just to let u know, when I used only rev in the formula 8216rev 1mill8217 then 82161-100mill8217..same Id is appearing in different categories(investigating this i found that the id has two rows in the table(database) because of a column(which is not available in my report) which has different values). So for the formula I used something like this 8216rev in (id)1mill8217 then 82161-100mill82178230thus defining the context for the measure limiting it to only the id. I was able to set up the section on this variable but ran into issues when trying to create an input control on this variable. I understand this blog is not intended for personnel issues8230please ignore this if am wasting your time. Really appreciate your contribution to Business Intelligence world..especially for starters like me. You generally cannot use a measure (or a variable derived from a measure) as a section. The reason for that is the measure scope (or context) is defined by the section, not the other way around. When I have had to create a break down by range (10-19, 20-29, 30-39 and so on for ages, for example) then I have created that as a dimension object in my universe instead of trying to do it on the report. I am new to BOB webi reporting and have started at a new company where the universe is set up differently than I have worked with before, we are trying to create a simple TY vs. LY rolling 12 month report (month to date plus last 11 months compared with same time LY) I am able to get the rolling using (year100) month Nbr for each block. (block1 has LY data and block2 has TY data) the issue I am having is creating a variable that can calculate data from each block. The report is displaying Month, week of month, Returns, Sales and RA Sales for each 12 month block. Last Year (Block1) This Year (Block2) Month Week RRA LY Sales LY Month Week RRA TY Sales TY Aug 1 50 6,000 Aug 1 20 7,000 What I am wanting to do is add a 3rd block or incorporate the totals into the 2nd block that calculates RRA TY RRA LY (created variable RRA TY-LY Change) and another calculation that shows the difference (RRA TY-LY ChangeRRA LY) When I try and add these to the report I get a single value duplicated in each row or a multivalue error. Any suggestions Hope you can help a complete newbie. I8217m working in Webi Intelligence (BOXI XI R2) with no access universe design. I have created a report measure variable in web intelligence which returns 1 if a record is current and 0 if not. I have a table on the report with a section break on case manager. If I do a sum on this measure in the table footer it returns the correct value for that section. However, I8217ve been trying to do a summary table in a seperate block and when I try exactly the same formula in that, i get the multivalue error. Any ideas Marcus, the problem is probably related to the fact that your 01 indicator is only valid at the row level. Once you try to project it to a higher level you get a MULTIVALUE error because there are multiple values (two different states, in this case) to pick from. What you might try instead is to use your current indicator as part of a 8220Where8221 clause on a sum, as the values used there do not have to appear in the block. Something like: sum(Measure) where (Current Flag 1) Do you have separate objects in the universe for this year (TY) and last year (LY) or are you breaking them apart on the report I have a problem in calculating the time taken for a particular status alone involved in a service request. multivalue error.One service request will have multiple no of status such as ( pending, work in progress. closed etc).Now i want to calculate the time taken for the status 8221 pending8221 which should in the business days calculation. Request Status Start Time Stop Time Total Time Taken 101 draft 1-Jul-13 5-Jul-13 4 101 review 6-Jul-13 7-Jul-13 1 101 WIP 7-Jul-13 8-Jul-13 1 101 Pending 8-Jul-13 9-Jul-13 1 101 Pending 10-Jul-13 11-Jul-13 1 Expected output Request No Total Time Taken(Pending) 101 2 Hi, Can you provide me Calculation Context Part II Hi Dave thanks for the extrordinary articles always8230. 4 years had past we are eagerly waiting for the later chapters Me too When am I going to get around to writing them, does anybody know In all seriousness, I have dusted off my draft for part two of this article and hopefully will have it ready to post without too much delay. I love your blogs and am so glad they come up frequently when I am searching for something. I8217m a little stumped here8230 I have a situation where I am pulling in a couple of dimensions (customer and fiscal periods) and the sales associated. What I want to do is show the sales for each customer for ONLY the latest fiscal period. So for example I am showing: Cust 1 8212 201401 8212 1,000 Cust 1 8212 201402 8212 2,000 Cust 1 8212 201403 8212 1,500 Cust 2 8212 201401 8212 1,700 Cust 2 8212 201402 8212 2,000 Cust 2 8212 201403 8212 2,500 What I WANT to show is: Cust 1 8212 201403 8212 1,500 Cust 2 8212 201403 8212 2,500 I8217ve gotten it pretty close, but I can8217t seem to get it just right. Is there any way to get a measure for the max (dimension) If there is, I can8217t seem to find it anywhere or guess at what the syntax might be. So I created a variable to give me the max fiscal period and since they are chosen via prompt, the varialbe is Max(Main Query.Fiscal Period) In Report. I called it MaxUserResponse. Then I came up with this formula: (Sales ForEach (MaxUserResponse)) The result I am getting is the total of Sales across the fiscal periods, which makes sense, since that is what ForEach does. But I tried (Sales In(MaxUserResponse)) also. I got the total for all customers for all fiscal periods. when I tried (Sales In(CustomerMaxUserResponse)) I got the total across all fiscal periods per customer. What am I missing I know I am close8230 Please help Thank you I think I figured it out. I just added a where clause to my formula. So now it looks like: (Sales In (Main Query.Customer Name)) Where (Main Query.Fiscal Period MaxUserResponse) Thanks for reading8230 Tietje excellent post8230 Were you able to publish rest of the context operators as per suggested by you8230 I am sure reading your posts will surely help clarify lods of my doubts. Moreover do you have a link or website where in i can access other posts of yours to get better insight of BO Hi, I need to implement a similar solution.I need to split the data into three category. based on the percent to total value, the top 70 into catagory 1. next 20 to category 2, last 10 to category 3. Any idea how i can do that. Ver Useful Link, It saved my day.Thanks Dave. I have a requirement to display an average number of beds in a hospital, but each hospital has a validity start and end date. I need to display the number of beds per hospital at a summary level, which is an average number for each hospital irrespective of how many entries there are for validity dates per hospital. Detail level data: Hospital- Validity start - Validity End - No of Beds 8212821282128212821282128212821282128212821282128212821282128212821282128212821282128212821282128211 ABC - 01012000 - 31012000 - 10 ABC - 01022000 - 28022000 - 20 DEF - 01012000 - 31012000 - 20 GHI - 01012000 - 31012000 - 30 GHI - 01022000 - 28022000 - 30 I need the above data summarised as follows: Hospital - Avg. No of Beds per hospital 82128212821282128212821282128212821282128212821282128212821282128212 ABC - 15 DEF - 20 GHI - 30 I have tried to average the measure ForAll 8220Validity Start8221, in an effort to exclude the date dimension from the calculation. I have also tried to average the measure 8220In Hospital8221 in an effort to only include Hospital in the calculation context. In both cases, I get the following output, where it adds up the measure for every entry of date, per hospital: Hospital - Avg. No of Beds per hospital 82128212821282128212821282128212821282128212821282128212821282128212 ABC - 30 DEF - 20 GHI - 60 Can you please help I have a weird situation. I have Account nos and balance coming from one query. The BO report seems to neglecting the negative balance. So when I sum up the balance only positive values are considered. That is to say Sum(Balance) gives the same result as Sum(Balance) Where (Balancegt0) and Sum(Balance) Where (Balancelt0) returns null. But there is negative balance. If I pull account no and balance into a separate report and apply a report filter for Balancelt0 then I can see the negative balances against the accounts. But if I remove the accounts then it displays blank cell for balance. Any idea what is going on. I used the example from this link bi.srivatsakr201108converting-rows-into-single-cell-comma.html to get multiple text values corresponding to an id into one cell, but as I need to do this for two type of multiple values, and my main query has already too much information, I did everything exactly as in the example in my main query and worked beautifully, but for the second one I am attempting to create it in a second query (as if I include this one also in the main query it takes forever), I tested in a second report and it worked fine, but then I tried to include it in the main report and I get multivalue, I merged the main ids, I made the variables as detail variables, I created an indicator variable (Boolean) for the comparison to the maximum value and it still doesn8217t work. In the second query and report I have something like this ID 8211 type of contact 123 8211 regular mail 123 8211 email 123 8211 phone 234 8211 email when I do the link example in a second report I8217m able to get it to look like this: ID 8211 Type of contact 123 8211 mail, email, phone 234 8211 email but when I try to move it to the main report it looks like this: ID 8211 Type of contact 123 8211 multivalue 234 8211 email my variables look like this: This one I also tried it as a Boolean and it still didn8217t work. Max Type ContactMax(Type of Contact) In (Query2.ID) A detail variable link to the main id of Query1 ID Type of Contact Type of Contact A detail variable link to the main id of Query1 Concat Type Contact If IsNull(Previous(Self(ID)))Then ID Type of Contact Else (ID Type of Contact8221, 8220Previous(Self(ID))) A measure variable: All Type of Contact Concat Type Contact Where (ID Type of Contact Max Type Contact) What am I missing how can I properly link it to the main report and get it to work Thanks a lot in advance, Hi Dave, when are you going to write about context in much more details. awaiting your post.please make it fast. Was wondering if you could help me calculate the percentages (G) of inlist values (VX) and (YX) within the same dimension(V,X,Y) on a crosstabs. V X Y G F A 6 100 25 B 12 200 50 B 18 300 75 C 24 400 100 D 30 500 125 E 36 600 150 I have a requirement in a Webi Report as below.I have everything except the YTD Planned cost.So now I need to calculate the YTD Planned cost based on the Planned cost,Portfolio,Project Name,Month and Frequency. Can some body help me how to achieve this in BO Webi or through SQL query is also fine. Hi Dave, I became a big fan of you. But I am not able to find the next blogs on input and output contexts as promised by you in this blog. Is it that you have not published those yet or I am not finding the proper link Eagerly waiting for your posts. Regards, Arup I am having a lot of trouble getting a simple query to change to what i want it to 8211 I believe i am inputting the formula correctly as i have not have problems before using something like this:. query 1 (Product Store Stock) Query 2 (Store) Distinct list of store8217s set by myself in a different universe. Merge: query1.store query2.store Source dimension: Query2.store Currently query 1 will give me a total stock figure by store product level. I require the store8217s from query 2 to be the one i sum up against so i8217ve tried: Sum(stock.Total Branch Stock Unit ForEach (Query 2.STORE)) This still gives me the stock figure from query 1 (it should be lower as i have tested this on another tab to see what the output is). I8217ve tried many variations of the above formula but all seem to get me the sum of product from query 1 when infact i need it to sum against query 2 store. Please advise, help would be much appreciated. Tinggalkan Balasan