Day 032 - Reports

Owned by David Harms
Last updated: Apr 26, 2013Version comment

The reports tutorial (page 318) begins with some basic definitions: typeface, style, character height, font, etc. It lists the available report types as

  • blank
  • form 
  • label
  • table
  • crosstab
  • multicolumn
  • mailing
  • composite
  • organizer/scheduler

Reports are organized in blocks, which include:

  • start of document
  • page header
  • break header
  • body
  • break footer
  • page footer
  • end of document
  • iteration
  • body complement 

You can create reports using the report editor or via hand code. 

The report previewer supplies watermarking, editing, a thumbnail list, output to different targets and more. 

Here's the New Report window:

Following the tutorial, I created a table report based on a new query, using the by-now-familiar query editor. 

After I created the query the wizard gave me the option of adding a break on the query's sort field:

Next up: choosing which items to display and in which order:

After that I selected the report size, orientation and margins, and then a skin template. 

I've previously mentioned that WinDev gives you the option of letting your users modify queries; the same capability is there for reports, although in most cases I think you'll only want to allow your users to change the layout and not the code. 

I showed the print previewer earlier, because the report tutorial app uses the previewer as the report destination, but you can also specify that you want the report to go directly to a printer, RTF, HTML, or printer-dependent or -independent PDFs. 

This tutorial example is a table report, which is not a free-form report. Instead it's more like a list box inside a report. You can add more columns to the list, and you can change the order of the columns, but you can't move items around randomly. You can create free form reports with WinDev but this isn't one of them. 

If you expand the above image you'll see something a bit odd about the report itself. I've zoomed in to 150% to better see what I'm doing, but that just resulted in pixel expansion - the report wasn't redrawn. It's a raster not a vector representation. No biggie. Just a bit odd. 

Report #2

I created the second tutorial report using a more complicated query:

The SQL generated for this report initially looked something like this (I'm not 100% sure because I had to go back later to get this source, by which time it had changed):

SELECT 
  CUSTOMER.CustomerName AS CustomerName,  
  CUSTOMER.CorporateName AS CorporateName,  
  CUSTOMER.ZipCode AS ZipCode,  
  CUSTOMER.City AS City,  
  ORDERS.OrdersID AS OrdersID,  
  ORDERS.OrderDate AS OrderDate,  
  PRODUCT.Specification AS Specification,  
  ORDERLINE.Reference AS Reference,  
  ORDERLINE.QtyOrdered AS QtyOrdered,  
  ORDERLINE.Amount AS Amount
FROM 
  CUSTOMER,  
  ORDERS,  
  ORDERLINE,  
  PRODUCT

When I validated the query I saw that very brief flash of a window indicating WinDev was looking for joins, which are notably absent in the above SQL. I went back to the SQL after that happened and the SQL looked like this:

SELECT 
  CUSTOMER.CustomerName AS CustomerName,  
  CUSTOMER.CorporateName AS CorporateName,  
  CUSTOMER.ZipCode AS ZipCode,  
  CUSTOMER.City AS City,  
  ORDERS.OrdersID AS OrdersID,  
  ORDERS.OrderDate AS OrderDate,  
  PRODUCT.Specification AS Specification,  
  ORDERLINE.Reference AS Reference,  
  ORDERLINE.QtyOrdered AS QtyOrdered,  
  ORDERLINE.Amount AS Amount
FROM 
  CUSTOMER,  
  ORDERS,  
  ORDERLINE,  
  PRODUCT
WHERE 
  PRODUCT.Reference = ORDERLINE.Reference
  AND    ORDERS.OrdersID = ORDERLINE.OrdersID
  AND    CUSTOMER.CustomerID = ORDERS.CustomerID
ORDER BY 
  CustomerName ASC,  
  OrdersID ASC

The join is implied in the where clause, but I wish I had an actual JOIN in there somewhere for the added precision. 

Although I have sorts on CustomerName and OrdersId, following the tutorial I removed the break on CustomerName:

I then redistributed some of the report elements, putting the customer-specific information in the first (and so far only) break header. That is, the order information will go in the body block, and for each body block there will be a header containing customer information. I expect that because I'm only breaking on OrdersID I'm going to get a full copy of the customer information for each order.

Only one calculation, on the amount. 

The report won't fit on a portrait page:

So I changed it to landscape. 

Here's the report preview. It appears each customer only had one order and the size of the order has somewhat suspiciously been chosen not to exceed the page, but I don't expect any particular problems with page overflow. 

There's more to come on this report tomorrow.Â