ties
Chapter 4 : Designing Target Reports
4. Master-detail report (Group Above Style) – Single Query
▶Target report: Sales order list by State, City, Product Category
▶Implementation guide:
① Display year and country from external parameter (Design dataset with year & country parameter).
② Set group break for State Province, City, Product Category.
(Use Insert Band > Upper Group Header Band on the data band.
Set the group footer band that is not required to Invisible.
③ Set the format of total and subtotals of groups (use summary label) to ‘$#,###’ and to right align-ment.
④ Use table component and fixed table component (use Insert Connected Table of fixed table)
⑤ Display current State, City, Category on every page (use Fix Prior Group Header of band)
⑥ Learn how to create master-detail from one dataset using group header/footer band.
①
②
③
④
Table component④ Fixed table compo-nent
1 Page 2 Page
⑤
32
▶Query:
SELECT Address.StateProvince, Address.City,
Category.CategoryName, OrderHeader.SalesOrderID,
Format(OrderHeader.OrderDate, 'yyyy-mm-dd') as OrderDate, Product.ProductName,
SUM(OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount) as Price, SUM(OrderDetail.OrderQty) as OrderQty,
SUM((OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount)*OrderDetail.OrderQty) AS TotalAmount FROM OrderHeader, OrderDetail, Address, Product, SubCategory, Category
WHERE OrderHeader.SalesOrderID = OrderDetail.SalesOrderID AND OrderHeader.AddressID = Address.AddressID AND
OrderDetail.ProductID = Product.ProductID AND
Product.SubCategoryID = SubCategory.SubCategoryID AND SubCategory.CategoryID = Category.CategoryID AND
DatePart('yyyy', OrderHeader.Orderdate) = '#OZParam.Year#' AND Address.Country = '#OZParam.Country#‘
group by
Address.StateProvince, Address.City,
Category.CategoryName, OrderHeader.SalesOrderID,
Format(OrderHeader.OrderDate, 'yyyy-mm-dd'), Product.ProductName
33
5. Master-detail report (Group Above Style) – Multi Query
▶Target report: Packing list by invoice
▶Implementation guide:
① Configure Invoice as master and Packing list as detail with Invoice NO key (use Master band Settings of the de-tail dataset)
② Display total by Invoice (use summary label), Set the format of total to ‘$#,###’ and to right alignment.
③ Display Freight and Total Amount (= Total + Freight)
④ Use table component and fixed table component
⑤ Learn how to create master-detail between two dataset by setting the master band for the detail band.
①
Set master bandkey: Invoice No
②
③
④
Table component④
Fixed table compo-nent1 Page 2 Page
34
▶Query:
SELECT OrderID, OrderDate, RequiredDate, ShippedDate, ShipVia, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry,
Trim(ShipRegion& ' ' & ShipCity& ' ' & ShipAddress) AS ShipFullAddr, Freight,
O.CustomerID, C.CompanyName, C.ContactName, C.ContactTitle, C.PostalCode, C.Phone, C.Fax,
Trim(C.Region & ' ' & C.City & ' ' & C.Address) AS FullAddr, Country,
S.CompanyName AS ShipperCompany
FROM "Invoice Orders" O, "Invoice Customers" C, "Invoice Shippers" S WHERE O.CustomerID = C.CustomerID AND O.ShipVia= S.ShipperID order by OrderID
SELECT O.OrderID, P.ProductName, OD.UnitPrice, OD.Quantity, OD.Discount,
(OD.UnitPrice*OD.Quantity*(1-OD.Discount)/100)*100 AS ExtendedPrice FROM "Invoice Orders" O, "Invoice Order Details" OD, "Invoice Products" P WHERE O.OrderID = OD.OrderID AND
OD.ProductID = P.ProductID
order by O.OrderID
35
6. Multi column report
▶Target report: Mailing label printing
▶Implementation guide:
① Mailing label with customer address using region
② Label with Zip code, fixed table with address and name
③ Print mailing label horizontally first, and down to the next line, print horizontally again ( Out Di-rection property of the region is Horizontal Limited )
④ Horizontal and vertical spacing between mailing labels (Horizontal Gap and Vertical Gap)
▶Query:
SELECT PostalCode,
Address + ' ' + City + ' ' + Country as fullAddress, ContactName
FROM "Invoice Customers"
③ Label printing direction
②
Label and fixed table① Region component
④
36
7. Fixed form report – using script
▶Target report: Calendar for any month, any year
①
▶Implementation guide:
① Define Year and Month as input parameters. Parameters are used for Date functions in the script.
(No need for dataset, hence create form parameter with Report Designer)
② Create 7*6 Table, display date on each cell using script.
③ Create Search toolbar for parameters.
②
③
37
8. Drill-down report
▶Target report: Sales by country report, drill down from country on the chart or table to country detail
①
②
③
38
▶Implementation guide:
① Create the chart showing sales by country with chart wizard Chart Type tab : Pie chart
X Axis tab: Country Y axis: SUM(TotalSales) Legend tab: bottom location, symbol width 8
Data Label tab: show tooltip, Data to Show: Item name and Percentage Pie tab: Data label Position: Show With Link Line
Write OZ script to link the pie to the detail report
(Link to Target report 4. Master-detail report(Group Above Style) – Single Query)
② Create data table for the chart
Write OZ script to link the Country cell to the detail report (same link as chart)
③ Set viewer option to show report tree on the viewer.
Write Java script for OnStartUp event of ReportTemplate as below:
ReportTemplate.SetReportOption("viewer.smartframesize", "true");
ReportTemplate.SetReportOption("viewer.showtree", "true");
④ Reference the external parameter Year in the query.
▶Query:
SELECT Address.Country,
SUM(OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount) as UnitPrice, SUM(OrderDetail.OrderQty) as OrderQty,
SUM((OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount)*OrderDetail.OrderQty) AS TotalSales
FROM OrderHeader, OrderDetail, Address
WHERE OrderHeader.SalesOrderID = OrderDetail.SalesOrderID AND OrderHeader.AddressID = Address.AddressID AND
DatePart('yyyy', OrderHeader.Orderdate) = '#OZParam.Year#‘
GROUP BY Address.Country
39
9. TOC(Table Of Content) link report
▶Target report: Select TOC item from the Sales Summary page and move to the detailed sales reports
② TOC page and detail pages
▶Implementation guide:
① Show report list in the OZ Viewer Tree and move to detailed report by clicking the TOC labels in the TOC page.
Set label’s TOC Level property.
If TOC Level is greater than -1, TOC label’s captions are listed in the tree of OZ Viewer when previewing the report. This numeric value means a level in the tree and 0 means the highest level. The root node of the tree is the report template object and shown with its Display Name property.
② Report consists of the TOC page, “1. Sales Summary” page, “2. By Sales Person” page, “3. By Coun-try” page, “4. By“ page. Each page is an independent report object so the report includes 5 report object.
③ Create the link from TOC labels to the target page of the report.
Create script on each source TOC label implementing link to the target TOC label using SetLink-Toc(TocName) function.
SetLinkToc function provides navigation through the tree and move to the target in the report.
Set the TocName to the target label.
③ Click a TOC item and move to the linked page(report)
①
40
▶Implementation guide: (Sales Summary)
① Horizontal bar chart for Top 10 Sales Persons and Top 10 Customers. Set the maximum row count to 10.
② Pie chart for Sales by Product Category , data table for the chart, link from Category Name of the table to the 4. By Category of the category (SetLinkToc function)
③ Pie chart for Sales by Country, data table for the chart, link from Country Name of the table to the 3.
By Country of the country (SetLinkToc function)
▶Implementation guide: (2. By Sales Person)
① Sales Data table & chart with Grouping by Sales Person Name, create TOC labels for all Sales Person Name (Use Group band, Sort by Sales Person Name)
② Print 3 Sales Persons in one page and skip to the next page (implement using script on the group footer)
41
▶Implementation guide: (3. By Country, 4. By Category)
① Sales Data table & chart with Grouping by Country, create TOC labels for all Country Name (Use Group band, Sort by Country Name)
② Sales Data table & chart with Grouping by Category, create TOC labels for all Category Name (Use Group band, Sort by Category Name)
③ Print one Country or one Category in the one page and skip to the next page (Use Force New Page property of the group footer)
④ Always print data table and chart parallely in the same page in any case. (If the table is long and over one page, the chart is printed in the next page. To avoid this, create 2 regions in the page and create table and chart in each region independently.
42
▶Query:
①
SELECT * FROM (
SELECT
SalesPerson.SalesPersonName,
SUM((OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount)*OrderDetail.OrderQty) AS TotalSales FROM OrderDetail, OrderHeader, SalesPerson
WHERE OrderHeader.SalesPersonID = SalesPerson.SalesPersonID AND OrderHeader.SalesOrderID = OrderDetail.SalesOrderID AND
Format(OrderHeader.Orderdate, 'yyyy-mm') = '#OZParam.YearMonth#' GROUP BY SalesPerson.SalesPersonName
) T
ORDER BY TotalSales desc
②
SELECT * FROM (
SELECT
Left(Customer.CustomerName, 20) AS CustomerName,
SUM((OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount)*OrderDetail.OrderQty) AS TotalSales FROM OrderDetail, OrderHeader, Customer
WHERE Customer.CustomerID = OrderHeader.CustomerID AND OrderHeader.SalesOrderID = OrderDetail.SalesOrderID AND
Format(OrderHeader.Orderdate, 'yyyy-mm') = '#OZParam.YearMonth#' GROUP BY Customer.CustomerName
) T
ORDER BY TotalSales desc
③
SELECT * FROM (
SELECT
Category.CategoryName,
SUM((OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount)*OrderDetail.OrderQty) AS TotalSales
FROM OrderDetail, Product, OrderHeader, SubCategory, Category WHERE OrderHeader.SalesOrderID = OrderDetail.SalesOrderID AND
OrderDetail.ProductID=Product.ProductID AND
Product.SubcategoryID = SubCategory.SubcategoryID AND SubCategory.CategoryID = Category.CategoryID AND
Format(OrderHeader.Orderdate, 'yyyy-mm') = '#OZParam.YearMonth#' GROUP BY Category.CategoryName
) T
ORDER BY TotalSales desc
④
SELECT * FROM (
SELECT
Address.Country,
SUM((OrderDetail.UnitPrice - OrderDetail.UnitPriceDiscount)*OrderDetail.OrderQty) AS TotalSales
FROM OrderDetail, OrderHeader, Address
WHERE OrderHeader.AddressID=Address.AddressID AND OrderHeader.SalesOrderID = OrderDetail.SalesOrderID AND
Format(OrderHeader.Orderdate, 'yyyy-mm') = '#OZParam.YearMonth#' GROUP BY Address.Country
) T
ORDER BY TotalSales desc
43
⑤
SELECT
Format(OrderHeader.Orderdate, 'yyyy-mm') AS OrderDate, OrderHeader.SalesOrderID,
SalesPerson.SalesPersonName,
Address.Country, Address.StateProvince, Address.City,
Category.CategoryName, SubCategory.SubCategoryName, Product.ProductName, Count(OrderHeader.SalesOrderID) AS NOrders,
SUM((UnitPrice - UnitPriceDiscount)*OrderQty) AS TotalSales, SUM(OrderQty*Product.StandardCost) AS ProductCost, TotalSales-ProductCost AS NetSales
FROM OrderHeader, OrderDetail, Address, Product, SubCategory, Category, Customer, Sales-Person
WHERE OrderHeader.SalesOrderID = OrderDetail.SalesOrderID and OrderHeader.AddressID = Address.AddressID and
OrderHeader.CustomerID = Customer.CustomerID AND
OrderHeader.SalesPersonID = SalesPerson.SalesPersonID AND OrderDetail.ProductID=Product.ProductID and
Product.SubCategoryID = SubCategory.SubCategoryID and SubCategory.CategoryID = Category.CategoryID and
Format(OrderHeader.Orderdate, 'yyyy-mm') = '#OZParam.YearMonth#'
GROUP BY Format(OrderHeader.Orderdate, 'yyyy-mm'), OrderHeader.SalesOrderID, SalesPerson.SalesPersonName,
Address.Country, Address.StateProvince, Address.City,
Category.CategoryName, SubCategory.SubCategoryName, Product.ProductName
44
10. Multi language report
▶Target report: Set the report to support multiple language resource for global business report .
English resource of a
re-port Korean resource of a report
▶Implementation guide:
① Change the resource of target report 5. Master -detail report to multi language resource.
② OZ Report Designer allow the user to convert all of general labels to resource labels.
OZ Report Designer allow to change language and check it out
45
Target report Description Report file