The following requests and output are examples of the capabilities of the Adapter for SAP BW using standard ANSI SQL code.
These examples require that you have created a synonym on the relevant tables from the Web Console. You can use the Web Console to navigate the SAP application hierarchy by searching the relevant tables or by doing a simple search by name.
Note: SQL does not support hierarchical reporting (BY HIERARCHY).
The following syntax retrieves values from an SAP BW database:
SELECT SUM(DISBURSEMENT),ADDRESS1_LEVEL_01 FROM ZPAY4 GROUP BY ADDRESS1_LEVEL_01 ORDER BY ADDRESS1_LEVEL_01;
The output is:
|
address1 Level 01 |
disbursement |
|---|---|
|
1106 BROADWAY |
132,242.33 |
|
11607 PACIFIC BLVD |
9,174.96 |
|
11607 TREMONT AVENUE |
14,849.56 |
|
1200 RIVERDALE |
9,983.99 |
|
187 WEST 39TH ST |
9,209.97 |
|
2601 8TH AVENUE |
26,274.65 |
|
31-18 SOUTHERN BLVD |
1,165.64 |
|
719 SEVENTH STREET |
10,800.00 |
|
DRAWER 4, COOPER STA |
21,406.32 |
|
P.O. BOX 1206 |
53,636.74 |
|
P.O. BOX 17616 |
6,703.90 |
The following syntax selects items from a hierarchy in MARA:
SELECT SUM(SALES_VOLUME),SUM(INCOMING_ORDERS), PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_01, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_02 FROM ZBIGQ GROUP BY PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_01, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_02 ORDER BY PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_01, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_02
The output is:
|
Product Hierarchy for material MARA Level 01 |
Product Hierarchy for material MARA Level 02 |
Sales Volume |
Incoming Orders |
|---|---|---|---|
|
Foods |
Bakery product; configured to order |
.00 |
400.00 |
|
Hardware |
PCs |
85,159,860.98 |
86,754,997.71 |
|
Printer |
.00 |
60,011.10 |
|
|
Lighting |
Bulbs |
62,235,898.00 |
62,674,320.00 |
|
Machines |
Pumps |
44,328,485.55 |
46,231,307.55 |
|
Paints |
Gloss paints |
.00 |
125.03 |
|
Services |
Maintenance |
291,562.54 |
485,315.83 |
|
Vehicles |
Cars |
155,640.00 |
155,640.00 |
|
Motorcycles |
41,761,619.76 |
42,494,811.00 |
The following syntax retrieves a complete hierarchy path from MARA:
SELECT SUM(SALES_VOLUME),SUM(INCOMING_ORDERS), PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_01, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_02, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_03, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_04 FROM ZBIGQ GROUP BY PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_01, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_02, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_03, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_04 ORDER BY PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_01, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_02, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_03, PRODUCT_HIERARCHY_FOR_MATERIAL_MARA_LEVEL_04;
The output is:
|
Product Hierarchy for material MARA Level 01 |
Product Hierarchy for material MARA Level 02 |
Product Hierarchy for material MARA Level 03 |
Product Hierarchy for material MARA Level 04 |
Sales Volume |
Incoming Orders |
|---|---|---|---|---|---|
|
Foods |
Bakery product; configured to order |
|
|
.00 |
400.00 |
|
Hardware |
PCs |
Drive |
Harddisk 1080 MB / SCSI-2-Fast |
4,234,580.64 |
4,094,612.64 |
|
Harddisk 2113 MB / ATA-2 |
5,216,171.71 |
5,216,171.71 |
|||
|
Harddisk 2149 MB / SCSI-2-Fast |
4,727,005.36 |
4,713,724.40 |
|||
|
Harddisk 4294 MB / SCSI-2-Fast |
5,132,812.80 |
5,132,812.80 |
|||
|
Input device |
Professional keyboard - MAXITEC Model |
720,022.65 |
714,750.89 |
||
|
Professional keyboard - NATURAL Model |
813,765.24 |
788,838.81 |
|||
|
Professional keyboard - PROFITEC Model |
576,079.36 |
576,079.36 |
|||
|
Standard Keyboard - EURO Model |
620,294.21 |
600,911.21 |
|||
|
Standard Keyboard - EURO-Special Model |
633,094.19 |
631,433.36 |
|||
|
Memory |
SIM-Module 16M x 32, 70 ns |
808,663.10 |
808,663.10 |
||
|
SIM-Module 4M x 36, 70 ns |
348,434.93 |
337,138.13 |
|||
|
SIM-Module 8M x 32, PS/2-72 Pin EDO-RAM |
562,619.24 |
561,208.39 |
|||
|
SIM-Module 8M x 36, 70 ns |
373,794.70 |
373,794.70 |
|||
|
Monitor |
Flatscreen LE 50 P |
1,116,349.10 |
1,155,586.30 |
||
|
Flatscreen LE 64P |
1,749,563.60 |
1,749,563.60 |
|||
|
Flatscreen MS 1460 P |
2,525,626.85 |
2,704,385.95 |
|||
|
Flatscreen MS 1575P |
2,375,663.66 |
2,469,023.66 |
|||
|
Flatscreen MS 1585 |
3,365,928.24 |
3,487,309.32 |
|||
|
Flatscreen MS 1775P |
3,809,366.74 |
4,074,466.82 |
|||
|
Flatscreen MS 1785P |
4,722,113.10 |
4,722,113.10 |
|||
|
Jotachi SN4000 |
4,396,902.60 |
4,572,792.60 |
|||
|
Jotachi SN4500 |
2,473,317.57 |
2,647,587.95 |
|||
|
Jotachi SN5000 |
2,949,233.80 |
2,949,233.80 |
|||
|
MAG DX 15F/Fe |
2,437,207.85 |
2,521,555.85 |
|||
|
MAG DX 17F |
2,567,506.69 |
2,655,601.89 |
|||
|
MAG PA/DX 175 |
2,976,662.21 |
3,181,471.75 |
|||
|
PAQ MONITOR, 17", Color |
683,666.40 |
685,463.40 |
|||
|
PAQ Monitor, 20", Color |
1,371,234.90 |
1,371,234.90 |
|||
|
SEC Multisync XV 17 |
3,557,594.77 |
3,690,080.79 |
|||
|
SEC Multisync XV15 |
2,961,072.00 |
2,961,072.00 |
|||
|
Sunny Extreme |
3,215,185.42 |
3,339,353.42 |
|||
|
Sunny Tetral3 |
2,996,901.84 |
2,996,901.84 |
|||
|
Sunny Xa1 |
2,830,258.78 |
3,026,866.52 |
|||
|
PC ensemble |
Maxitec-R 3100 Personal Computer |
3,616,021.08 |
3,600,521.08 |
||
|
Maxitec-R 375 personal computer |
194,520.00 |
194,520.00 |
|||
|
Processor |
Motherboard 3100 |
.00 |
18,558.62 |
||
|
Processor 100 MHz |
401,608.95 |
401,608.95 |
|||
|
Processor 133 MHz |
15,949.50 |
11,574.50 |
|||
|
Processor 166 MHz |
1,083,067.20 |
1,016,409.60 |
|||
|
Printer |
Laser printer |
High Speed Printer |
.00 |
60,011.10 |
|
|
Lighting |
Bulbs |
Light Bulb 40 Watt clear 220/235V |
|
15,351,358.00 |
15,351,358.00 |
|
Light Bulb 40 Watt frosted 220/235V |
|
5,432,920.00 |
5,256,060.00 |
||
|
Light Bulb 40 Watt red 220/235V |
|
2,976,965.00 |
3,197,425.00 |
||
|
Light Bulb 40 Watt yellow 220/235V |
|
3,032,064.00 |
2,917,008.00 |
||
|
Light Bulb 60 Watt clear 220/235V |
|
6,312,877.00 |
6,731,493.00 |
||
|
Light Bulb 60 Watt frosted 220/235V |
|
6,115,488.00 |
5,902,704.00 |
||
|
Light Bulb 60 Watt red 220/235V |
|
2,775,919.00 |
2,975,767.00 |
||
|
Light Bulb 60 Watt yellow 220/235V |
|
2,958,303.00 |
2,850,624.00 |
||
|
Light Bulb 80 Watt clear 220/235V |
|
7,095,740.00 |
7,589,438.00 |
||
|
Light Bulb 80 Watt frosted 220/235V |
|
5,120,425.00 |
4,935,200.00 |
||
|
Light Bulb 80 Watt red 220/235V |
|
2,416,380.00 |
2,416,380.00 |
||
|
Light Bulb 80 Watt yellow 220/235V |
|
2,647,459.00 |
2,550,863.00 |
||
|
Machines |
Pumps |
Special pump |
Pump GG IDESNORM 100-200 |
5,798,946.55 |
6,191,646.55 |
|
Pump cast steel IDESNORM 150-200 |
6,721,540.00 |
6,962,580.00 |
|||
|
Pump cast steel IDESNORM 170-230 |
9,558,837.00 |
9,565,269.00 |
|||
|
Pump chrome-steel IDESNORM 150-200 |
475,602.00 |
495,702.00 |
|||
|
Pump standard IDESNORM 100-402 |
6,893,740.00 |
7,336,070.00 |
|||
|
pump CR IDESNORM 150-200 ATO |
7,822,280.00 |
8,343,320.00 |
|||
|
pump sphere-cast IDESNORM 150-200 |
7,057,540.00 |
7,336,720.00 |
|||
|
Paints |
Gloss paints |
Opaque |
Coating Matt Green RAL 6014/10 Liter |
.00 |
125.03 |
|
Services |
Maintenance |
HiTech maintenance |
PC Service (Configurable) |
171,992.54 |
285,995.83 |
|
PC Service Plus |
119,570.00 |
199,320.00 |
|||
|
Vehicles |
Cars |
Car (complete) |
SAPSOTA FUN DRIVE 2000GT |
155,640.00 |
155,640.00 |
|
Motorcycles |
Accessories |
Motorcycle Helmet - Standard |
3,651,535.18 |
3,800,706.24 |
|
|
Components |
Deluxe Gas Tank Striping Decals |
658,897.52 |
703,373.20 |
||
|
Deluxe Headlight |
1,609,718.67 |
1,675,515.07 |
|||
|
Deluxe Taillight |
546,588.47 |
579,120.40 |
|||
|
Motor-cycle (compl.) |
CrossFun / 350 cm3 |
10,842,211.75 |
11,243,347.19 |
||
|
IDES Glad Boy configurable |
929,800.00 |
953,830.00 |
|||
|
SunFun / 1200 cm3 |
23,522,868.17 |
23,538,918.90 |
| WebFOCUS | |
|
Feedback |