Skip to Content
0
Former Member
Jun 24, 2015 at 08:43 PM

Query manager - user supplied Parameters how to?

1515 Views

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)