How to Create Daily, Weekly and Monthly Report in C#

Submitted by janobe on Tuesday, December 4, 2018 - 14:43.

In this tutorial, I will teach you how to create Daily, Weekly and Monthly Report in C#. This method has the ability to generate report that would be display in the DataGridView. This is one of the most important function in a system because it is where the user or admin will find out the transaction every month.So, let's begin.

Creating Database

Create a database named “dbtransaction”. Execute the following query for creating table and adding data in the table.

CEATE TABLE `tbltransaction` (
  `TRANSID` int(11) NOT NULL,
  `ORNO` int(30) NOT NULL,
  `TRANSDATE` date NOT NULL,
  `AMOUNTSALE` double NOT NULL,
  `CASHIER` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbltransaction`
--
 
INSERT INTO `tbltransaction` (`TRANSID`, `ORNO`, `TRANSDATE`, `AMOUNTSALE`, `CASHIER`) VALUES
(1, 70004, '2018-12-03', 385, 'Janno Palacios'),
(2, 70005, '2018-12-03', 385, 'Janno Palacios'),
(3, 70002, '2018-12-01', 385, 'Janno Palacios'),
(4, 70001, '2018-12-01', 385, 'Janno Palacios'),
(5, 70006, '2018-12-04', 69, 'Janno Palacios'),
(6, 70007, '2018-12-04', 69, 'Janno Palacios'),
(7, 70003, '2018-12-02', 138, 'Janno Palacios');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application for c#. psReport1

Step 2

Do the form just like shown below. psReprt2

Step 3

Add a namespace to access MySQL libraries.

using MySql.Data.MySqlClient;

Step 4

Create a connection between C# and MySQL database. After that, initialize all the classes and variables that are needed.

 MySqlConnection con = new MySqlConnection("server=localhost;user id=root;password=;database=dbtransaction;sslMode=none");
 MySqlCommand cmd;
 MySqlDataAdapter da;
 DataTable dt;
 string sql;

Step 5

Create a method for retrieving data in the database.

private void loadData(string sql)
        {
            try
            {
                con.Open();
                cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                dt = new DataTable();
                da.Fill(dt);
 
                dtg_list.DataSource = dt;
 
 
            } catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                da.Dispose();
            }
        }

Step 6

Double click the “Daily Report” radio button and do the following codes for the daily report.

        private void rdo_daily_CheckedChanged(object sender, EventArgs e)
        {
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE DATE(`TRANSDATE`) =CURDATE()";
            loadData(sql);
        }

Step 7

Double click the “Weekly Report” radio button and do the following codes for the weekly report.

        private void rdo_weekly_CheckedChanged(object sender, EventArgs e)
        {
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE WEEK(`TRANSDATE`) =WEEK(NOW())";
            loadData(sql);
        }

Step 8

Double click the “Monthly Report” radio button and do the following codes for the Monthly report.

        private void rdo_monthly_CheckedChanged(object sender, EventArgs e)
        {
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE MONTH(`TRANSDATE`) =MONTH(NOW())";
            loadData(sql);
        }

Note: The database file is included inside the folder. The complete source code is included. You can download it and run it on your computer For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.

Add new comment

4005 views