PowerBI - Sales Dashboards

Sales Invoices

Introduction

Welcome to the guide for configuring Power BI for your Sales Dashboard. This manual will help you connect Power BI to SAP tables and create effective sales visualizations.

This guide focuses on two views: the Sales Silver View (silver_sd_invoice) and the Sales Gold View (gold_bi_sd_invoice). We'll cover setting up queries for each view, allowing you to access and transform your sales data.

In addition, we provide sample data for both silver and gold views in the Data Base - Data Example Views (Silver/Gold) section, giving you a hands-on experience to refine your skills.

This manual equips you with the knowledge and tools needed to tailor Power BI to your sales environment, enabling you to make informed decisions effectively. Let's get started on your journey to maximize your sales data with Power BI.

1. Power BI Dashboard Charts and Queries

1.1 Main Sales Dashboard

Compilation of all annual sales data summarized by month with added filters to display the information.

1.2 Charts - previous sales year vs current sales year

PBI Graph Parameters	Gold View Column
Axis X	MONTH - MY_DATEINVOICE
Axis y	SUM MONTHLY_SALES
Legend	YEAR - MY_DATEINVOICE

1.3 Sales Table – Sales Month/Year

PBI Graph Parameters	Gold View Column
Rows	YEAR - MY_DATEINVOICE
Columns	MONTH - MY_DATEINVOICE
Values	SUM MONTHLY_SALES

1.4 Filters – Description and Date

Group Shape To Image

2. SAP Mapping

2.1 Send Tables: SAP

SAP Table	Send Filter
VBRK
VBRP
KNA1
MARA
T001W	SPRAS = ‘E’
T023T	SPRAS = ‘E’
T171T	SPRAS = ‘E’
TSPAT	SPRAS = ‘E’
TVKBT	SPRAS = ‘E’
TVKOT	SPRAS = ‘E’
TVTWT	SPRAS = ‘E’

2.2 Sales Silver View: silver_sd_invoic

Description	SAP Field	Table SAP
Sales Date	FKDAT	VBRK
Year	Y_FKDAT	VBRK
Month/Year	MY_FKDAT	VBRK
Organization ID	VKORG	VBRK
Sales	NETWTR	VBRK
Currency	WAERK	VBRK
Exchange Rate	KURRF	VBRK
Channel ID	VTWEG	VBRK
Territory ID	VKORG = Organization VTWEG = Channel	VBRK VBRK
Office ID	VKBUR	VBRP
Zone ID	BZIRK	VBRK
Sector ID	SPART	VBRP
Plant ID	WERKS	VBRP
Material Group ID	MATKL	VBRP
Customer ID	KUNAG	VBRK
Material	MATNR	VBRP

2.3 Sales Gold View IDs: gold_bi_sd_invoice

Description	SAP Field	SAP Table
Sales Date	DATEINVOICE	SILVER_SD_INVOICE
Year	Y_DATEINVOICE	SILVER_SD_INVOICE
Month/Year	MY_DATEINVOICE	SILVER_SD_INVOICE
Sales by Month	SUM(NETWTR) = MONTHLY_SALES	SILVER_SD_INVOICE
Currency	WAERK	SILVER_SD_INVOICE
Exchange Rate	KURRF	SILVER_SD_INVOICE
Organization ID	VKORG	SILVER_SD_INVOICE
Channel ID	VTWEG	SILVER_SD_INVOICE
Territory ID	VKORG = Organization VTWEG = Channel	SILVER_SD_INVOICE SILVER_SD_INVOICE
Office ID	VKBUR	SILVER_SD_INVOICE
Zone ID	BZIRK	SILVER_SD_INVOICE
Sector ID	SPART	SILVER_SD_INVOICE
Plant ID	WERKS	SILVER_SD_INVOICE
Material Group ID	MATKL	SILVER_SD_INVOICE
Customer ID	KUNAG	SILVER_SD_INVOICE
Material	MATNR	SILVER_SD_INVOICE

2.4 Sales Gold View Descriptions: gold_bi_sd_invoice

Description	SAP Field	SAP Table	Relations
Organization	VTEXT	VKORG	VKORG - VBRK
Channel	VTWEG	TVTWT	VTWEG - VBRK
Office	BEZEI	TVKBT	VKBUR - VBRP
Zone	BZTXT	T171T	BZIRK - VBRK
Sector	VTEXT	TSPAT	SPART - VBRP
Plant	NAME1	T001W	WERKS - VBRP
Material Group	WGBEZ	T023T	MATKL - VBRP
Customer	NAME1	KNA1	KUNAG – VBRK

3. Data Base – View Queries

Note: Change the database name, schema and table names.

Example View: {database name}.{schema}.silver_sd_invoice

Example Table: {database name}.{schema}.{table prefix}_vbrk

3.1 Query Sales Silver View: silver_sd_invoice

CREATE OR REPLACE VIEW onibex.demo.silver_sd_invoice

COMMENT 'View Silver for sales'

SELECT

(CASE WHEN vbrk.fkdat = '00000000' THEN cast(null as date)

ELSE to_date( vbrk.fkdat, 'yyyyMMdd' ) END) as DateInvoice,

CONCAT(SUBSTRING(vbrk.fkdat,1,4),'-',SUBSTRING(vbrk.fkdat,5,2)) as MY_DateInvoice,

SUBSTRING(vbrk.fkdat,1,4) as Y_DateInvoice ,

vbrk.vkorg as OrgID,

vbrk.kurrf as ExRateID,

vbrk.vtweg as ChannelID,

vbrk.bzirk as ZoneID,

vbrp.vkbur as OfficeID,

vbrp.werks as PlantID,

vbrp.spart as SectorID ,

vbrp.matkl as GroupMatID,

vbrk.kunag as CustomerID,

CONCAT(vbrk.vkorg,vbrk.vtweg) as Territory,

vbrp.netwr as Sales,

vbrp.matnr as Material,

vbrk.waerk as Currency

from onibex.demo.oni_dev_vbrk vbrk

join onibex.demo.oni_dev_vbrp vbrp on vbrk.vbeln = vbrp.vbeln

group by

vbrp.vkbur, vbrp.werks, vbrp.matnr, vbrp.spart, vbrp.matkl, vbrp.netwr,

fkdat, vbrk.vkorg, vbrk.waerk, vbrk.kurrf, vbrk.vtweg, vbrk.bzirk, vbrk.kunag

order by MY_DateInvoice;

3.2 Query Sales Gold View: gold_bi_sd_invoice

CREATE OR REPLACE VIEW onibex.demo.gold_bi_sd_invoice

COMMENT 'View gold for sales'

AS SELECT

CAST(CONCAT(ssi.MY_DateInvoice ,"-01")as date) AS MY_DateInvoice,

ssi.Y_DateInvoice,

ssi.OrgID,

ssi.ExRateID,

ssi.ChannelID,

ssi.ZoneID ,

ssi.PlantID ,

ssi.OfficeID ,

ssi.SectorID ,

ssi.GroupMatID ,

ssi.CustomerID ,

ssi.Currency,

ssi.Material,

ssi.Territory,

sum(ssi.Sales) as monthly_sales,

tvkot.vtext as Org,

tvtwt.vtext as Channel,

t171t.bztxt as Zone,

t001w.name1 as Plant,

tvkbt.bezei as Office ,

tspat.vtext as Sector,

t023t.wgbez as GroupMaterial,

kna1.name1 as Customer

from onibex.demo.silver_sd_invoice ssi

left join onibex.demo.oni_dev_tvkot tvkot on tvkot.vkorg = ssi.OrgID

left join onibex.demo.oni_dev_tvtwt tvtwt on tvtwt.vtweg = ssi.ChannelID

left join onibex.demo.oni_dev_tvkbt tvkbt on tvkbt.vkbur = ssi.OfficeID

left join onibex.demo.oni_dev_tspat tspat on tspat.spart = ssi.SectorID

left join onibex.demo.oni_dev_t171t t171t on t171t.bzirk = ssi.ZoneID

left join onibex.demo.oni_dev_t001w t001w on t001w.werks = ssi.PlantID

left join onibex.demo.oni_dev_t023t t023t on t023t.matkl = ssi.GroupMatID

left join onibex.demo.oni_dev_kna1 kna1 on kna1.kunnr = ssi.CustomerID

where (ssi.MY_DateInvoice like '2%' or ssi.MY_DateInvoice like '19%')

group by

ssi.Y_DateInvoice, ssi.OrgID, ssi.ExRateID, ssi.ChannelID, ssi.ZoneID , ssi.PlantID ,

ssi.OfficeID , ssi.SectorID , ssi.GroupMatID , ssi.CustomerID , ssi.Currency,

ssi.Material,ssi.Territory, ssi.Sales,

tvkot.vtext, tvtwt.vtext, t171t.bztxt, t001w.name1, tvkbt.bezei, tspat.vtext, t023t.wgbez, kna1.name1

order by MY_DateInvoice;

4. Data Base - Data Example Views (Silver/Gold)

select * from onibex.demo.silver_sd_invoice;

select * from onibex.demo.gold_bi_sd_invoice;

5. Queries – Historic Data Sales and Real Time Invoice

Note: Change the database name, schema, table names and dates.

Example View: {database name}.{schema}.silver_sd_invoice

Example Table: {database name}.{schema}.{table prefix}_vbrk

Example Date: delete from {database name}.{schema}.{table prefix}_vbrk where fkdat like ‘{year}%’;

5.1 Delete history by year (vbrk table)

delete from onibex.demo.oni_dev_vbrk where fkdat like ‘2020%’;

5.2 Delete invoice in real time (vbrk table)

delete from onibex.demo.oni_dev_vbrk where fkdat like ‘202309%’;

5.3 Count for historical data by year (vbrk table)

select count(*) from onibex.demo.oni_dev_vbrk where fkdat like ‘2020%’;

5.4 Count for historical data by year (gold view)

select count(*) from onibex.demo.gold_bi_sd_invoice where MY_DateInvoice like ‘2020%’;

5.5 Show historical data by year (gold view)

select * from onibex.demo.gold_bi_sd_invoice where MY_DateInvoice like ‘2020%’;

5.6 Show invoice in real time (gold view)

select * from onibex.demo.gold_bi_sd_invoice where MY_DateInvoice like ‘2023-09’;

Related Articles
OneConnec V2 SAP Manual
OneConnect V2 SAP Integration The following manual configuration considers the fact that you have installed the version 2 of OneConnect in SAP, please make sure that you are using this version Install the Transport Install the transport request sent ...
3. One Connect - Connection
3. CONNECTION The One Connect system provides the capability to extract information from SAP and its transfer it to Kafka. This facilitates the creation of a requisite database for analysis, which serves as the foundation for generating financial ...
IDOC Configuration Manual
1. Output Message Creation for IDOCS STEP 1 Access SAP STEP 2 Go to the Transaction field and enter “NACE” in the input box. Press Enter or click the checkmark. STEP 3 Select the application that you want to configure. For this manual, we will choose ...
2. One Connect - Setup
2. SETUP In addition to setting up entries and core modules, One Connect requires the configuration of specific customer tables. These tables play a crucial role in facilitating the application’s data retrieval process from SAP and its subsequent ...
1. One Connect - Installation
Step by Step SAP Integration Manual. The following information provide a condensed overview of the comprehensive SAP Integration Manual. In this manual, you'll find detailed step-by-step instructions for seamless SAP integration. Here's a glimpse of ...

PowerBI - Sales Dashboards

PowerBI - Sales Dashboards

1. Power BI Dashboard Charts and Queries

1.1 Main Sales Dashboard

1.2 Charts - previous sales year vs current sales year

1.3 Sales Table – Sales Month/Year

1.4 Filters – Description and Date

2. SAP Mapping

2.1 Send Tables: SAP

2.2 Sales Silver View: silver_sd_invoic

2.3 Sales Gold View IDs: gold_bi_sd_invoice

2.4 Sales Gold View Descriptions: gold_bi_sd_invoice

3. Data Base – View Queries

3.1 Query Sales Silver View: silver_sd_invoice

3.2 Query Sales Gold View: gold_bi_sd_invoice

4. Data Base - Data Example Views (Silver/Gold)

5. Queries – Historic Data Sales and Real Time Invoice

5.1 Delete history by year (vbrk table)

5.2 Delete invoice in real time (vbrk table)

5.3 Count for historical data by year (vbrk table)

5.4 Count for historical data by year (gold view)

5.5 Show historical data by year (gold view)

5.6 Show invoice in real time (gold view)

Related Articles

OneConnec V2 SAP Manual

3. One Connect - Connection

IDOC Configuration Manual

2. One Connect - Setup

1. One Connect - Installation