Former Member

### SAP B1 Crystal Report for Sales Analysis Requirement Problem!

Hi Experts,

I have a created a crystal report for sales analysis per Item sold sales analysis based on my client requirements. So I Created Query like this:

```declare @d1 date = {?DateFrom@}
declare @d2 date = {?DateTo@}
SELECT
T1.ItemCode
,T1.Dscription
,SUM(T1.Quantity) AS 'QNTY'
,T2.[SalUnitMsr] AS 'UoM'
,SUM(T1.LineTotal) as 'Amount'
,SUM(T1.Quantity * T1.[GrossBuyPr]) AS 'Item Cost'
,T2.[U_JTEC_SUB_PRINCIPAL]
,CASE
WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0
ELSE SUM(IsNUll(T1.GrssProfit ,0)) / Sum(isnull(T1.LineTotal,0)) * 100
END as 'Gross Profit %'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T0.[CANCELED] = 'N' and T0.DocDate between @d1 and @d2

GROUP BY T1.ItemCode
,T1.Dscription
,T2.[SalUnitMsr]
,T2.[U_JTEC_SUB_PRINCIPAL]
UNION ALL
SELECT
T1.ItemCode
,T1.Dscription
,-SUM(T1.Quantity) AS 'QNTY'
,T2.[SalUnitMsr] AS 'UoM'
,-SUM(T1.LineTotal) as 'Amount'
,-SUM(T1.Quantity * T1.[GrossBuyPr]) AS 'Item Cost'
,T2.[U_JTEC_SUB_PRINCIPAL]
,CASE
WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0
ELSE SUM(IsNUll(T1.GrssProfit ,0)) / Sum(isnull(T1.LineTotal,0)) * 100
END as 'Gross Profit %'
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T0.[CANCELED] = 'N' and T0.DocDate between @d1 and @d2

GROUP BY T1.ItemCode
,T1.Dscription
,T2.[SalUnitMsr]
,T2.[U_JTEC_SUB_PRINCIPAL]
```

Well the query works fine! but my client want me to just summarize the total qty sold, amount sold,item cost and gross profit.

--My Query and In My Crystal ReportShow like this

Gin (Bilog) Round 350ml | 2,288 | Case | 2,079,997.00 | 1,974,544.00 | 105,453.00

Gin (Bilog) Round 350ml | 7 | pcs | 261.88 | 243.25 | 18.63

Gin (Bilog) Round 350ml | -2 | Case | -1,818.00 | -1,726.00 | -92.00

Gin (Bilog) Round 350ml | -27 | pcs | -1,019.97 | -938.25 | -81.72

Note: The Positive amount is AR INVOICES and The Negative Amounts are AR CREDIT MEMOS

My Client Just want like this

eg. on CASE

Gin (Bilog) Round 350ml | 2,286 | Case | 2,078,179.00 | 1,972,828.00 |105,351.00

But How Can I make that? I'm really Out of IDEA.

I tried to make a formula but didn't work!

any help would be appreciated!

If you want a see my report here's the RPT File download below.

https://ufile.io/hfpuf

Best Regards,

Socrates Ariola

10|10000 characters needed characters exceeded

Nov 07, 2017 at 04:42 PM

Do you have a way of knowing which item is the starting total? If so, I would modify your query to something like this:

```declare @d1 date = {?DateFrom@}
declare @d2 date = {?DateTo@}
SELECT
ItemCode,
Dscription,
sum(QNTY) as 'QNTY',
UoM,
sum(Amount) as 'Amount',
sum(ItemCost) as 'ItemCost',
sum(GrossProfit) as 'GrossProfit'
U_JTEC_SUB_PRINCIPAL
FROM (
SELECT
T1.ItemCode
,T1.Dscription
,T1.Quantity AS 'QNTY'
,T2.[SalUnitMsr] AS 'UoM'
,T1.LineTotal as 'Amount'
,T2.[U_JTEC_SUB_PRINCIPAL]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T0.[CANCELED] = 'N' and T0.DocDate between @d1 and @d2

UNION ALL
SELECT
T1.ItemCode
,T1.Dscription
,-1 * T1.Quantity) AS 'QNTY'
,T2.[SalUnitMsr] AS 'UoM'
,-1 * T1.LineTotal as 'Amount'
,-1 * T1.Quantity * T1.[GrossBuyPr] AS 'Item Cost'
,-1 * IsNUll(T1.GrssProfit ,0) as 'Gross Profit'
,T2.[U_JTEC_SUB_PRINCIPAL]

FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
WHERE T0.[CANCELED] = 'N' and T0.DocDate between @d1 and @d2
) as data

GROUP BY ItemCode
,Dscription
,UoM
,U_JTEC_SUB_PRINCIPAL
```

You didn't mention what type of database this is, but this sub-query construct will work with most databases. It will bring back just the top level of the totals.

-Dell

10|10000 characters needed characters exceeded
• Former Member

I'm Sorry

I get it!

`-1* (T1.Quantity)AS'QNTY'`

Just need a bracket :)

Very Thanks

Socrates Ariola

• Former Member
Nov 08, 2017 at 05:33 AM

Can you also help me with this?