PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

400+ FREE Tips for you from Sterling Tulus Cemerlang (STEM) - SAP Indonesia Gold Partner, and Become SAP B1 Super User

Custom Report – Adding Golden Arrows to Queries

SAP Business One Tips Custom report Adding Golden Arrow to Query
SHARE ON


Have you ever made a custom query? example: GRPO Receipt List Query and it working, but still have difficulty when checking or seeing the documents directly one by one? whether you want to check the Business Partner, or the other because there is no golden arrow directly to the destination document.

Below is one example of the query results to add the golden arrow as desired as shown.

SAP Business One Tips - Custom Report - Adding Golden Arrows to Queries

SAP Business One Tips – Custom Report – Adding Golden Arrows to Queries

The thing that needs to be done is to create a query view made in SQL Server Management Studio, follow these step:

  1. Open the SSMS application (SQL Server Management Studio).

    SAP Business One Tips - Custom Report - Adding Golden Arrows to Queries

    SAP Business One Tips – Custom Report – Adding Golden Arrows to Queries

  2. Select the Database that is used in SAP with right-click and choose a new Query.

    SAP Business One Tips - Custom Report - Adding Golden Arrows to Queries

    SAP Business One Tips – Custom Report – Adding Golden Arrows to Queries

  3. Enter the query view (here using the example query view that is changed from Goods Receipt PO), and select execute (F5).
    Example query view for GRPO list:

    CREATE VIEW [LIST_GRPO] AS
    SELECT CASE 
                   WHEN T1.[LineNum] = ''
                           THEN T0.[DocNum]
                   ELSE NULL
                   END GRPO_Number
            ,CASE 
                   WHEN T1.[LineNum] = ''
                           THEN T0.[DocDate]
                   ELSE NULL
                   END GRPO_Date
            ,CASE 
                   WHEN T1.[LineNum] = ''
                           THEN T0.[CardCode]
                   ELSE NULL
                   END Vendor_Code
            ,CASE 
                   WHEN T1.[LineNum] = ''
                           THEN T0.[CardName]
                   ELSE NULL
                   END Vendor_Name
            ,CASE 
                   WHEN T1.[LineNum] = ''
                           AND T0.[Canceled] = 'Y'
                           THEN 'CANCELED'
                   WHEN T1.[LineNum] = ''
                           AND T0.[Canceled] = 'C'
                           THEN 'CANCELATION'
                   WHEN T1.[LineNum] = ''
                           AND T0.[Canceled] = 'N'
                           THEN ''
                   ELSE NULL
                   END CANCEL_STATUS
            ,CASE 
                   WHEN T1.[LineNum] = ''
                           THEN 1
                   ELSE T1.[LineNum] + 1
                   END LINE_NO
            ,T1.[ItemCode]
            ,T1.[Dscription]
            ,T1.[Quantity]
            ,T1.[OpenCreQty] OpenQty
            ,T1.[Price]
            ,T1.[Currency]
            ,T1.[Rate]
            ,T1.[DiscPrcnt]
            ,T1.[LineTotal]
            ,T0.[DocDate]
    FROM OPDN T0
    INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
  4. Open the SAP Business One application, and enter the query that is joined with the query view, so that the golden arrow can appear according to the document.
    Examples of queries joined with query view:

    SAP Business One Tips - Custom Report - Adding Golden Arrows to Queries

    SAP Business One Tips – Custom Report – Adding Golden Arrows to Queries

  5. Select the Execute, and enter the date that you want, select OK.

    SAP Business One Tips - Custom Report - Adding Golden Arrows to Queries

    SAP Business One Tips – Custom Report – Adding Golden Arrows to Queries

  6. The golden arrow appears.

    SAP Business One Tips - Custom Report - Adding Golden Arrows to Queries

    SAP Business One Tips – Custom Report – Adding Golden Arrows to Queries

The golden arrow can be raised by displaying a key in the query view as a column, then joined to the query used in the SAP Business One query manager.

Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , , , , , , ,