cancel
Showing results for 
Search instead for 
Did you mean: 

Query manager - user supplied Parameters how to?

Former Member
0 Kudos

Good afternoon.  Our company is a new client for SAP Business One and I have been handed several SQL queries that I need to turn in to "reports" within SAP B1.  These queries will also need to to be delivered to our zedsuite portals (hence why I need to deliver them through the query manager, so that i can then publish them to the B1 Web API.

Several of these reports have user supplied parameters that need to be used.

The two parameters that I need to query the user for are:

  • a "School Year field" - ideally a single select list i.e. 2014-2015 or 2015-16
  • A student username - free text input field.

How do I do 'convert' this SQL query so that it can be used in the query manager and then delivered to the zedSuite web API - specifically, how do I define these parameters??

I have a Crystal version of this report that works for internal people BUT  zedsuite cannot display the crystal reports - hence my need to develop a second version of the report for delivery to the portal.

Sample Query:

SELECT DISTINCT

    CRD1.U_V33_SHP2_STUD_ID AS StudentID,

    @schoolYear AS SchoolYear,

    ORDR.DocNum AS OrderNumber,

    'Sales Order' AS ObjectType,

    ORDR.DocDate As DocDate,

    CRD1.U_V33_SHP2_STUD_ID AS CustomerNumber,

    CRD1.U_V33_SHP2_STUD_LNAM + ', ' + CRD1.U_V33_SHP2_STUD_FNAM AS CustomerName,

    RDR1.Quantity AS QuantityOrdered,

    RDR1.OpenQty AS QuantityBackordered,

    ORDR.DocNum AS InvoiceNumber,

    ORDR.DocDate AS InvoiceDate,

    RDR1.ItemCode AS ItemCode,

    RDR1.Dscription AS ItemDescription,

    ship.U_TrackNum AS FedExTrackingNumber,

    RDR1.SerialNum AS SerialNumber

FROM [@V33_SHIP_PLD] ship

    join ORDR on ship.U_DocNum=ORDR.DocNum

    join RDR1 on ORDR.DocEntry=RDR1.DocEntry

    join CRD1 on ORDR.CardCode=CRD1.CardCode AND ORDR.ShipToCode=CRD1.Address

WHERE ORDR.CardCode = 'CPACYBER'

    AND CRD1.U_V33_SHP2_STUD_ID = @studentID

    AND ORDR.DocDate >= CONVERT(datetime, LEFT(@schoolYear, 4) + '-07-01 00:00:00', 120)

    AND ORDR.DocDate <= CONVERT(datetime, RIGHT(@schoolYear, 4) + '-06-30 23:59:59.998', 120)

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

Hi,

Hope this gives you idea on how to do it.

DECLARE @schoolYear as VarChar, @studentID as VarChar

SET @schoolYear = (SELECT a.DocDate FROM ORDR A WHERE A.DocDate = [%0])

SET @studentID = (SELECT a.CardName FROM ORDR A WHERE A.CardName = [%1])

SELECT DISTINCT

    CRD1.U_V33_SHP2_STUD_ID AS StudentID,

    @schoolYear AS SchoolYear,

    ORDR.DocNum AS OrderNumber,

    'Sales Order' AS ObjectType,

    ORDR.DocDate As DocDate,

    CRD1.U_V33_SHP2_STUD_ID AS CustomerNumber,

    CRD1.U_V33_SHP2_STUD_LNAM + ', ' + CRD1.U_V33_SHP2_STUD_FNAM AS CustomerName,

    RDR1.Quantity AS QuantityOrdered,

    RDR1.OpenQty AS QuantityBackordered,

    ORDR.DocNum AS InvoiceNumber,

    ORDR.DocDate AS InvoiceDate,

    RDR1.ItemCode AS ItemCode,

    RDR1.Dscription AS ItemDescription,

    ship.U_TrackNum AS FedExTrackingNumber,

    RDR1.SerialNum AS SerialNumber

FROM [@V33_SHIP_PLD] ship

    join ORDR on ship.U_DocNum=ORDR.DocNum

    join RDR1 on ORDR.DocEntry=RDR1.DocEntry

    join CRD1 on ORDR.CardCode=CRD1.CardCode AND ORDR.ShipToCode=CRD1.Address

WHERE ORDR.CardCode = 'CPACYBER'

    AND CRD1.U_V33_SHP2_STUD_ID = @studentID

    AND ORDR.DocDate >= CONVERT(datetime, LEFT(@schoolYear, 4) + '-07-01 00:00:00', 120)

    AND ORDR.DocDate <= CONVERT(datetime, RIGHT(@schoolYear, 4) + '-06-30 23:59:59.998', 120)

Regards,

Answers (0)