Mada za sehemu hiiCreating database connectivity options and reportingMada 4
- Establishing a connection of event-driven programs to a database
- Performing basic CRUD operations using SQL queries
- Showing database records on forms
- Generating reports from database data
A database report is a formatted summary of data that has been retrieved from a database to answer a specific question or support a decision. In event-driven programming, reports are generated when a user triggers an event such as clicking a "Generate Report" button.
A database report takes raw data stored in database tables and presents it in a readable, organised format. Reports help users understand information quickly without having to read thousands of individual database records.
Reports typically include:
- Headers – identifying the report (title, date range, author)
- Data sections – the actual information retrieved from tables
- Totals and summaries – aggregated values like sums and averages
- Footers – report date, page numbers, or notes
Step 1: Connect to the Database
First, the program establishes a connection to the database using connection strings that include the server name, database name, username, and password.
Dim conn As New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=ShopDB;User ID=admin;Password=secret"
conn.Open
Step 2: Write an SQL Query
Use a query (usually SQL) to specify exactly what data you need. The query selects data from one or more tables and can filter, sort, and calculate values.
Step 3: Retrieve the Data
Execute the query and store the results in a recordset object that holds the retrieved rows.
Step 4: Format the Report
Arrange the data into a readable layout with proper headings, alignment, and grouping.
Step 5: Export or Display
Present the finished report by displaying it on screen, printing it, or saving it to a file such as PDF, Excel, or CSV.
Consider a small general store in Arusha. The owner wants a monthly sales report.
Database table structure (Sales):
| Field | Data Type |
|---|---|
| SaleID | Integer (auto-increment) |
| ProductName | Text |
| Quantity | Number |
| UnitPrice | Decimal |
| SaleDate | Date |
Step 1 – Connect to the database
Dim conn As New ADODB.Connection
' Use the exact driver name installed on your computer
conn.Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=shop_db;User=report_user;Password=your_password;"
Step 2 – Write SQL query for June 2025 sales
Dim sql As String
sql = "SELECT ProductName, SUM(Quantity) AS TotalQty, " & _
"SUM(Quantity * UnitPrice) AS TotalSales " & _
"FROM Sales " & _
"WHERE SaleDate BETWEEN '2025-06-01' AND '2025-06-30' " & _
"GROUP BY ProductName " & _
"ORDER BY TotalSales DESC"
Step 3 – Execute and retrieve
Dim rs As New ADODB.Recordset
rs.Open sql, conn
Step 4 – Format and display
The program loops through each record, adds one line to the report for each product and keeps a running total:
Dim report As String, grandTotal As Currency
Do While Not rs.EOF
report = report & rs("ProductName") & vbTab & rs("TotalQty") & vbTab & _
Format(rs("TotalSales"), "#,##0") & vbCrLf
grandTotal = grandTotal + rs("TotalSales")
rs.MoveNext
Loop
rs.Close
conn.Close
With a title, column headings and the total added, the finished report looks like this:
General Store, Arusha
Monthly Sales Report — June 2025
Product Name | Quantity Sold | Total Sales (TZS)
-------------------|---------------|------------------
Rice (5kg) | 45 | 675,000
Cooking Oil (2L) | 38 | 570,000
Sugar (2kg) | 52 | 468,000
Total Revenue: TZS 1,713,000
Step 5 – Export options
The report can be saved as a CSV file for the accountant or printed for the shop noticeboard.
A report rarely lists every record. Instead, the query aggregates the data: it combines many rows into a few summary figures. The common SQL aggregate functions are:
| Function | What it returns |
|---|---|
SUM() | The total of a column |
COUNT() | The number of rows |
AVG() | The average value |
MAX() and MIN() | The largest and the smallest value |
WHERE chooses which rows are included, GROUP BY gives one summary row for each group (each product, class or month), and ORDER BY sorts the result. For a monthly attendance report, for example:
SELECT StudentName, COUNT(*) AS DaysPresent
FROM Attendance
WHERE Status = 'Present'
AND AttendDate BETWEEN '2025-06-01' AND '2025-06-30'
GROUP BY StudentName
ORDER BY StudentName;
The figures in a report come straight from the stored records through the query, so the report is only as accurate as the query. A wrong date range, a missing WHERE condition or a wrong GROUP BY gives a report that looks neat but misleads the reader. Always check a few totals by hand against the table.
Choose the form that makes the message clearest for the person who will read the report:
- Table: best when the reader needs exact figures, such as a fee statement.
- Bar chart: compares categories, such as the sales of each product.
- Line graph: shows change over time, such as total sales for each month of the year.
- Pie chart: shows shares of a whole, such as each product's part of total revenue. Use it only for a few categories.
To draw a chart, the query must return one column of labels and one column of values (for example ProductName and TotalSales), which a chart control or a spreadsheet then plots. Give every chart a title, label the axes with their units, and do not crowd it with too many colours or categories. A shop owner who sees at a glance that one product's bar is far shorter than the others can decide quickly what to restock and what to stop selling. This is how reports support decisions.
In an event-driven application the report code is placed inside an event procedure, so it runs whenever that event happens:
Private Sub cmdReport_Click()
' connect, run the query, format and display the report
End Sub
Because the query runs again each time the button is clicked (or a form loads, or a timer ticks), the report always shows the data as it is in the database at that moment. A sale saved a minute ago appears in the next report without anyone retyping figures.
| Format | Use Case |
|---|---|
| Tabular | Structured data with columns and rows |
| Summary | Aggregated totals and statistics |
| Labelled | Key-value pairs for quick reference |
| Grouped | Data organised by category (e.g., by region) |
- Always filter data to include only what is needed — a report covering five years is harder to read than one covering one month
- Use clear column headings and consistent formatting
- Include the report date so readers know when the data was current
- Test your SQL query separately before building the full report
- Give the report program a database user that can only read the data it needs, and never show a password on screen or in a report
- SQL queries — to extract data from tables
- Reporting tools — Crystal Reports, Microsoft Access Report View, or built-in report designers in programming environments
- Export formats — PDF, Excel (.xls), CSV, or printed output
A school secretary in Tanzania uses database reports daily to generate student fee payment summaries for the headteacher. By running a query on the school's database, she produces a report showing which students have fully paid, partially paid, or not paid their school fees for the term — helping the administration make quick decisions about who to follow up with and ensuring the school can plan its budget for textbooks and meals.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
The student should be able to:
- Write SQL queries to retrieve data required for reports.
- Format and organize data into meaningful and readable report layouts.
Tathmini ya matokeo
Reports that accurately reflect database data and that meet specified requirements are generated from database.
Tathmini ya maarifa
Methods used: The trainee should explain how to:
- Write and execute SQL queries to extract data for reporting.
Principles: The student should explain the principle of:
- Data aggregation and summarization for report generation.
- Visual representation of data for clarity and decision-making.
Theories: The trainee should explain:
- How reporting enhances decision-making in data-driven applications.
- The role of database queries in providing accurate data for reports.
Circumstantial knowledge:
Detailed knowledge about:
- Designing reports for specific use cases (e.g. financial summaries or attendance records).
- Formatting data for graphs, tables, and charts to improve visual appeal.
- Integrating reports into event-driven applications for real-time data updates.
Swali
What is the primary purpose of a database report?
Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.
Ingia ili kufanya mazoeziMwalimu
Umekwama? Niulize chochote kuhusu mada hii.
Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.
Ingia ili kuulizaMajadiliano
Hakuna maswali bado