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 |
| iWay Software |