I apologized I wasn’t very clear. We needed this fields included in the report but it is not one of the available fields to choose from. So what I did was modify the stored procedures by adding a couple of fields that I need in my report. Below is the stored procedure, the ones in red are the ones I added. BBPTHEAD.ITEMCODE is the one missing.
The report consist of multiple subreports that are all using stored procedures. This is just one of the subreports.
SELECT * from (
SELECT
BBPTHEAD.LJOB
, BBPTHEAD.PARTNO
, BBPTHEAD.ITEMCODE
, BBPTHEAD.QUANTITY
, BBPTHEAD.PARTDES
, BBPTHEAD.PARTDES2
, rtrim(BBPTHEAD.PARTDES)
+ char(13)
+ rtrim(BBPTHEAD.PARTDES2)
AS PlateDescription
, SSINVENT.MATERIAL
, SSINVENT.MATNO
, BBPROCES.INKSIDES
, BBPROCES.PMSNO
, BBPROCES.PLATENO
, BBPROCES.procmats1
, BBPROCES.COUNTER
, CASE WHEN (@tlBackorder = 0) OR v_part_backorder.prevship IS NULL Then BBPTHEAD.QUANTITY
ELSE v_part_backorder.orderquan - v_part_backorder.prevship
END as PartQuantity
, RTRIM(SSINVENT.MATERIAL)
+ CASE LEN(BBPROCES.PMSNO)
WHEN 0 then ''
ELSE ' PMS ' + rtrim(BBPROCES.PMSNO)
END
+ CASE BBPROCES.INKSIDES
WHEN '1' THEN ' Side 1 '
WHEN '2' THEN ' Side 2 '
ELSE ' Both Sides '
END AS InkDescription
,RTRIM(LTRIM(SSINVENT.MATERIAL)) +
CASE len(BBPROCES.PMSNO)
when 0 THEN ''
else ' PMS ' + RTRIM(BBPROCES.PMSNO)
END
+ REPLICATE('.',150)
AS InkSummaryLine
, v_part_backorder.prevship
, v_part_backorder.orderquan
, v_part_backorder.closed
, 'Weight (' + LTRIM(dbo.UOMTermConvert('Med Weight',@tcOpt_UOM,'S',2.0,0,0,'','')) + ')' as WeightColumnHeader
, cast('' as varchar(7500)) as FieldData
,-1 as SortTop
,-1 as SortLeft
, dbo.Logical2Bit('F') as isCSF
FROM dbo.pv_bbpthead(@tiDocNo,@tcPartNo) AS BBPTHEAD
LEFT OUTER JOIN [dbo].[pv_process_group] ('I') AS BBPROCES
ON BBPTHEAD.LJOB = BBPROCES.LJOB
AND BBPTHEAD.PARTNO = BBPROCES.PARTNO
LEFT OUTER JOIN v_part_backorder AS v_part_backorder
ON BBPTHEAD.LJOB = v_part_backorder.LJOB
AND BBPTHEAD.PARTNO = v_part_backorder.PARTNO
LEFT OUTER JOIN SSINVENT AS SSINVENT
ON BBROCES.MATNO= SSINVENT.MATNO
UNION
SELECT
csf.LJOB
, csf.PARTNO
, null
, null
, null
, null
, null
, null AS PlateDescription
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, csf.FieldData
, csf.SortTop
, csf.SortLeft
, csf.isCSF
FROM dbo.pv_BBcsf_jticket(@tiDocNo,@tcPartNo,'I',DEFAULT) csf
WHERE FIELDDATA IS NOT NULL
) D
ORDER BY PARTNO, iscsf, COUNTER, sorttop, sortleft
END
GO
IP Logged
You need to click on the Database menu and choose Verify Datasource. This will read the stored procedure again and refresh the list of fields that are in it.
