| Chapter 7 Advanced Reports |
In one report building process, PTReportCom 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 presentation with several tables.
Example
This example executes multiple SQL statements to create a report.
This example executes multiple SQL statements to create a report.
1. Create the template in Microsoft PowerPoint.
/*********************************************************
Compare with Last Month by Categories
*********************************************************/
/* Calculate the total in the current month */
@F8_1=REPORT(slide=8 type=fix cell=B4)
SELECT 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')
/* Calculate the total in the last month */
@F8_2=REPORT(slide=8 type=fix cell=D4)
SELECT 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#
/* Drop table tmp_category_sales */
@F8_3=EXECSQL()
DROP TABLE tmp_category_sales
/* Create table tmp_category_sales */
@F8_4=EXECSQL()
CREATE TABLE tmp_category_sales (
CategoryID INTEGER,
Quantity INTEGER,
Amount MONEY
)
/* Get the sales amount by categories in the current month */
@F8_5=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 */
@F8_6=REPORT(slide=8 type=var cell=A3)
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 */
@F8_7=EXECSQL()
DELETE FROM tmp_category_sales
/* Get the sales amount by categories in the last month */
@F8_8=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 */
@F8_9=REPORT(slide=8 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.
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.
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.
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, you can use aggregate functions in SQL statement, such as COUNT, SUM, AVG, MAX, MIN.
1. In the fixed table report, you can add a total directly using a separate SQL.
2. In the variable table report, you must add the 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.
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. For more detail information, refer to "GroupTableReport Method" and "Group Table 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.
For more detail information, refer to the samples customer_profile.ptr, product_catalog.ptr and sales_detail.ptr within PTReportCom.
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. 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, 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 sales_detail.ptr within PTReportCom.
To make eye-catching reports, you can add pictures to your reports. You can directly insert pictures into the report template in Microsoft PowerPoint. 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 a presentation, refer to Microsoft PowerPoint Help.
Except for inserting the static pictures during report design, you may insert pictures during report building process. PTReportCom can put the graphics files into the report, and support all graphics file format that Microsoft PowerPoint support.
To insert pictures into a report using PTReportCom, 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 size in the report template
To specify the 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, you write a formatting expression in the shape or text box. PTReportCom 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 PTR file, and identify the image fields using the IMAGE argument. For example,
@F1=Report(slide=1 ... image=photo)
4. Use PTReportCom to generate report with pictures
PTReportCom 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 "", PTReportCom will return "". PTReportCom 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.ptr, product_catalog.ptr within PTReportCom.