Using Formula in Crystal Report

• Download source - 88.75 KB

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:

   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.

   // 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
   
   	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:

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

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()