Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / Visual Basic

Using Formula in Crystal Report

Rate me:
Please Sign up or sign in to vote.
4.85/5 (15 votes)
8 Aug 2011CPOL9 min read 234.9K   5.2K   20   5
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.

Abstract

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:
    VB.NET
    Public Function GetDetails(ByVal strCode As String) As Int16
    
      Sql = "Select StockDescr From stockbincard Where StockCode = ?Dcode "
    
    Try
    
        Try
    
            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.

    VB.NET
    // 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.

    VB.NET
    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:

    VB.NET
    XYcommand.Connection = Conn
    
    	XYcommand.CommandText = Sql
    
    	KAdapter.SelectCommand = XYcommand
    
    	KAdapter.Fill(RData)
    
    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:

VB.NET
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}
endif
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
    http://dev.mysql.com/doc/refman/5.1/en/tutorial.html
  2. MySQL Tutorial
    http://www.tizag.com/mysqlTutorial/
  3. MSDN for Visual Studio 2008
    http://www.microsoft.com/vs
  4. Visual Basic .NET Programming for Beginners
    http://www.homeandlearn.co.uk/net/vbnet.html
  5. Visual Basic and MySQL developer Notes
    http://www.openwin.org/mike/index/index.php/articles
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
16/05/201120-220Tony-
22/05/2011-5215Andrew-
23/05/201140-255Tony-
24/05/2011-55200JohnTransfer
27/05/2011-12188AndrewDamaged
27/05/20117-195Tony-

image001.jpg

Figure 1: A typical Manual Stock Bin Card

image002.png

Figure 2: The Stockbincard Table

image003.png

Figure 3: The Main Menu for the StockNinCard Project

image004.png

Figure 4: The Data Entry Form

image005.jpg

Figure 5: The Report Viewer Form

image006.png

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

        errflg = False

        If Conn.State = ConnectionState.Closed Then
                Conn.Open()
        End If
                XYcommand.Connection = Conn
                XYcommand.CommandText = Sql
                KAdapter.SelectCommand = XYcommand
                KAdapter.Fill(RData)
                myReport.Load("ReportBinCard.rpt")
                myReport.SetDataSource(RData)

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

image007.png

Fig. 8: The Formula using Basic Syntax

image009.jpg

Fig. 9: The Formula using Crystal Syntax

image010.png

Fig. 10: The Successful Connection Message

image012.jpg

Figure 11: A Typical report display

License

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


Written By
Web Developer
Nigeria Nigeria
A Computer Scientist of high programming skill

Comments and Discussions

 
GeneralMy vote of 5 Pin
manish149822-Sep-13 3:48
manish149822-Sep-13 3:48 
GeneralMy vote of 5 Pin
KiranKumar Roy11-Feb-13 22:02
KiranKumar Roy11-Feb-13 22:02 
SuggestionGood Article Pin
Jhone Mitnick22-Mar-12 22:21
Jhone Mitnick22-Mar-12 22:21 
GeneralMy vote of 5 Pin
Jhone Mitnick22-Mar-12 22:20
Jhone Mitnick22-Mar-12 22:20 
I luren some thing from it
GeneralGood One Pin
Prasad_Kulkarni27-Dec-11 19:15
Prasad_Kulkarni27-Dec-11 19: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.