| Chapter 7 Advanced Reports |
In one report building process, WDReportCom can execute multiple SQL statements. This enables you to
1. Create a report like building block. You may divide one report into several parts, and respectively use the different SQL statements to make each part of the report. You can use the different queries to get the data located in the different tables or databases.
2. Create a complex report using the temporary table. First, you create a temporary table. Second, use several SQL statements to prepare data in the temporary table. You can execute INSERT, UPDATE, DELETE, INSERT SELECT statements. And then put the prepared data from the temporary table into your report.
3. Create one report file with several reports. For example, you may create one document with several tables.
Example
This example executes multiple SQL statements to create a report.
1. Create the template in Microsoft Word.
2. Write SQL statements in a WRF file.
/*********************************************************
Compare with Last Month by Categories
*********************************************************/
@F9_1=EXECSQL()
DROP TABLE tmp_category_sales
/* Create table tmp_category_sales */
@F9_2=EXECSQL()
CREATE TABLE tmp_category_sales (
CategoryID INTEGER,
Quantity INTEGER,
Amount MONEY
)
/* Get the sales amount by categories in the current month */
@F9_3=EXECSQL()
INSERT INTO tmp_category_sales (CategoryID, Quantity, Amount)
SELECT p.CategoryID
, SUM(d.Quantity)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
,Products p
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND YEAR(o.OrderDate) = YEAR('1996-04-01')
AND MONTH(o.OrderDate) = MONTH('1996-04-01')
GROUP BY p.CategoryID
/* Show the sales amount by categories in the current month */
@F9_4=REPORT(table=Report9 type=var cell=A3 copyrange=3:3)
SELECT c.CategoryName
, IIF(IsNull(t.Quantity),0,t.Quantity)
, IIF(IsNull(t.Amount),0,t.Amount)
FROM Categories c LEFT JOIN tmp_category_sales t
ON c.CategoryID = t.CategoryID
ORDER BY c.CategoryName
/* Delete from table tmp_category_sales */
@F9_5=EXECSQL()
DELETE FROM tmp_category_sales
/* Get the sales amount by categories in the last month */
@F9_6=EXECSQL()
INSERT INTO tmp_category_sales (CategoryID, Quantity, Amount)
SELECT p.CategoryID
, SUM(d.Quantity)
, Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
,Products p
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND o.OrderDate >= DateAdd('m',-1,#1996-04-01#)
AND o.OrderDate < #1996-04-01#
GROUP BY p.CategoryID
/* Show the sales amount by categories in the last month */
@F9_7=REPORT(table=Report9 type=fix cell=D3)
SELECT IIF(IsNull(t.Quantity),0,t.Quantity)
, IIF(IsNull(t.Amount),0,t.Amount)
FROM Categories c LEFT JOIN tmp_category_sales t
ON c.CategoryID = t.CategoryID
ORDER BY c.CategoryName
3. Generate the report.
In Microsoft Word, you can use (Formula) field to calculates a number. To insert an (Formula) field in a table or in regular text, you can use the Formula command (Table menu) or press CTRL+F9. For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
{ = 5+2*3 \# "#,##0.00"}In an (Formula) field, an expression that can contain any combination of numbers, bookmarks that refer to numbers, fields resulting in numbers, and the available operators and functions. The expression can refer to values in a table and values returned by functions. For more detail information about (Formula) field, refer to Microsoft Word Help.
In a report template file, you can use all kind of (Formula) field. And they will be brought to the final report file.
Example
Add totals such as Total Quantity, Total Amunt.
You can use (Formula) field of Microsoft Word.
1. Create a template file as follows, define the formula of total quantity as "=SUM(ABOVE)" in cell B3, and the formula of total amount as "=SUM(ABOVE)" in cell C3.
2. Write report function as follow.
@F2=REPORT(table=Report2 type=var cell=A2)
SELECT c.CategoryName, SUM(d.Quantity), Sum(d.UnitPrice * d.Quantity * (1-d.Discount))
FROM Orders o
,OrderDetails d
,Products p
,Categories c
WHERE o.OrderID = d.OrderID
AND d.ProductID = p.ProductID
AND p.CategoryID = c.CategoryID
AND YEAR(o.OrderDate) = YEAR('1996-04-01')
AND MONTH(o.OrderDate) = MONTH('1996-04-01')
GROUP BY c.CategoryName
ORDER BY c.CategoryName
3. The report generated by WDReportCom is as follows.
Sorting means placing data in some kind of order to help you find and evaluate it. For example, you may want to have a customer list sorted alphabetically by name or by country.
To sort your data, you may use SQL. Use the ORDER BY clause to have your results displayed in a sorted order.
Sorting, Grouping and Totaling
Sorting data
SELECT EmployeeID
,LastName
,FirstName
,HireDate
FROM Employees
ORDER BY HireDate; /* ascending sort */
In the example above, results will come back in ascending order by hire date. To explicitly specify ascending or descending order, add ASC or DESC, to the end of your ORDER BY clause. The following is an example of a descending order sort.
ORDER BY HireDate DESC; /* descending sort */
You can sum the values, count all the values or only those values that are distinct from one another, and determine the maximum, minimum, average. To add totals, there are two ways.
1. You can add total using (Formula) fields of Microsoft Word, such as SUM(above). For more detail information, refer to "Using Formula Fields" in this document.
2. You can use the aggregate functions in SQL statement, such as COUNT, SUM, AVG, MAX, MIN.
Grouped data is data that is sorted and broken up into meaningful groups. In a customer list, for example, a group might consist of all those customers living in the same Region.
To group data in a report, you should use GROUP TABLE REPORT function or FORM REPORT function. For more detail information, refer to "GroupTableReport Method", "FormReport Method", "Group Table Report" and "Form Report" in this document.
Using the feature of grouping data, you can make subreports within a report. A subreport would typically be used to perform one-to-many lookups such as Customer / Order / OrderDetails.
To make sub reports within the main report,
1. Write a JOIN SQL statement to access data from two or more tables. For example, you can join Customers, Orders and OrderDetails tables.
2. Use GROUP TABLE REPORT function or FORM REPORT function.
For more detail information, refer to the samples invoice.wrf, product_catalog.wrf and sales_detail.wrf within WDReportCom.
A subtotal is a summary that totals or sums numeric values in a group. You can sum the values in each group, count all the values in each group, and determine the maximum, minimum, average in each group. For example, determine the total sales per sales representative in a sales report.
To add subtotals, you can use aggregate functions in SQL statement.
1. Use aggregate function and GROUP BY clause, get summary data for each group, and insert results into a temporary table.
2. If you have the different kinds of summaries, repeat the step 1, and insert results into another temporary table.
3. Use group table report function or form report function, and join the detail data and the summary data using JOIN. The summary fields must be included in the group list.
4. Except for sub-totals, you can add total too using aggregate function in SQL statement. You must add total first using a Fixed Table report function before you use the Variable Table report function. Because the cell address of the total field will change after you use Variable Table report function.
For more detail information, please refer to the samples invoice.wrf and sales_detail.wrf within WDReportCom.
To make eye-catching reports, you can add pictures to your reports. You can directly insert pictures into the report template in Microsoft Word. For example, you want to display a logo in your report. You can insert the logo graphics file into the report template. For more information about adding pictures to tables, refer to Microsoft Word Help.
Except for inserting the static pictures during report design, you may insert pictures during report building process. WDReportCom can put the graphics files into the report, and support all graphics file format that Microsoft Word support.
To insert pictures into a report using WDReportCom, you should do as follows:
1. Store the paths and names of the graphics files in the database
You store the paths and names of the picture files in database, do not store the pictures. The file path can be a relative path, an absolute path or a URL. For example, you store "images\emp1.jpg" in Photo field.
2. Specify the inserted way, text wrapping style and size in the report template
To specify the inserted way, text wrapping style and size, you should write a formatting expression in the report template file. For a table report, you write a formatting expression in the cell. For a form report, write a formatting expression in the field switch "\#". WDReportCom will get the formatting expression, and insert a picture into the report according to the instruction in the format expression.
3. Write the report function in a WRF file, and identify the image fields using the IMAGE argument. For example,
@F1=Report(table=1 ... image=photo)
4. Use WDReportCom to generate report with pictures
WDReportCom will submit the SQL statement and get the data from database, read the graphics files according to the paths and names, and insert them into the report. If the path and file name of the picture is "", WDReportCom will return "". WDReportCom will return "#Error" if it does not find the file of the picture.
For more detail information about pictures, refer to the samples employee_profile.wrf, product_catalog.wrf within WDReportCom.