BMS-Imprint
User guide
×
  (c) BMS365, 2023

2. Report Templates

 
There are a few conventions that must be followed in the SSRS report templates in order for this tool to work properly.
 
  • The BMS-Imprint app recognizes the query inside the SSRS report template and shows all the fields in a grid within the user interface.
  • The report must contain Only 1 Generic Parameter.
  • There must be a field called "UNIQUE_ID" and it should be unique as it acts as a Key for this tool to render the required report/label once a row is selected in the main grid on the screen.
  • The "UNIQUE_ID" field should be also used as parameter which will be used by the tool to render the report and this parameter must be part of a ISNULL function. See example below.
     
     
     
 
  • There should be a field called "BARCODE_IMAGE" which can be used by the tool to pass the image of a barcode to the SSRS report.
  • There should be a field called "DATA_FOR_GENERATING_BARCODE_IMAGE_IMAGE" which can be used by the tool to pass the image of a barcode to the SSRS report.
  • The actual barcode in the report can be based on the image generated by the app or alternatively, created in the report by using a barcode font.
  • The fields shown in the main grid are defined in the SSRS report template internal query.
 
 
 
Where clause example:
WHERE (INVENTORY_TRANS.TRANSACTION_DATE >= ISNULL(@TRANSACTION_DATE, INVENTORY_TRANS.TRANSACTION_DATE)) AND (INVENTORY_TRANS.TRANSACTION_ID = ISNULL(@UNIQUE_ID,INVENTORY_TRANS.TRANSACTION_ID))
 
Note: The special use of the @UNIQUE_ID field inside a ISNULL function means that it will not have any effect if this parameter is null. The same concept also applies to the other generic parameters.
 
Barcode types
 
  •  Code39
  •  Code39Extended
  •  Code11
  •  Codabar
  •  Code32
  •  Code93
  •  Code93Extended
  •  Code128A
  •  Code128B
  •  Code128C
  •  DataMatrix
  •  QRBarcode
  •  UpcBarcode
 
Alternative barcode types such as Code128, QR or Datamatrix can be used in those situations where the field that will be barcoded contains special characters not supported by the traditional Code39 or ir very long and the barcode itselft does not fit in the label.
 
 
 
Report templates used by this module
 
 
The grid shown in the Label Generator module is built automatically based on the fields included in the query within the label report.
Query Examples:
 
 
Example 1:
--Declare  @TRANSACTION_ID as int
--Set  @TRANSACTION_ID = null
 
--Declare  @UNIQUE_ID as int
--Set  @UNIQUE_ID = null
 
 
SELECT        PART.ID, PART.DESCRIPTION, ISNULL(TRACE_INV_TRANS.TRACE_ID, '-') AS TRACE_ID, INVENTORY_TRANS.TRANSACTION_ID, INVENTORY_TRANS.WAREHOUSE_ID, INVENTORY_TRANS.LOCATION_ID,
                         INVENTORY_TRANS.TRANSACTION_DATE, PART.STOCK_UM, INVENTORY_TRANS.WORKORDER_BASE_ID, INVENTORY_TRANS.WORKORDER_LOT_ID, INVENTORY_TRANS.WORKORDER_SPLIT_ID,
                         INVENTORY_TRANS.WORKORDER_SUB_ID, TRACE.COMMENTS, TRACE.EXPIRATION_DATE, INVENTORY_TRANS.PURC_ORDER_ID, INVENTORY_TRANS.PURC_ORDER_LINE_NO, '' AS BARCODE,
                         ZZZ.WORKORDER_BASE_ID AS WORKORDER_BASE_ID_Aux, ZZZ.WORKORDER_LOT_ID AS WORKORDER_LOT_ID_Aux, ZZZ.WORKORDER_SPLIT_ID AS WORKORDER_SPLIT_ID_Aux,
                         ZZZ.WORKORDER_SUB_ID AS WORKORDER_SUB_ID_Aux, ZZZ.PURC_ORDER_ID AS PURC_ORDER_ID_Aux, ZZZ.PURC_ORDER_LINE_NO AS PURC_ORDER_LINE_NO_Aux,
                         INVENTORY_TRANS.TRANSACTION_ID AS UNIQUE_ID, '' AS BARCODE_IMAGE, '' AS DATA_FOR_GENERATING_BARCODE_IMAGE
FROM            TRACE_INV_TRANS LEFT OUTER JOIN
                             (SELECT        TRACE_INV_TRANS_1.PART_ID, TRACE_INV_TRANS_1.TRACE_ID, MAX(TRACE_INV_TRANS_1.TRANSACTION_ID) AS TRX, INVENTORY_TRANS_1.WORKORDER_BASE_ID,
                                                         INVENTORY_TRANS_1.WORKORDER_LOT_ID, INVENTORY_TRANS_1.WORKORDER_SPLIT_ID, INVENTORY_TRANS_1.WORKORDER_SUB_ID, INVENTORY_TRANS_1.PURC_ORDER_ID,
                                                         INVENTORY_TRANS_1.PURC_ORDER_LINE_NO
                               FROM            TRACE_INV_TRANS AS TRACE_INV_TRANS_1 INNER JOIN
                                                         INVENTORY_TRANS AS INVENTORY_TRANS_1 ON TRACE_INV_TRANS_1.TRANSACTION_ID = INVENTORY_TRANS_1.TRANSACTION_ID
                               WHERE        (INVENTORY_TRANS_1.TYPE = 'I') AND (INVENTORY_TRANS_1.CLASS = 'R')
                               GROUP BY TRACE_INV_TRANS_1.PART_ID, TRACE_INV_TRANS_1.TRACE_ID, INVENTORY_TRANS_1.WORKORDER_BASE_ID, INVENTORY_TRANS_1.WORKORDER_LOT_ID, INVENTORY_TRANS_1.WORKORDER_SPLIT_ID,
                                                         INVENTORY_TRANS_1.WORKORDER_SUB_ID, INVENTORY_TRANS_1.PURC_ORDER_ID, INVENTORY_TRANS_1.PURC_ORDER_LINE_NO) AS ZZZ ON TRACE_INV_TRANS.TRACE_ID = ZZZ.TRACE_ID AND
                         TRACE_INV_TRANS.PART_ID = ZZZ.PART_ID AND TRACE_INV_TRANS.TRANSACTION_ID > ZZZ.TRX LEFT OUTER JOIN
                         TRACE ON TRACE_INV_TRANS.PART_ID = TRACE.PART_ID AND TRACE_INV_TRANS.TRACE_ID = TRACE.ID RIGHT OUTER JOIN
                         INVENTORY_TRANS INNER JOIN
                         PART ON INVENTORY_TRANS.PART_ID = PART.ID ON TRACE_INV_TRANS.TRANSACTION_ID = INVENTORY_TRANS.TRANSACTION_ID
WHERE        (INVENTORY_TRANS.TRANSACTION_ID = ISNULL(@UNIQUE_ID, INVENTORY_TRANS.TRANSACTION_ID)) AND (INVENTORY_TRANS.TRANSACTION_ID >= ISNULL(@TRANSACTION_ID, INVENTORY_TRANS.TRANSACTION_ID))
 
 
 
 
Example 2:
--Declare  @TRANSACTION_DATE as date
--Set  @TRANSACTION_DATE = null
 
 
--Declare  @UNIQUE_ID as int
--Set  @UNIQUE_ID = null
SELECT        PART.ID, PART.DESCRIPTION, ISNULL(TRACE_INV_TRANS.TRACE_ID, '-') AS TRACE_ID, INVENTORY_TRANS.TRANSACTION_ID, INVENTORY_TRANS.WAREHOUSE_ID, INVENTORY_TRANS.LOCATION_ID,
                         INVENTORY_TRANS.TRANSACTION_DATE, PART.STOCK_UM, INVENTORY_TRANS.WORKORDER_BASE_ID, INVENTORY_TRANS.WORKORDER_LOT_ID, INVENTORY_TRANS.WORKORDER_SPLIT_ID,
                         INVENTORY_TRANS.WORKORDER_SUB_ID, TRACE.COMMENTS, TRACE.EXPIRATION_DATE, INVENTORY_TRANS.PURC_ORDER_ID, INVENTORY_TRANS.PURC_ORDER_LINE_NO, '' AS BARCODE,
                         ZZZ.WORKORDER_BASE_ID AS WORKORDER_BASE_ID_Aux, ZZZ.WORKORDER_LOT_ID AS WORKORDER_LOT_ID_Aux, ZZZ.WORKORDER_SPLIT_ID AS WORKORDER_SPLIT_ID_Aux,
                         ZZZ.WORKORDER_SUB_ID AS WORKORDER_SUB_ID_Aux, ZZZ.PURC_ORDER_ID AS PURC_ORDER_ID_Aux, ZZZ.PURC_ORDER_LINE_NO AS PURC_ORDER_LINE_NO_Aux,
                         INVENTORY_TRANS.TRANSACTION_ID AS UNIQUE_ID, '' AS BARCODE_IMAGE, '' AS DATA_FOR_GENERATING_BARCODE_IMAGE
FROM            TRACE_INV_TRANS LEFT OUTER JOIN
                             (SELECT        TRACE_INV_TRANS_1.PART_ID, TRACE_INV_TRANS_1.TRACE_ID, MAX(TRACE_INV_TRANS_1.TRANSACTION_ID) AS TRX, INVENTORY_TRANS_1.WORKORDER_BASE_ID,
                                                         INVENTORY_TRANS_1.WORKORDER_LOT_ID, INVENTORY_TRANS_1.WORKORDER_SPLIT_ID, INVENTORY_TRANS_1.WORKORDER_SUB_ID, INVENTORY_TRANS_1.PURC_ORDER_ID,
                                                         INVENTORY_TRANS_1.PURC_ORDER_LINE_NO
                               FROM            TRACE_INV_TRANS AS TRACE_INV_TRANS_1 INNER JOIN
                                                         INVENTORY_TRANS AS INVENTORY_TRANS_1 ON TRACE_INV_TRANS_1.TRANSACTION_ID = INVENTORY_TRANS_1.TRANSACTION_ID
                               WHERE        (INVENTORY_TRANS_1.TYPE = 'I') AND (INVENTORY_TRANS_1.CLASS = 'R')
                               GROUP BY TRACE_INV_TRANS_1.PART_ID, TRACE_INV_TRANS_1.TRACE_ID, INVENTORY_TRANS_1.WORKORDER_BASE_ID, INVENTORY_TRANS_1.WORKORDER_LOT_ID, INVENTORY_TRANS_1.WORKORDER_SPLIT_ID,
                                                         INVENTORY_TRANS_1.WORKORDER_SUB_ID, INVENTORY_TRANS_1.PURC_ORDER_ID, INVENTORY_TRANS_1.PURC_ORDER_LINE_NO) AS ZZZ ON TRACE_INV_TRANS.TRACE_ID = ZZZ.TRACE_ID AND
                         TRACE_INV_TRANS.PART_ID = ZZZ.PART_ID AND TRACE_INV_TRANS.TRANSACTION_ID > ZZZ.TRX LEFT OUTER JOIN
                         TRACE ON TRACE_INV_TRANS.PART_ID = TRACE.PART_ID AND TRACE_INV_TRANS.TRACE_ID = TRACE.ID RIGHT OUTER JOIN
                         INVENTORY_TRANS INNER JOIN
                         PART ON INVENTORY_TRANS.PART_ID = PART.ID ON TRACE_INV_TRANS.TRANSACTION_ID = INVENTORY_TRANS.TRANSACTION_ID
WHERE        (INVENTORY_TRANS.TRANSACTION_DATE >= ISNULL(@TRANSACTION_DATE, INVENTORY_TRANS.TRANSACTION_DATE)) AND (INVENTORY_TRANS.TRANSACTION_ID = ISNULL(@UNIQUE_ID,
                         INVENTORY_TRANS.TRANSACTION_ID))
ORDER BY UNIQUE_ID
 
 
 
Example 3:
--Declare  @PART_ID as varchar (40)
--Set  @PART_ID = null
 
--Declare  @UNIQUE_ID as int
--Set  @UNIQUE_ID = null
SELECT        ID AS PART_ID, ID AS UNIQUE_ID, ISNULL(DESCRIPTION, '') AS DESCRIPTION, STOCK_UM, ISNULL(PRODUCT_CODE, '') AS PRODUCT_CODE, ISNULL(COMMODITY_CODE, '') AS COMMODITY_CODE, ISNULL(MFG_PART_ID, '')
                         AS MFG_PART_ID, ISNULL(PREF_VENDOR_ID, '') AS PREF_VENDOR_ID, '' AS BARCODE_IMAGE, ID AS DATA_FOR_GENERATING_BARCODE_IMAGE
FROM            PART
WHERE        (ID LIKE N'%' + ISNULL(@PART_ID, ID) + N'%') AND (ID = ISNULL(@UNIQUE_ID, ID))
ORDER BY PART_ID
 
 
 
 
 
 
Report design examples
 
The report templates are created with the Microsoft Report Builder that can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=53613