Creating a quick report from any cursor


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


 

Comments (1)

  1. Alex Sosa says:

    Good example. Thanks for examples of simple yet powerful new functionality like this.

Skip to main content