- SAP Community
- Products and Technology
- Enterprise Resource Planning
- ERP Q&A
- HELP IN THIS QUERY (ITEM PROPERTY SALES ANALYSIS)

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

HELP IN THIS QUERY (ITEM PROPERTY SALES ANALYSIS)

former_member752097

Explorer

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 01-22-2022 3:51 AM

259

HELLO EXPERTS,

IN THE BELOW QUERY I HAVE SOME PROBLEM LIKE IN MY ITEM MASTER I HAVE SELECT TWO ITEM PROPERTY IN IT. BUT IN THIS QUERY ITS NOT SHOWING THE SECOND ITEM PROPERTY.

PLEASE RESOLVE THIS.

Select T0.[DocNum],

T0.[DocDate],

Case when T0.[ObjType] = 13 then 'INVOICE' when T0.[ObjType] = 14 then 'CREDIT NOTE' end as 'Type',

T0.[CardCode],

T0.[CardName],

T1.[ItemCode],

T1.[Dscription],

T1.[Quantity],

T1.[LineTotal] as 'Total Sale',

Case when T1.[Rate] = 0 then T1.[LineTotal] else (T1.[LineTotal]/T1.[Rate]) end as 'Equivalent Value',

Case when (T1.[Quantity] = 0 and (T1.[StockPrice]*T1.[Quantity])= 0 ) then T1.[LineTotal] else (T1.[INMPrice]*T1.[Quantity]) end as 'Sales - Net',

(T1.[StockPrice]*T1.[Quantity]) as 'Cost',

T1.[GrssProfit],

Case when (T1.[INMPrice]*T1.[Quantity]) =0 then 0 else (T1.[GrssProfit]/(T1.[INMPrice]*T1.[Quantity]))*100 end as 'GP %age'

, T3.ItmsGrpNam

FROM OINV T0

INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

LEFT JOIN OITG T3 ON CASE WHEN ISNULL(T2.QryGroup1, '') = 'Y' THEN 1 WHEN ISNULL(T2.QryGroup2, '') = 'Y' THEN 2 WHEN ISNULL(T2.QryGroup3, '') = 'Y' THEN 3 WHEN ISNULL(T2.QryGroup4, '') = 'Y' THEN 4 WHEN ISNULL(T2.QryGroup5, '') = 'Y' THEN 5 WHEN ISNULL(T2.QryGroup6, '') = 'Y' THEN 6 WHEN ISNULL(T2.QryGroup7, '') = 'Y' THEN 7 WHEN ISNULL(T2.QryGroup8, '') = 'Y' THEN 8 WHEN ISNULL(T2.QryGroup9, '') = 'Y' THEN 9 WHEN ISNULL(T2.QryGroup10, '') = 'Y' THEN 10 WHEN ISNULL(T2.QryGroup11, '') = 'Y' THEN 11 WHEN ISNULL(T2.QryGroup12, '') = 'Y' THEN 12 WHEN ISNULL(T2.QryGroup13, '') = 'Y' THEN 13 WHEN ISNULL(T2.QryGroup14, '') = 'Y' THEN 14 WHEN ISNULL(T2.QryGroup15, '') = 'Y' THEN 15 WHEN ISNULL(T2.QryGroup16, '') = 'Y' THEN 16 WHEN ISNULL(T2.QryGroup17, '') = 'Y' THEN 17 WHEN ISNULL(T2.QryGroup18, '') = 'Y' THEN 18 WHEN ISNULL(T2.QryGroup19, '') = 'Y' THEN 19 WHEN ISNULL(T2.QryGroup20, '') = 'Y' THEN 20 WHEN ISNULL(T2.QryGroup21, '') = 'Y' THEN 21 WHEN ISNULL(T2.QryGroup22, '') = 'Y' THEN 22 WHEN ISNULL(T2.QryGroup23, '') = 'Y' THEN 23 WHEN ISNULL(T2.QryGroup24, '') = 'Y' THEN 24 WHEN ISNULL(T2.QryGroup25, '') = 'Y' THEN 25 WHEN ISNULL(T2.QryGroup26, '') = 'Y' THEN 26 WHEN ISNULL(T2.QryGroup27, '') = 'Y' THEN 27 WHEN ISNULL(T2.QryGroup28, '') = 'Y' THEN 28 WHEN ISNULL(T2.QryGroup29, '') = 'Y' THEN 29 WHEN ISNULL(T2.QryGroup30, '') = 'Y' THEN 30 WHEN ISNULL(T2.QryGroup31, '') = 'Y' THEN 31 WHEN ISNULL(T2.QryGroup32, '') = 'Y' THEN 32 WHEN ISNULL(T2.QryGroup33, '') = 'Y' THEN 33 WHEN ISNULL(T2.QryGroup34, '') = 'Y' THEN 34 WHEN ISNULL(T2.QryGroup35, '') = 'Y' THEN 35 WHEN ISNULL(T2.QryGroup36, '') = 'Y' THEN 36 WHEN ISNULL(T2.QryGroup37, '') = 'Y' THEN 37 WHEN ISNULL(T2.QryGroup38, '') = 'Y' THEN 38 WHEN ISNULL(T2.QryGroup39, '') = 'Y' THEN 39 WHEN ISNULL(T2.QryGroup40, '') = 'Y' THEN 40 WHEN ISNULL(T2.QryGroup41, '') = 'Y' THEN 41 WHEN ISNULL(T2.QryGroup42, '') = 'Y' THEN 42 WHEN ISNULL(T2.QryGroup43, '') = 'Y' THEN 43 WHEN ISNULL(T2.QryGroup44, '') = 'Y' THEN 44 WHEN ISNULL(T2.QryGroup45, '') = 'Y' THEN 45 WHEN ISNULL(T2.QryGroup46, '') = 'Y' THEN 46 WHEN ISNULL(T2.QryGroup47, '') = 'Y' THEN 47 WHEN ISNULL(T2.QryGroup48, '') = 'Y' THEN 48 WHEN ISNULL(T2.QryGroup49, '') = 'Y' THEN 49 WHEN ISNULL(T2.QryGroup50, '') = 'Y' THEN 50 WHEN ISNULL(T2.QryGroup51, '') = 'Y' THEN 51 WHEN ISNULL(T2.QryGroup52, '') = 'Y' THEN 52 WHEN ISNULL(T2.QryGroup53, '') = 'Y' THEN 53 WHEN ISNULL(T2.QryGroup54, '') = 'Y' THEN 54 WHEN ISNULL(T2.QryGroup55, '') = 'Y' THEN 55 WHEN ISNULL(T2.QryGroup56, '') = 'Y' THEN 56 WHEN ISNULL(T2.QryGroup57, '') = 'Y' THEN 57 WHEN ISNULL(T2.QryGroup58, '') = 'Y' THEN 58 WHEN ISNULL(T2.QryGroup59, '') = 'Y' THEN 59 WHEN ISNULL(T2.QryGroup60, '') = 'Y' THEN 60 END = ItmsTypCod

WHERE

T0.[CANCELED] = 'N' and

T0.[DocDate] >= [%0] and

T0.[DocDate] <=[%1] and

T1.[LineTotal] <> '0'

- SAP Managed Tags:
- SQL,
- SAP Business One

jitin_chawla

Product and Topic Expert

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-24-2022
5:21 AM

Hi,

I think this query needs to be re-written as this would only bring 1 result if multiple checkbox are marked.

May be check query from the following thread and see if works for you

https://answers.sap.com/questions/7823861/query-for-item-properties.html

Kr,

Jitin

former_member752097

Explorer

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

01-24-2022
8:47 AM

HELLO JITIN CHAWALA,

THANKS A LOT FOR YOU HELP THIS QUERY WORKS FINE FOR ME...BUT COULD YOU HELP TO ADD THE TOTAL SALE OF THE ITEM PROPERTY WITH **DATE RANGE OPTION, OCRD CARDCODE, OCRD CARDNAME** AND **OCQG GROUPNAME**

THANKS

select src.ItemCode,src.ItemName,src.ItmsGrpNam

from

(select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup1 = 'Y' and OITG.ItmsTypCod = 1

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup2 = 'Y' and OITG.ItmsTypCod = 2

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup3 = 'Y' and OITG.ItmsTypCod = 3

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup4 = 'Y' and OITG.ItmsTypCod = 4

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup5 = 'Y' and OITG.ItmsTypCod = 5

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup6 = 'Y' and OITG.ItmsTypCod = 6

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup7 = 'Y' and OITG.ItmsTypCod = 7

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup8 = 'Y' and OITG.ItmsTypCod = 8

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup9 = 'Y' and OITG.ItmsTypCod = 9

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup10 = 'Y' and OITG.ItmsTypCod = 10

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup11 = 'Y' and OITG.ItmsTypCod = 11

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup12 = 'Y' and OITG.ItmsTypCod = 12

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup13 = 'Y' and OITG.ItmsTypCod = 13

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup14 = 'Y' and OITG.ItmsTypCod = 14

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup15 = 'Y' and OITG.ItmsTypCod = 15

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup16 = 'Y' and OITG.ItmsTypCod = 16

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup17 = 'Y' and OITG.ItmsTypCod = 17

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup18 = 'Y' and OITG.ItmsTypCod = 18

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup19 = 'Y' and OITG.ItmsTypCod = 19

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup20 = 'Y' and OITG.ItmsTypCod = 20

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup21 = 'Y' and OITG.ItmsTypCod = 21

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup22 = 'Y' and OITG.ItmsTypCod = 22

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup23 = 'Y' and OITG.ItmsTypCod = 23

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup24 = 'Y' and OITG.ItmsTypCod = 24

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup25 = 'Y' and OITG.ItmsTypCod = 25

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup26 = 'Y' and OITG.ItmsTypCod = 26

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup27 = 'Y' and OITG.ItmsTypCod = 27

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup28 = 'Y' and OITG.ItmsTypCod = 28

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup29 = 'Y' and OITG.ItmsTypCod = 29

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup30 = 'Y' and OITG.ItmsTypCod = 30

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup31 = 'Y' and OITG.ItmsTypCod = 31

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup32 = 'Y' and OITG.ItmsTypCod = 32

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup33 = 'Y' and OITG.ItmsTypCod = 33

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup34 = 'Y' and OITG.ItmsTypCod = 34

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup35 = 'Y' and OITG.ItmsTypCod = 35

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup36 = 'Y' and OITG.ItmsTypCod = 36

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup37 = 'Y' and OITG.ItmsTypCod = 37

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup38 = 'Y' and OITG.ItmsTypCod = 38

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup39 = 'Y' and OITG.ItmsTypCod = 39

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup40 = 'Y' and OITG.ItmsTypCod = 40

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup41 = 'Y' and OITG.ItmsTypCod = 41

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup42 = 'Y' and OITG.ItmsTypCod = 42

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup43 = 'Y' and OITG.ItmsTypCod = 43

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup44 = 'Y' and OITG.ItmsTypCod = 44

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup45 = 'Y' and OITG.ItmsTypCod = 45

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup46 = 'Y' and OITG.ItmsTypCod = 46

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup47 = 'Y' and OITG.ItmsTypCod = 47

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup48 = 'Y' and OITG.ItmsTypCod = 48

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup49 = 'Y' and OITG.ItmsTypCod = 49

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup50 = 'Y' and OITG.ItmsTypCod = 50

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup51 = 'Y' and OITG.ItmsTypCod = 51

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup52 = 'Y' and OITG.ItmsTypCod = 52

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup53 = 'Y' and OITG.ItmsTypCod = 53

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup54 = 'Y' and OITG.ItmsTypCod = 54

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup55 = 'Y' and OITG.ItmsTypCod = 55

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup56 = 'Y' and OITG.ItmsTypCod = 56

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup57 = 'Y' and OITG.ItmsTypCod = 57

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup58 = 'Y' and OITG.ItmsTypCod = 58

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup59 = 'Y' and OITG.ItmsTypCod = 59

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup60 = 'Y' and OITG.ItmsTypCod = 60

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup61 = 'Y' and OITG.ItmsTypCod = 61

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup62 = 'Y' and OITG.ItmsTypCod = 62

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup63 = 'Y' and OITG.ItmsTypCod = 63

union all

select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam

from OITM CROSS JOIN OITG

where OITM.QryGroup64 = 'Y' and OITG.ItmsTypCod = 64) src

order by src.ItemCode

Ask a Question

Related Content

- Joule for SAP S/4HANA Private Cloud Edition - A Comprehensive Setup Guide in Enterprise Resource Planning Blogs by SAP
- SAP ERP to SAP S/4HANA Delta Scope in Enterprise Resource Planning Blogs by SAP
- Manufacturing in SAP S/4HANA Cloud Public Edition 2408 in Enterprise Resource Planning Blogs by SAP
- Advanced Solution Bundling for Solution Order Management in SAP S/4HANA Cloud, Public Edition in Enterprise Resource Planning Blogs by SAP
- GRC, Trade, and Tax with SAP S/4HANA Cloud 2408 in Enterprise Resource Planning Blogs by SAP

Top Q&A Solution Author

User | Count |
---|---|

91 | |

12 | |

8 | |

7 | |

6 | |

6 | |

5 | |

5 | |

4 | |

4 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.