By Redge Shepherd Posted on

Crystal Reports and SQL Stored Procedures – Passing Multiple Parameters – Part 1 of 2

Passing multiple parameters between Crystal Reports and a T-SQL Stored Procedure

  1. Part 1:  SQL Server Management Studio SQL code
  2. Part 2:  Crystal Reports report setup and SHARED Formulas

Crystal Reports is a well-known and widely used application that is able to extract information from a variety of sources to create complex and dynamic reports.  For our purposes here, a connection to Microsoft’s SQL Server is all that is required.

Crystal Reports also provides for more advanced functionality that can serve purposes beyond report creation alone.  We can “INSERT” data from a Crystal Report into a SQL Server database table by passing parameters to a stored procedure and we can also return columns that in turn can be used in the Crystal Report itself.

As discussed in our post titled “T-SQL SEQUENCE – An Application“, a customer requires each container of product to be identified with a unique serialized label that adheres to the Automotive Industry Action Group’s publication “Shipping/Parts Identification Label Standard” (AIAG-B-10) Version 3, June 2004, and contains the following information:

  • Part Number (P)
  • Quantity (Q)
  • Serial Number (S)
  • Vendor (Supplier Code) (V)
  • Part Description (N/A)
  • Customer Purchase Order Number (K)
  • “License Plate” (Vendor + Serial Number), (1J)
  • Manufacturing Date (MM/DD/YY), (N/A)
  • Supplier Name, (N/A)
  • Supplier Designated Area (Revision / Job Number), (N/A)

With the exception of the SERIAL NUMBER, all of the remaining fields can be populated using a table served by the front end application.  Information from the front end application includes data extracted from various tables in the database and a user form to input data that is otherwise not available in the database tables.

The challenges discussed in “T-SQL SEQUENCE – An Application” are two-fold:

  1. A unique serial number is required for each label and
  2. The serial number must be traceable to the product to which it is assigned.

Tools Required:

  1. Microsoft SQL Server Management Studio (MSSMS)
  2. Crystal Reports – We’re using Crystal Reports 2016

General Procedure:

  1. Using Microsoft SQL Server Studio:
    1. Create a database SEQUENCE object to generate our Serial Number
      • We will also create a “Batch SEQUENCE” that will be used to track the number of labels processed in a given “print session”
    2. Create a TABLE to store the label information from Crystal Reports
    3. Create a Stored Procedure to:
      • INSERT data into our TABLE as “passed” from Crystal Reports  and
      • RETURN a unique SERIAL number to Crystal Reports
  2. Using Crystal Reports:
    1. Create a Primary report for the Label to be applied to our container.
    2. Create a Sub report to pass parameters from the Primary Report to our stored procedure to pass the SERIAL number to the Primary-report.
    3. SHARED formulas are used to pass parameters from the Sub report to the Primary report
    4. Publish the final report for use by the Front End application.

Putting it ALL together – Step by Step:

  1. Creating the Sequences:  Enter and execute the following code in MSSMS
    • USE [YourDataBaseNameGoesHere]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      /*******************************************************
      1.  CREATE SEQUENCES:  NextBatch and NextLabel
      *******************************************************/
      --
      --NextBatch
      --
      DROP SEQUENCE IF EXISTS dbo.NextBatch
      CREATE SEQUENCE dbo.NextBatch
      AS int
      START WITH 1
      INCREMENT BY 1
      MINVALUE 1
      MAXVALUE 999999999
      CYCLE
      NO CACHE
      --
      --NextLabel
      --
      DROP SEQUENCE IF EXISTS dbo.NextLabel
      CREATE SEQUENCE dbo.NextLabel
      AS int
      START WITH 1
      INCREMENT BY 1
      MINVALUE 1
      MAXVALUE 999999999
      CYCLE
      NO CACHE
  2. Create the Table to store the label data using the following code:
    1. USE [YourDataBaseNameGoesHere]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      /******************************************************
      2.  CREATE LABEL TABLE
      ******************************************************/
      IF OBJECT_ID(N'dbo.JobLabelData', N'U') IS NOT NULL
      DROP TABLE dbo.JobLabelData
      CREATE TABLE [dbo].[JobLabelData] (
      LabelID  int IDENTITY (1,1)
      --
      --Do NOT use DEFAULT (NEXT VALUE FOR NextLabel) to enable
      --table to be altered or truncated if necessary.
      --
      SerialNo int,
      -------------------------------------------------------
      BatchID  int,
      -------------------------------------------------------
      Labelx  int, -- Label #
      Labely  int, -- Total #
      LabelxOFy varchar(30), -- BoxDescrip (String 30)
      -------------------------------------------------------
      CustName varchar(30),
      CustCode varchar(12),
      PartNo  varchar(30),
      PartRev  varchar(30),
      PartDesc varchar(MAX),
      QtyInBox varchar(30),
      LotNo  varchar(30),
      CustomerPO varchar(30),
      VendorCode varchar(30),
      VendorID  varchar(30),
      OrderNo  varchar(12),
      JobNo  varchar(16),
      MfgDate  varchar(8),
      Printed  datetime DEFAULT (GETDATE())
      )
  3. Enter the following stored procedure that will “integrate” our database table with Crystal Reports:
    • USE [YourDataBaseNameGoesHere]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      /************************************************
      3. Stored Procedure to ADD n Labels
      ************************************************/
      CREATE PROCEDURE [dbo].[LabelXfer]
      --Parameters FROM Crystal Reports
      @BoxDescrip varchar(30) = NULL,
      @PartNo varchar(30) = NULL,
      @PartRev varchar(30) = NULL,
      @QtyInBox varchar(30) = NULL,
      @LotNo varchar(30) = NULL,
      @CustomerPO varchar(30) = NULL,
      @VendorCode varchar(30) = NULL,
      @OrderNo varchar(12) = NULL,
      @JobNo varchar(16) = NULL,
      @DateWithoutSlashes varchar(6)
      AS
      BEGIN
      --TRUNCATE TABLE dbo.LabelData
      --
      --The BoxDescrip field is of the form "x OF y", where x is
      --the unique box number and "y" is the total number of boxes
      --If this field is blank, there is no record to process!
      --Otherwise, we need to parse "x" and "y" to store them as
      --values in our table.
      --
      IF LEN(@BoxDescrip) > 0
      BEGIN
      DECLARE @xOFy varchar(4) = ' OF 'DECLARE @Labelx int = (SELECT CAST(SUBSTRING(@BoxDescrip,
      1,
      (CHARINDEX(@xOFy, @BoxDescrip,1) - 1))
      as int))      DECLARE @Labely int = (SELECT CAST(SUBSTRING(@BoxDescrip,
      (CHARINDEX(@xOFy, @BoxDescrip,1) + DATALENGTH(@xOFy)),
      (DATALENGTH(@BoxDescrip) – CHARINDEX(@xOFy, @BoxDescrip,1) + DATALENGTH(@xOFy)+1))
      as int))

      DECLARE @BatchID int

      IF @Labelx = 1
      BEGIN
      Set @BatchID = (NEXT VALUE FOR NextBatch)
      END
      ELSE
      BEGIN
      SET @BatchID = (SELECT CONVERT(int, current_value)
      FROM sys.sequences
      WHERE name = ‘NextBatch’)
      END

      DECLARE @CustCode varchar(12) = (SELECT CustCode
      FROM Orders
      WHERE OrderNo = @OrderNo)

      DECLARE @CustName varchar(30) = (SELECT CustName
      FROM CustCode
      WHERE CustCode = @CustCode)

      DECLARE @VendorID varchar(30) = (SELECT User_Text2
      FROM CustCode
      WHERE CustCode = @CustCode)

      DECLARE @PartDesc varchar(MAX) = (SELECT PartDesc
      FROM OrderDet
      WHERE JobNo = @JobNo)

      DECLARE @MfgDate varchar(8) = (Substring(@DateWithoutSlashes,1,2) + ‘/’ +
      Substring(@DateWithoutSlashes,3,2) + ‘/’ +
      Substring(@DateWithoutSlashes,5,2))

      DECLARE @SerialNo int = (NEXT VALUE FOR NextLabel)

      INSERT INTO JobLabelTable( SerialNo
      , BatchID
      , Labelx
      , Labely
      , LabelxOFy
      , CustName
      , CustCode
      , PartNo
      , PartRev
      , PartDesc
      , QtyInBox
      , LotNo
      , CustomerPO
      , VendorCode
      , VendorID
      , OrderNo
      , JobNo
      , MfgDate
      )
      VALUES ( @SerialNo
      , @BatchID
      , @Labelx
      , @Labely
      , @BoxDescrip
      , @CustName
      , @CustCode
      , @PartNo
      , @PartRev
      , @PartDesc
      , @QtyInBox
      , @LotNo
      , @CustomerPO
      , @VendorCode
      , @VendorID
      , @OrderNo
      , @JobNo
      , @MfgDate
      )
      END
      —
      — RETURN Parameters TO Crystal Reports
      —
      SELECT TOP(1)
      SerialNo,
      VendorID
      FROM JobLabelTable
      ORDER BY SerialNo DESC

      END

You may have noticed that there are some additional fields here that are not necessarily part of the original scope of our project.  The Front End application allows us to enter the Vendor Number manually, however, we modified one of the “user definable” fields in the Customer Code table to serve as a permanent record of our customer assigned Vendor Number as User_Text2.

Rather than pass the Customer Name from Crystal Reports, we decided to extract it from the Customer Code table as well. Similarly, we are reading the Part Description data directly from the Order Detail table.

Although we could link the tables referenced in our stored procedure using Crystal Reports, we decided to extract the data directly from the table in our stored procedure to minimize any additional complexity in the report and to maintain data integrity in our internal table.

In our next post, we will focus on Crystal Reports and link our Stored Procedure to the label report.

Until Next Time,

versalytics-logoRelated Articles

  1. T-SQL SEQUENCE – An Application

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.