| Chapter 7 Advanced Reports |
In one report building process, XLReportCom 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 workbook with several worksheets.
Example
This example executes multiple SQL statements to create a report.
1. Create the template in Microsoft Excel.
2. Write SQL statements in an XRF file.
/*********************************************************
Compare with Last Month by Categories
*********************************************************/
/* Drop table tmp_category_sales */
@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(sheet="Report9" type=var cell=B8 reserve=2)
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(sheet="Report9" type=fix cell=E8)
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.
Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3
A formula can also contain any or all of the following: functions, references, operators, and constants. For more detail information about formulas, functions and references, refer to Microsoft Excel Help.
In a report template file, you can use all kind of Microsoft Excel formulas. And then all formulas in the report template file will be brought to the final report file.
Example
Show Unit Price, Quantity, Discount and Amount. The Amount will be changed if an end user changes Unit Price, Quantity or Discount.
You can use a formula to show Amount.
1. Create a template file as follows, and define the formula "=C2*D2*(1-E2)" in cell F2. You must use the relative reference.
2. Write the report function as follow, and use COPYRANGE to copy the formula to all following cells for each record. For the first record, XLReportCom will directly put data into row 2. For the other records, it will copy row 2 to the current row, and then put data into the current row. So the formula in cell F2 will copy to cell F3, F4... and Microsoft Excel will automatically change the formula to "=C3*D3*(1-E3)" ...
@F1=Report(sheet="Sheet1" cell=A2 copyrange=2:2)
SELECT c.CompanyName AS Customer
,p.ProductName
,d.Quantity
,d.UnitPrice
,d.Discount
FROM Orders o
, Customers c
, OrderDetails d
, Products p
WHERE o.CustomerID = c.CustomerID
AND 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')
ORDER BY 1, 23. Generate the report.
Example
Add totals such as Total Quantity, Total Amount.
You can use the math functions of Microsoft Excel, such as SUM.
1. Create a report template file as follows, define the formula of total quantity as "=SUM(C7:C8)" in cell C9, and the formula of total amount as "=SUM(D7:D8)" in cell D9. You must use the relative reference.
2. Write the report function as follow. When XLReportCom insert some rows according to the records, Microsoft Excel will automatically change the formulas.
@F2=REPORT(sheet="Report2" type=var cell=B7 reserve=2)
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.CategoryName3. The following is the generated report. The formula of total quantity is changed to "=SUM(C7:C14)", and the formula of total amount is changed to "=SUM(D7:D14)".
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.
1. You can add the totals using the math functions of Microsoft Excel, such as SUM. For more detail information, refer to "Using Excel Formulas" 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. 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 invoice.xrf, product_catalog.xrf and sales_detail.xrf within XLReportCom.
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 the functions of Microsoft Excel or aggregate functions in SQL statement.
1. You can add sub-totals using the math functions of Microsoft Excel, such as SUM.
2. If you want to have a total and sub-totals,
3. You can add sub-totals using the aggregate function in SQL statement too.
For more detail information, please refer to the samples invoice.xrf and sales_detail.xrf within XLReportCom.
Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. You can use Microsoft Excel to add sophisticated, colorful charts in your reports. For example, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.
To create a chart in a report, you should create the chart in the template file. You can create a chart on its own sheet or as an embedded object on a worksheet. For more detail information how to create chart, refer to Microsoft Excel Help.
To create a chart in the report template file, you may use some sample data. Using sample data, you can set the various chart options. After you have made the report template, delete the sample data. When you generate the report, XLReportCom will put data into the report, and you will get the chart. For more detail information about charting, refer to the sample monthly_sales.xrf within XLReportCom.
Example
This example uses REPORT function to create the chart: Sales by Categories.
1. Create a report template with a blank worksheet and a blank chart. The worksheet of the chart defined in the report template as follws:
2. Write the report function in an XRF file. The function puts data into the worksheet that provides data for the chart.
@F2=REPORT(sheet="Report2" type=var cell=B7 reserve=2)
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.CategoryName3. Generate the worksheet and the chart.
To make eye-catching reports, you can add pictures to your reports. You can directly insert pictures into the report template in Microsoft Excel. 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 worksheets, refer to Microsoft Excel Help.
Except for inserting the static pictures during report design, you may insert pictures during report building process. XLReportCom can put the graphics files into the report, and support all graphics file format that Microsoft Excel support.
To insert pictures into a report using XLReportCom, 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 positioning option and size in the report template
To specify the positioning option and size, you should write a formatting expression into the cell in the report template file. XLReportCom will get the text of the cell, and insert a picture into the cell according to the instruction in the format expression.
3. Write the report function in an XRF file, and identify the image fields using the IMAGE argument. For example,
@F1=Report(sheet="Employee Profile" ... image=photo)
4. Use XLReportCom to generate report with pictures
XLReportCom 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 "", XLReportCom will return "". XLReportCom 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.xrf, product_catalog.xrf within XLReportCom.