1 2 Previous Next 16 Replies Latest reply on Feb 16, 2010 10:11 AM by Zal Parchem

    Useful SAP B1 queries

    Matt Roberts

      Hey everyone,

       

      I wrote a query the other day, and thought that, rather than using this forum just to post Q&A in response to problems, I might actually post on a regular basis some of the queries that I've developed for our company that others might find useful. I'll also post the business need or scenario that prompted us to write the query (and get the information out of the database).

       

      This first query answers the question: how many sales orders have multiple deliveries / invoices? In my company, our sales orders are 99% item marketing documents (as opposed to service) - we receive orders for items, we then ship them (Delivery), and then invoice them (Invoice) at the end of the day. We do not combine multiple sales orders to a single delivery. The only situations where we might have multiple deliveries for one sales order are when an item is out-of-stock and has to be backordered for an extended period of time (longer than a week). This results in a lower first hit rate, or the percentage of orders which can ship fully the first time.

       

      In an effort to improve our customer service, we wanted to take a look at those sales orders that generate multiple deliveries, to see if there might be some inventory adjustments that might have let this order go out the door complete (and sooner). If you wish to use this query, simply cut and paste the query text into the Query Generator window and save the query to your SBO query manager. The query is as follows:

       

      SELECT DISTINCT INV1.DocEntry AS 'Invoice',
      DLN1.DocEntry AS 'Delivery',
      RDR1.DocEntry AS 'Sales Order', RDR1.DocDate AS 'SalesOrderDate', YEAR(RDR1.DocDate) AS 'SODateYear', MONTH(RDR1.DocDate) AS 'SODateMonth'
      FROM INV1
          INNER JOIN DLN1 ON INV1.BaseEntry = DLN1.DocEntry
                              AND INV1.BaseLine = DLN1.LineNum
          INNER JOIN RDR1 ON DLN1.BaseEntry = RDR1.DocEntry
                              AND DLN1.BaseLine = RDR1.LineNum
      WHERE INV1.BaseEntry IN (
          SELECT DISTINCT DocEntry FROM DLN1 WHERE BaseEntry IN (
                  SELECT BaseEntry
                  FROM (SELECT DISTINCT ODLN.DocEntry, DLN1.BaseEntry
          FROM ODLN INNER JOIN DLN1
              ON ODLN.DocEntry = DLN1.DocEntry
          WHERE DLN1.BaseEntry IS NOT NULL) AS BaseDoc
          GROUP BY BaseEntry
          HAVING COUNT(DocEntry) > 1)
      ) AND INV1.BaseType = 15
        AND DLN1.BaseType = 17
      ORDER BY RDR1.DocEntry

       

      There are a few nested subqueries in this query, but it does run fairly quickly on our system, and we have > 100,000 each of sales orders / deliveries / invoices. This will select the Invoice number, the Delivery number, and the Sales Order number for orders that have gone from Sales Order -> Delivery -> Invoice. Orders that have gone from Sales Order directly to Invoice are excluded.

       

      As it stands, this pulls all sales orders / deliveries / invoices where a sales order has multiple deliveries, and the results are ordered by the sales order number. Should one wish to see only the documents from a particular month and / or year, it's easy to use the filter tool on the query (the funnel-shaped icon in the top menu bar in SAP) on the 'SODateYear' and 'SODateMonth' fields to narrow the results down.

       

      I'll see what other useful queries we might have lying around that are slightly above the average difficulty level for query-users. Of course, if you have a particular query that you might like to have written, I'd find it pretty fun (and fairly easy) to do so - as long as you don't mind me posting it here. Shoot me an email at matt@prestwickhouse.com and I'll see what I can do. Hope this is of some help to someone!

       

      -Matt Roberts, MCTS

      Database Marketer

      Prestwick House, Inc.

        • 1. Re: Useful SAP B1 queries
          Andee DiCola

          Thank you Matt! I think that is a great idea.

           

          Andee

          • 2. Re: Useful SAP B1 queries
            Matt Roberts

            Here is another query that some might find interesting. Hopefully I'll be able to keep putting these in at a rate of one per week, although some of these might not be as complex.

             

            The following query answers a question like this: how many customers that placed an order in one particular year returned to place an order in a subsequent year? This query will do this not among the entire customer group as a whole, but will divide this up by the SAP customer group. It is, of course, possible to group the data by other fields, such as a user-defined field, if one so wishes. This query checks invoices instead of sales orders; if one wants to check sales orders instead of invoices, I would recommend adding the stipulation that the document status (DocStatus) = 'C' (or "closed") and that the document has not been canceled (Canceled = 'N'). I suppose that it would also be possible to check only invoices that are actually paid or to somehow eliminate those customers whose invoices had to be written off because they did not pay them.

             

            SELECT OCRG.GroupName, COUNT(DISTINCT OCRD.CardCode) AS 'CustNumber' FROM OCRD
                 INNER JOIN OINV ON OCRD.CardCode = OINV.CardCode
                 INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode
            WHERE YEAR(OINV.DocDate) = '2008'
            AND OCRD.CardCode IN
            (SELECT DISTINCT T1.CardCode FROM OCRD T1
            INNER JOIN OINV T2 ON T1.CardCode = T2.CardCode
            WHERE YEAR(T2.DocDate) = '2007')
            GROUP BY OCRG.GroupName

             

            This particular example shows which customers from 2007 also placed an order in 2008. Should one wish to check different years, simply change the year number in this query - or replace the years with SAP variables (like [%0]) that prompt the user to put in the year before the query runs. One can also check to see which customers from 2007 returned yet again in 2009 by adjusting the years. This type of information is very useful for, among other things, calculating the retention rate and average lifetime value of customers that belong to a particular group. This would in turn allow one to plan marketing spending accordingly, so that one does not spend more to acquire and retain a customer than their business would actually be worth over the likely course of their relationship with you as a company.

             

            We have made the decision to automate these lifetime value queries with stored procedures / dynamic SQL in a different database that pulls sales data from SAP and merges it with sales data from our legacy system, so those queries would not be as useful to the SBO public at large, I'm afraid.

             

            -Matt Roberts, MCTS

            Database Marketer

            Prestwick House, Inc.

            • 3. Re: Useful SAP B1 queries
              Zal Parchem

              Great Idea Matt!!!

               

              Thanks for the SQL and, most importantly, the explanations you provide.  I have started to work with SQL to cull out some information not readily provided by SAP B1, and it is helpful to see how others write their SQL.

               

              Looking forward to the next SQL you post...

               

              Zal

              • 4. Re: Useful SAP B1 queries
                Matt Roberts

                This next query deals with the Incoming Payments table, and is a calculation of how long (in days) it takes one's company to receive payments on invoices. This particular version of this query will run against only the check and credit amounts, not taking into account the cash amounts - my company does not take cash payments, as we are a mail-order business and do not have a storefront. Of course, if one were to own a storefront, one would be paid immediately in almost every case, and a query like this would not be so useful. But, for many companies that extend "net x# days" terms to their customers and get subsequently paid by check, this query is helpful both to determine which customers are honoring those terms and to do cash flow planning for the future.

                 

                SELECT T0.DocNum, AVG(DATEDIFF(DAY, T1.DocDate, T0.DocDate))
                    AS 'Average Num Days', SUM(T0.CreditSum) AS 'Credit Sum',
                    SUM(T0.CheckSum) AS 'Check Sum'
                FROM ORCT T0  INNER JOIN OINV T1 ON T0.DocEntry = T1.ReceiptNum
                WHERE T0.DocDate >=[%0] and T0.DocDate <=[%1]
                GROUP BY T0.DocNum
                ORDER BY AVG(DATEDIFF(DAY, T1.DocDate, T0.DocDate)) DESC

                 

                Note first that, when this query is run, the Business One variables will require the user to input the starting and end dates for the time period for the incoming payments that they'd like to examine.

                 

                As is perhaps to be expected in most companies, the Credit Sum amounts will likely appear at the bottom, since credit card payments can be made somewhat promptly, whereas checks are often used for customers that have Net 30 /  60 / 90 payment terms. This query is ordered not by payment date, but by the number of days - so the payments for the most long-standing debts appear first. Of course, it's easy in Business One to re-order the data by columns after the query is run.

                 

                There are two SQL functions being used here that will work in the Query Generator: AVG and DATEDIFF. As their names imply, AVG collects the average of a range of numbers, and DATEDIFF returns a numerical value that compares the length of time between two dates. The "DAY" function says that this value will be marked in days; this could be substituted with MONTH or YEAR in other queries (also, WEEK, QUARTER, HOUR, SECOND, and so forth) to get the value in varying denominations. Because payments can be made for multiple invoices, this will take the average of the number of days that all items on one payment have remained outstanding.

                 

                Like SUM, MAX / MIN, and other many-to-one SQL functions, AVG requires a "GROUP BY" clause that includes all columns / data fields that are not included in a many-to-one function.

                 

                It is certainly possible to look at this on an individual invoice basis - to do this, one would need to eliminate the AVG function, the two SUM functions, and then the GROUP BY clause. This will return one line for each invoice, as opposed to each incoming payment, and each incoming payment will likely be listed multiple times. Each invoice will return the exact number of days for which payment was outstanding; the date difference will not be averaged.

                 

                -Matt Roberts, MCTS

                Database Marketer

                Prestwick House, Inc.

                • 5. Re: Useful SAP B1 queries
                  Matt Roberts

                  In a slight variation on last week's query, here is another query using date calculations. This one calculates the average lead time (in days) that it takes a vendor to deliver items / materials. This query accomplishes this goal by subtracting the absolute value (in days) of the difference between the posting dates a purchase order and a goods receipt PO, and averaging these values together.

                   

                  SELECT OPDN.CardCode, OPDN.CardName, AVG(ABS(DATEDIFF(day,OPDN.DocDate, OPOR.DocDate))) AS 'AvgLeadTime'
                  FROM  PDN1 INNER JOIN
                        OPDN ON PDN1.DocEntry = OPDN.DocEntry INNER JOIN
                        POR1 INNER JOIN
                        OPOR ON POR1.DocEntry = OPOR.DocEntry ON PDN1.BaseLine = POR1.LineNum AND PDN1.BaseEntry = POR1.DocEntry
                  GROUP BY OPDN.CardCode, OPDN.CardName

                   

                  This information can, of course, be imported back into SAP (using the data transfer workbench or some other tool) into an item code's "Planning" tab, under "Lead Time," to help with MRP calculations. One would need to also have a list of all item codes by vendor and apply the average vendor lead time to products - something that is a bit beyond the scope of this post but can be easily done in, say, Microsoft Access (or another SQL database aside from the SAP database).

                   

                  It may be, however, that one wants to do this sort of calculation by item, and not by vendor - perhaps a vendor is very slow in providing some items but is quick with others. In that case, the following query may be more useful:

                   

                  SELECT PDN1.ItemCode, AVG(ABS(DATEDIFF(day, OPDN.DocDate, POR1.DocDate)))
                  FROM PDN1 INNER JOIN POR1
                      ON PDN1.BaseLine = POR1.LineNum AND PDN1.BaseEntry = POR1.DocEntry
                      INNER JOIN OPDN ON OPDN.DocNum = PDN1.DocEntry
                  WHERE     (PDN1.ItemCode IS NOT NULL)
                  GROUP BY PDN1.ItemCode
                  ORDER BY PDN1.ItemCode

                   

                  This second query calculates this average date-difference between purchase order and goods receipt PO by individual items, not by the vendor.

                   

                  -Matt Roberts, MCTS

                  Database Marketer

                  Prestwick House, Inc.

                  • 6. Re: Useful SAP B1 queries
                    Matt Roberts

                    A somewhat simple query this Monday that answers the simple question: which open purchase orders in the system have been open for longer than two weeks?

                     

                    SELECT T0.DocNum,T0.CardCode, T0.CardName, T0.DocStatus, T0.DocDate, T0.DocTotal
                    FROM OPOR T0
                    WHERE T0.DocStatus = 'O' AND T0.CreateDate < (GETDATE()-14)
                    ORDER BY T0.CardName, T0.DocDate

                     

                    The GETDATE()-14 bit is the function that determines how old the purchase order is. If your items take longer to get to your warehouse(s), then you can adjust this number to any amount - 21 or 30 days, for instance, instead of 14 days.

                     

                    This list can be quite useful for purchasing agents to be able to see which orders they most urgently need to call about to check the status of.

                     

                    -Matt Roberts, MCTS

                    Database Marketer

                    Prestwick House, Inc

                    • 7. Re: Useful SAP B1 queries
                      Zal Parchem

                      Thanks again Matt for the queries - good stuff.  LOL - I started a discussion like this some time ago but it was sort of turned into another discussion and I had no idea where the folks were heading with it, so I stopped.

                       

                      So my question to you - would you like to have others contribute to this discussion with their own SQL or is this thread intended to be used for your postings only?

                       

                      Either way - thanks bunches - Zal

                      • 8. Re: Useful SAP B1 queries
                        Matt Roberts

                        I would love to see other peoples' queries for Business One - that's a great idea. Feel free to contribute, Zal - hopefully this thread will have lots of useful queries for everyone, and might even get "stickied" by the powers-that-be, if we get enough.

                        • 9. Re: Useful SAP B1 queries
                          Matt Roberts

                          Here's an interesting little query that I dug out of our Query Manager...I wrote this one some time ago for our customer service manager.

                           

                          This query measures the number of order lines each Business One user entered on a particular date. Back in the stone ages, we used to count the dollar value each rep processed, but they don't work on commission and which orders they get is largely luck-of-the-draw...so we obviously stopped that one. Then we used to count the number of orders that a customer service rep entered, but some rightly countered that some people might get an order with 30-50 line items on it and someone else might get twenty orders with 1 line that might be the same amount of "work."

                           

                          So this query is something we consider - along with other measures of activity (how many phone calls they field, how many emails from the website they answer) - when trying to measure for how much "work" each customer service rep is doing.

                           

                          This measures it by day, but of course you could do this by week, month, whatever. This would probably require some BETWEEN statements and two variables instead of just the one. If anyone's interested, I'll write that modification later.

                           

                          SELECT T0.OwnerCode AS 'Employee Code',T1.firstName AS 'First Name', T1.lastName AS 'Last Name', COUNT(T0.DocEntry) AS 'Number of Lines Processed'
                          FROM RDR1 T0 INNER JOIN OHEM T1 ON T0.OwnerCode = T1.empID
                          WHERE MONTH(T0.DocDate) = MONTH('[%0]') AND YEAR(T0.DocDate) = YEAR('[%0]')
                          GROUP BY T0.OwnerCode,T1.firstName, T1.lastName

                           

                           

                          -Matt Roberts, MCTS

                          Database Marketer

                          Prestwick House, Inc.

                          • 10. Re: Useful SAP B1 queries
                            Zal Parchem

                            A company needed a report on Mark Up Ratio (MUR) to review pricing strategy and to determine if they were going to adjust their prices up or down.  MUR, in its simplest terms, is an estimating average target, usually expressed in a factor, where one sees how much the cost of an item is “marked up” (increased) to cover Cost of Goods Sold AND all Expenses.  The SQL uses only two tables; INV1 (AR Invoice Line Details) and OITM (Item Master Data).  You will notice the SQL does not consider AR Credit Memos; if you want credit memos, a UNION command is required (my customer did not want them).  I included several calculations the customer found useful but they had not thought about; historical average sales price, a “break even” (suggested sales price) to reach the MUR target, and a total profit/loss variance to show how much money was made over or under the target MUR within the date range selected.  The results of the SQL are sorted with the “ORDER BY’ of highest sales dollar volume within the date range selected.

                             

                            SELECT

                             

                            T0.ItemCode AS 'Product',

                            SUM(T0.LineTotal - T0.VatSum) AS 'Total Sales Dollars',

                            CAST(SUM(T0.Quantity) AS NUMERIC) AS 'Quantity Sold',

                            (SUM(T0.LineTotal - T0.Vatsum) / SUM(T0.Quantity)) AS 'Hist Avg Sales Price',

                            T1.LstEvlPric * 1.52 AS 'Break Even Sales Price at 1.52 MUR',

                            T1.LstEvlPric AS 'Last Eval Cost',

                            (SUM(T0.LineTotal - T0.VatSum) / SUM (T0.Quantity)) / T1.LstEvlPric AS 'Mark Up Ratio',

                            ((SUM(T0.LineTotal - T0.Vatsum) / SUM(T0.Quantity)) - (T1.LstEvlPric *1.52)) * SUM (T0.Quantity) AS 'Break Even Profit Variance',

                            (SUM(T0.LineTotal - T0.Vatsum) / SUM(T0.Quantity)) - (T1.LstEvlPric *1.52) AS 'Per Product Sold Variance'

                             

                            FROM INV1 T0

                             

                            INNER JOIN OITM T1

                            ON T0.ItemCode = T1.ItemCode

                             

                            WHERE

                            T0.ItemCode IS NOT NULL

                            AND T1.LstEvlPric < > 0.0000

                            AND T0.DocDate >= '[%0]'

                            AND T0.DocDate <= '[%1]'

                             

                            GROUP BY

                             

                            T0.ItemCode,

                            T1.LstEvlPric

                             

                            ORDER BY SUM(T0.LineTotal - T0.VatSum) DESC

                             

                            This SQL has a lot of calculations going on and maybe the most important point to be learned from this SQL is the “GROUP BY” area.  An SQL Rule that causes folks lots of headaches: whenever you have a “SUM” being used in your SQL, you must have a “GROUP BY” area where you list those data fields that are NOT used in summing up numeric date; in this case, ItemCode and LstEvlPric are not used in any “SUM” statement, so they must be in the “GROUP BY”.  [Special Note: LstEvlPric is used in a calculation but NOT in a SUM function – check the SQL out again].

                             

                            How can you use this SQL in your company?  First and foremost, take your time to get a really, really good understanding of Mark Up Ratio by using the Internet or a business book – there is a big difference between MUR and Gross Profit.  Calculate your own average MUR and substitute the “1.52” in the SQL to what you have calculated.  Make sure the decimal positions for the LstEvlPrc are correct in the WHERE clause.  Run the SQL and take your results to the person responsible for the setting of sale prices.  Clearly explain the results to that person and they will quickly understand which individual items are causing a bottom line loss (below MUR) and those bringing in a profit (above MUR).  With the “Break Even Sales Price” column, they can even see what the suggested sales price should be for the product when they are doing a product pricing review.  Remember that a sales strategy might be to sell one product below MUR to “get a foot in the door” with a customer, but that another product might be higher than the MUR to make up for the loss in that first product – good sales personnel know what these facts are, so discuss the report with the sales person to probe a bit deeper into what they are interested in seeing.  There might be other calculations your sales person would find helpful; for example, the difference between the MUR suggested sales price and the historical averages sales price, or maybe the report by item group selection.  I have received quite a few changes once the entire organization understood the ramifications of MUR and the SQL results are now part of the month-end review process.  Plus, this is one SQL which has turned out to be constantly listed on the “never ending” enhancement and support log as they fine tune their sales prices!

                             

                            One sales person commented that the SQL is almost like a mini Profit/Loss Statement by each individual product which might be a good analogy to explain/think about it.

                             

                            Hope this helps you and your company…

                             

                            Matt - hope this is the kind of input you are looking for...lol - sticky or not, here they come...

                            Zal

                            • 11. Re: Useful SAP B1 queries
                              Matt Roberts

                              Didn't get a chance to post this past Monday; here's a brief query for this week. I've been trying to stay away from queries that use our particular user-defined fields, because this might not be as useful for everyone - but I'm running out of queries that don't use them. So this query might be more useful as an example than as something that could be immediately put into one's system. The modifications wouldn't be extensive, however.

                               

                              We had a problem a few years back where customer service representatives were putting items that had been marked as obsolete (OBS) or out-of-print (OOP) were being put on orders instead of the newer, updated items. Given the way things are organized when one searches for items and re-name items that are out-of-print, it takes some effort to ignore the new item names and put an obsolete item on an order.

                               

                              So this query checks to see which orders have items of a particular type on it (in this case, a user-defined field called "U_Category" on the item master table - this is checked in a subquery). Because this was used to check out individual customer service reps, this also shows the document owner.

                               

                              SELECT T0.DocNum, T0.CardCode, T0.CardName, T0.DocTotal, T0.DocStatus, T3.firstName, T3.lastName
                              FROM ORDR T0 INNER JOIN OHEM T3 ON T0.OwnerCode = T3.empID
                              WHERE T0.DocStatus <> 'C'
                              AND T0.DocNum IN (SELECT DISTINCT T1.DocEntry FROM RDR1 T1 WHERE T1.LineStatus = 'O' AND T1.ItemCode IN
                                                       (SELECT T2.ItemCode
                                                         FROM OITM T2
                                                         WHERE T2.IsCommited > 0 and T2.U_category IN ('obs','oop')
                                                        )
                                                )

                              • 12. Re: Useful SAP B1 queries
                                Matt Roberts

                                Have been out of the country for a bit (and getting over jet-lag), so will resume posting these with a somewhat simple query here:

                                 

                                 

                                SELECT COUNT(T0.DocNum) FROM ORDR T0 WHERE T0.U_Specials = 'Yes' AND  T0.DocDate >='[%0]' AND  T0.DocDate <= '[%1]'

                                 

                                 

                                This query will bring back a number, indicating the count of orders where a "special" has been used and the document date is between two numbers. The U_Specials field has but two values, yes and no, so that's that part.

                                 

                                The DocDate values in the WHERE statement use variables, and calculate whether the value is greater than or equal to the start date and less than or equal to the end date. There is a simpler way to write this query, and it is as follows:

                                 

                                SELECT COUNT(T0.DocNum) FROM ORDR T0 WHERE T0.U_Specials = 'Yes' AND T0.DocDate BETWEEN '[%0]' AND '[%1]'

                                 

                                the BETWEEN function is equivalent to using >= and <=, as it uses the dates inclusive of the beginning and ending date. Using variables also prompts the query-user to input dates that they want to see data for.

                                 

                                This is one method to use to filter queries by beginning and ending date ranges.

                                • 13. Re: Useful SAP B1 queries
                                  FARIS AL-OTAIBI

                                  Thank you for your email.

                                   

                                  I am currently on vacation Up to December 08, 2009

                                   

                                  for any Business issues you can call Hamdan N. Al-Shammari @673-3292 - 673-5286, or Email (shamhn0j), Wish you a nice day.

                                  • 14. Re: Useful SAP B1 queries

                                    Here is a Pivot Table query we use to create a report with Sales Person down the left side and Month across the top.

                                    The result is a report on written sales by salesperson by month.  This can be run from the query generator, but we embed this within the Boyum add-on and get a report that sums each column also.

                                     

                                    Rob

                                     

                                     

                                    SELECT *
                                    FROM(SELECT
                                          CASE MONTH(T0.[DocDate])
                                          WHEN 1 THEN 'January'
                                          WHEN 2 THEN 'February'
                                          WHEN 3 THEN 'March'
                                          WHEN 4 THEN 'April'
                                          WHEN 5 THEN 'May'
                                          WHEN 6 THEN 'June'
                                          WHEN 7 THEN 'July'
                                          WHEN 8 THEN 'August'
                                          WHEN 9 THEN 'September'
                                          WHEN 10 THEN 'October'
                                          WHEN 11 THEN 'November'
                                          WHEN 12 THEN 'December'
                                        END as [Month],
                                    T2.[SlpName] as [Name],
                                    T1.[LineTotal] as [Amount]
                                    FROM ORDR T0
                                    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
                                    INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
                                    INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
                                    INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
                                    Where Year(T0.[DocDate] ) = 2009 and not T0.SlpCode IN (-1, 1, 2))
                                    WorkOrders
                                    PIVOT
                                    (
                                      SUM([Amount])
                                      FOR [Month] IN (
                                        [January],[February],[March],[April],
                                        [May],[June],[July],[August],
                                        [September],[October],[November],[December]
                                      )
                                    ) AS PivotTable
                                    ORDER BY [Name]

                                    1 2 Previous Next