Click here to Skip to main content
13,768,211 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


18 bookmarked
Posted 8 Aug 2011
Licenced CPOL

Using Formula in Crystal Report

, 8 Aug 2011
Rate this:
Please Sign up or sign in to vote.
The formula field in a Crystal report is a functionality provided to enhance computation of values dynamically during the display/printing of records in a report of an application.


The ultimate goal of most commercial software is the generation of useful and accurate reports for the requirements of the users in enhancing the decision making process at the managerial level of the enterprise. Notwithstanding the existence of some other forms of reporting tools, Crystal Report has found enormous patronage in the development of concise and reliable reports. In this paper, the use of one of its functionalities – formula – is used to demonstrate the implementation of the electronic bin card (e-BinCard). The bin card is used to manually track individual items in stock of most merchandising company and stores/warehouses in most of the developing nations of the world, including Nigeria. However, this is gradually being replaced by software applications capable of performing the same function. For this demonstration, a stock management application is presented with the implementation of an e-BinCard using the formula field of the Crystal report design tools to compute dynamically the stock balance.

1.0 Introduction

The formula field in a Crystal report is a functionality provided to enhance computation of values dynamically during the display/printing of records in a report of an application. Most developers often fail to effectively explore the potentials of this Crystal Report functionality and often preferred other report viewing modules. Formulas in Crystal report have two forms of syntax; Basic and Crystal. In this paper, a few of the details on the syntax, data types, return values and case sensitivity will be demonstrated. Formulas are always used to return a value or Insert a value where desired. The data types returned must be simple and can be Number, Currency, String, Boolean, Date, Time and DateTime. You cannot return complex data types such as range or array. Formulas return a value by assigning the value to the Formula variable and must always return a value. These values are automatically assigned to the field the formulas are inserted. The Stock Bin Card will be used to demonstrate the use of formulas and this will enhance the reader’s knowledge of formula in Crystal report.

2.0 The Stock Bin Card

The Bin Card is a manual technique of maintaining stock movement in a warehouse. The Bin Card contains the following elements/fields; Stock Code, and Description, date of operation, Opening balance, Quantity received and dispatched/sold stock balance and staff Identity. (See figure 1). Although getting this type of report from the Crystal Report module may look easy but the Group By or Order By functionality may be muddled up when computing the Stock Balance rendering the report meaningless. It is expected the reader has minimal knowledge of Visual Basic.NET and MySql for ease of understanding as will be demonstrated in the course of this paper. The example is broken down into three parts; the table (MySql), form (design – VB.NET and Crystal Report) and the program (VB.NET).

2.1. The Database, Table and Columns

Databases are created in MySql using the instruction “Create database nameofdatabase” while tables are also created with the instruction “Create table tablename”. The bincard database and the stockbincard table are thus created. The creation and use of Database and Table are illustrated in MySQL Tutorial. Alternatively, the MySQLAdmin could also be used for the same purpose as illustrated in figure 2. The MySQL query browser could be used to confirm the creation of the database and the table respectively along with the columns.

3.0 The StockBinCard Project

The Stockbincard project is created in the VB.NET environment and the following forms are created:

  1. The stkbinmenu.vb - The main menu form for the project (figure 3). The form contains three submenus to access the three basic functionalities of the e-BinCard. (Stock Receipts, Dispatches and printing e-BinCard).
  2. The dataentry.vb - This form provides the mechanism for data entry into the database. The receipts and dispatches are registered for the stock item. If the stock item is a new product, it passes into creation mode, else it accepts the data in either of the operations (receipts or dispatches) (see figure 4). The following code fragments suffixes:
    Public Function GetDetails(ByVal strCode As String) As Int16
      Sql = "Select StockDescr From stockbincard Where StockCode = ?Dcode "
            With cmd
               .CommandText = Sql
               .CommandType = CommandType.Text
               .Connection = BconnectObj
               .Parameters.Add("?Dcode", MySqlDbType.VarChar).Value = strCode
               label = .ExecuteScalar  // Returns the label if item is in the DB
            End With
    End Function

    Once the item is found in the e-BinCard database, the various objects are initialized and the operation is validated. It is important to state here that the StockCode column of the database is not used as a key, therefore multiple occurrence of records is allowed.

  3. The stkModule.vb contains the declaration of common variables (global) used in the project.

    Some of these declarations are commonly used and accessible variables or instances of objects in the project.

    // stkmodule.vb
    Module MainModule
        Public myReport As New ReportDocument 
        Public RData As New DataSet
        Public KAdapter As New OdbcDataAdapter
        Public XYcommand As New OdbcCommand
        Public cmd As New MySqlCommand
        Public BconnectObj As MySqlConnection = New MySqlConnection()
        Public MyConString As String
        Public Conn As New OdbcConnection() '
        Public IsConnected As Boolean
        Public Gflag As Boolean        'Global error flag
        Public FormMain As New Form()
        Public toggleForm As Integer = 0
    End Module

    The file also contains some other public functions such as the startup() – starting point for the project, DatabaseConnection() – connects to the BinCard database, PrintBinCard() – to generate the e-BinCard.

  4. The ReportBinCardView.vb – implements the viewing of the report generated by the crystalreport viewer (see figure 5): This is the form that ‘houses’ the object ViewReportBinCard.

    The form is created and the crystalreportviewer object dragged and inserted into the form and the names associated appropriately. The following code segment is associated with the viewer to load the output of the select statement (see figure 7) to the ViewReportBinCard.

    Private Sub ViewReportBinCard_Load(ByVal sender As System.Object, 
     ByVal e As System.EventArgs) Handles ViewReportBinCard.Load
     ViewReportBinCard.ReportSource = myReport
     End Sub
  5. The ReportBinCard.rpt - The Crystal Report file (design) for the StockBinCard (see figure 6).

    Before using the ‘Crystal report wizard’ to create the “ReportBinCard.rpt”, the generation of the ‘Rdata’ set and writing in an XML into the file “StockBinCard.xml” should be realized.

    The following code segment generates the dataset ‘RData in XML format:

    XYcommand.Connection = Conn
    	XYcommand.CommandText = Sql
    	KAdapter.SelectCommand = XYcommand
    RData.WriteXml("StockBin.xml", XmlWriteMode.WriteSchema)

    The crystal report creation wizard allows the creation of ‘New Connection’ by uploading the XML file (StockBin.xml) and attaching the table – StockBinCard – to the ‘ReportBinCard.rpt’.

    In figure 6, the corresponding fields of the StockBinCard are shown with the formula field to compute the stock balance after each operation.

4.0 Generating the Report

Generating the report involves preparing the data from the database into a dataset for the report-loader. The following codes will be used to achieve the desired result in the following order (see figure 7):

  1. The select statement
  2. The connection to the database
  3. An instance of OdbcCommand Xycommand
  4. An instance of OdbcDataAdapter Kadapter
  5. Filling the dataset – Rdata
  6. Loading the report – myreport – and setting the Report ReportSource
  7. Display the report - ViewReportBinCard

5.0 The Formula Field

As earlier indicated, the stock balance will be computed and displayed dynamically using the Crystal Report Formula. When creating formulas, you have the option of using either Crystal or Basic syntax. Almost any formula written with one syntax can be written with the other. Reports can contain formulas that use Basic syntax as well as formulas that use Crystal syntax, but a single formula can use only one syntax. In the computation of the stock balance, we shall demonstrate with both the crystal and basic syntax options.

5.1. The Basic Syntax

The Microsoft MSDN Library clearly explains the Basic syntax of the crystal report and employs the same syntax as the BASIC language. An exception is that a value must be returned in the special variable Formula. It is not case-sensitive and contains data types such as string, integer, etc. as commonly used in BASIC. In figure 8 below, the option Basic Syntax is selected, the table columns highlighted, and the formula also encircled.

The formula for the computation of the stock balance is given at the code section of the formula workshop:

Global BalQty As Number 	      - declares the global variable Balqty as an Integer
if {Table.Flag} = 1 Then                        - if the record is Receipt of Stock
BalQty = {Table.qtyRec} + Balqty       - Add to the cumulative value of Balqty
else                             		      - otherwise
BalQty = BalQty – {Table.qtyDispa}
Formula  = Balqty 		      - Return Balqty

5.2. The Crystal Syntax

The crystal syntax is equally illustrated and explained in the MSDN Library. The syntax is quite different from the Basic Syntax. The value returned by a crystal syntax does not have to be attached to a variable but the variable value itself. Any of the data types could be returned as a value. This will be displayed at the position in the report containing the formula. This is demonstrated below in figure 9.

6.0 How the Program Works

Once the program is launched, upon a successful connection to the database – bincard - , message of successful completion is displayed otherwise an error message (See figure 10).

Thereafter, the Main Menu is displayed with the following options:

  1. Receive Stock – To register receipts
  2. Dispatch Stock – To register dispatches
  3. Print Bin Card – To print Bin Card

During the registration of stock (receipts or dispatches), if the item is new, the program creates the item otherwise, the information on the stock is displayed, i.e., the stock description. A sample report output is shown in figure 11.

7.0 Conclusion

The stockbincard has been used to demonstrate the use of an electronic stock bin card. In its present form, it looks simple but is a good demonstration of the use of Formula fields in crystal report.

Other functionalities such as; periodic choice of printing the electronic Stock Bin Card, inclusion of the staff Identity, etc. could be inserted and full report of the stock during for the purpose of inventory. It is hoped the reader would find this example useful and be able to enhance it for other requirements.

The source program could be downloaded here.

8.0 Suggested Further Reading

  1. MySQL 5.1 Reference Manual
  2. MySQL Tutorial
  3. MSDN for Visual Studio 2008
  4. Visual Basic .NET Programming for Beginners
  5. Visual Basic and MySQL developer Notes
ABC Company Ltd.

43 Asokoro, Abuja, Nigeria

Stock Bin Card
Stock Code: 2633774 Description: Michelin Tyre Tubes 295/15
Date(dd/mm/yy)ReceiptsDispatch/SoldStock BalanceStaff Id./SignComments
12/05/2011--200TonyOpening Bal


Figure 1: A typical Manual Stock Bin Card


Figure 2: The Stockbincard Table


Figure 3: The Main Menu for the StockNinCard Project


Figure 4: The Data Entry Form


Figure 5: The Report Viewer Form


Figure 6: The Crystal Report file
Sql = "Select StockCode,StockDescr,DateMvt,qtyRec, qtyDispa,Comments, _
		Flag From stockbincard " _
        & " Where StockCode = '" & idtx & "' "

        errflg = False

        If Conn.State = ConnectionState.Closed Then
        End If
                XYcommand.Connection = Conn
                XYcommand.CommandText = Sql
                KAdapter.SelectCommand = XYcommand

                FormMain = New ReportBinCardView
                FormMain.Text = FormMain.Text
Fig. 7: The Code for generating the report


Fig. 8: The Formula using Basic Syntax


Fig. 9: The Formula using Crystal Syntax


Fig. 10: The Successful Connection Message


Figure 11: A Typical report display


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Ayeni J.A
Web Developer
Nigeria Nigeria
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
manish149822-Sep-13 4:48
membermanish149822-Sep-13 4:48 
GeneralMy vote of 5 Pin
KiranKumar Roy11-Feb-13 23:02
memberKiranKumar Roy11-Feb-13 23:02 
SuggestionGood Article Pin
Jhone Mitnick22-Mar-12 23:21
memberJhone Mitnick22-Mar-12 23:21 
GeneralMy vote of 5 Pin
Jhone Mitnick22-Mar-12 23:20
memberJhone Mitnick22-Mar-12 23:20 
GeneralGood One Pin
Prasad_Kulkarni27-Dec-11 20:15
memberPrasad_Kulkarni27-Dec-11 20:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04-2016 | 2.8.181116.1 | Last Updated 8 Aug 2011
Article Copyright 2011 by Ayeni J.A
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid