Custom Report – Adding Golden Arrows to Queries

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.

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

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

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

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

   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

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.

Also read:

• Golden Arrow in SAP Crystal Reports
• Query Price List in SAP Business One
• Add New Tab to Your Screen Without UI API
• Quick Access To Queries