SAP B1 Crystal Report for Sales Analysis Requirement Problem!

[Former Member]

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'
      ,SUM(IsNUll(T1.GrssProfit ,0)) as 'Gross Profit'
      ,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'
      ,-SUM(IsNUll(T1.GrssProfit ,0)) as 'Gross Profit'
      ,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

already lessen the negative value.

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

Thanks in Advance experts!

Best Regards,

Socrates Ariola

[DellSC · Accepted solution]

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'
        ,T1.Quantity * T1.[GrossBuyPr] AS 'ItemCost'
        ,IsNUll(T1.GrssProfit ,0) as 'GrossProfit'
        ,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

[Former Member · Accepted solution]

Hi Dell Stinnett-Christy,

My Database is SQL2012, I tried your modify query but seems, I has an error when I execute the query really don't why ?

see attached picture

best regards,

Socrates Ariola

[Former Member · Accepted solution]

I'm Sorry

I get it!

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

Just need a bracket 🙂

Very Thanks

Socrates Ariola

[Former Member]

Hi Dell Stinnett-Christy,

Can you also help me with this?

https://answers.sap.com/questions/348677/crystal-report-check-layout-error.html

Hoping really for it,

Best Regards,

Socrates