Creating a quick report from any cursor

  • Article

For probably more than a decade, Fox can create a “quick report” from any table or cursor.

CREATE CURSOR TEST (name c(10))

CREATE REPORT dummy FROM test

This will create a default quick report that can be used for any purpose. It’s similar to choosing File->New->Report, then Report->Quick Report, choosing a table, then accepting the default.

Apparently the documentation could be clearer.

This code does a SQL SELECT some data and does a quick report to HTML

OPEN DATABASE (HOME()+"\samples\Northwind\northwind")

 

cWeather="Rain"

*cWeather="Sun"

cCity="Seattle Rain"

IF cWeather $ cCity

      *What customers ordered BEVERAGES in the WESTERN or Northern region? (“BEVERAGES”, “WESTERN” could be textbox values) Something like this

      SELECT Cust.customerid, Cust.companyname,r.regiondescription,cat.categoryname,;

      SUM(Det.quantity*Det.Unitprice* (1-discount)) as CustTotal;

      FROM ;

      Customers Cust ;

      INNER JOIN Orders Ord;

      ON Cust.customerid = Ord.customerid ;

      INNER JOIN Employees Emp;

      ON Emp.employeeid = Ord.employeeid ;

      INNER JOIN EmployeeTerritories ET;

      ON Emp.employeeid = ET.employeeid ;

      INNER JOIN Territories T;

      ON ET.territoryid = T.territoryid ;

      INNER JOIN Region R;

      ON R.regionid = T.regionid ;

      INNER JOIN OrderDetails Det;

      ON Ord.orderid = Det.orderid ;

      INNER JOIN Products P;

      ON P.productid = Det.productid ;

      INNER JOIN Categories Cat ;

      ON Cat.categoryid = P.categoryid;

      WHERE (r.regiondescription="Western" OR r.regiondescription="Northern");

      AND Cat.categoryname="Beverages";

      GROUP BY r.regiondescription,Cust.CustomerId, Cust.companyname,cat.categoryname;

      INTO CURSOR Result

ELSE

  

      * Sales total by customer with PCT of All customer sales

      SELECT ;

      C.customerID, ;

      C.companyname, ;

      SUM(D.quantity*D.unitprice*(1-discount)) AS CustTotal ,;

      (SUM(D.quantity*D.unitprice*(1-discount)) / ;

      (SELECT SUM((quantity*unitprice)*(1-discount)) ;

      FROM OrderDetails D2) ;

      )*100 AS PctTotal ;

      FROM Customers C ;

      INNER JOIN Orders O ;

      ON C.customerID = O.customerID ;

      INNER JOIN OrderDetails D ;

      ON O.orderid = D.orderid ;

      GROUP BY C.customerID, C.companyname; &&, O.orderID

      ORDER BY pctTotal DESC;

      INTO CURSOR Result

ENDIF

CREATE REPORT test FROM Result

oListener=0

DO (_reportoutput) WITH 5,oListener

oListener.TargetFileName = "t.html"

oListener.QuietMode = .T.

REPORT FORM test OBJECT oListener

! t.html