User manual LJZSOFT WDREPORTGEN

[. . . ] WDReportGen User Manual Version 3. 7 2007-11 Copyright© 2003 -2007 LJZsoft Corporation All rights reserved Contents CHAPTER 1 INTRODUCTION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1. 1 OVERVIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1. 2 FEATURES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 CHAPTER 2 INSTALLATION AND STARTUP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2. 1 SOFTWARE REQUIREMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2. 2 INSTALLING WDREPORTGEN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2. 3 UNINSTALLING WDREPORTGEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2. 4 COMMAND LINE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 CHAPTER 3 QUICK START . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3. 1 LEARNING HOW TO USE WDREPORT GEN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3. 2 SAMPLE DATABASE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 3. 3 STEPS OF REPORTING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 3. 4 MY FIRST REPORT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3. 4. 1 Creating a report template. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3. 4. 2 Creating a WRF file. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 3. 4. 3 Configuring the report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [. . . ] For more detail information about (Formula) field , refer to Microsoft Word Help. 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. @F2=REPORT(table=Report2 type=var cell=A2) SELECT c. CategoryName, SUM(d. Quantity), Sum(d. UnitPrice * d. Quantity * - 45 - (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('$ReportMonth-01') AND MONTH(o. OrderDate) = MONTH('$ReportMonth-01') GROUP BY c. CategoryName ORDER BY c. CategoryName ; 3. The report generated b y WDReportGen is as follows. 5. 8 Sorting, Grouping and Totaling 5. 8. 1 Sorting data 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. Use the ORDER BY clause to have your - 46 - results displayed in a sorted order. 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. ORDER BY HireDate DESC; /* descending sort */ 5. 8. 2 Totaling 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. 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. You can use aggregate functions in SQL statement, such as COUNT, SUM, AVG, MAX, MIN. (1) In the fixed table report, you can add total directly using a separate SQL. (2) In the variable table report, 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. - 47 - 5. 8. 3 Grouping data and subreports 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 VARIABLE TABLE REPORT function. For more detail information, refer to " Group Variable Table Report"in this document. A subreport would typically be used to perform one-to-many lookups such as Customer / Order / OrderDetails . For more detail information, refer to the samples invoice. wrf, product_catalog. wrf and sales_detail. wrf within WDReportGen. 5. 8. 4 Subtotaling 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. [. . . ] c Display the date as ddddd and display the time as ttttt, in that order. Display only date information if there is no fractional part - 105 - to the date serial number; display only time information if there is no integer portion. d dd ddd dddd ddddd Display the day as a number without a leading zero (1 ­ 31). Display the date as a complete date (including day, month, and year), formatted according to your system's short date format setting. [. . . ]