ansaurus

Question

Answer 1

+1 A:

First, VFP doesn't support a case-when construct at the field level. Additionally, your sub-select at the field level has comma after the from table and before the where, and after the where clause, before the order... such as

select * from MyTable, where SomeCondition, Order by ...

If doing a VFP report, you don't have to manually add lines to your groups into your raw data, that should be done in the report itself by datagrouping and add your ItemID as the basis of the group. Then have your "summable" columns copy/pasted into the group footer band of the report per item. Double click on the field, and for the calculation, tell it to sum(), and to reset at the end of each group (ie: ItemID). Then, include a report summary band. This prints once for the entire report... Like the summary items at the item level, copy/paste again, but put into the report summary band. Double click those elements to sum() and reset at the END OF REPORT.

However, since you are running in VFP9, and you might want to DUMP the data out with the lines already included in their respective positions, I would break into separate queries and merge the results together as I've sampled below. Pre-extract what would be the line-item support for the report data without any necessary aggregates.

SELECT ;
        qItemSaleLines.ItemID, ; 
        qItemSaleLines.SaleID, ; 
        qCustomers.CardRecordID, ; 
        qItems.ItemNumber AS ProdCODE, ; 
        qItems.ItemName AS StkNAME, ; 
        qCustomers.LastName AS CUSTOMER, ; 
        qSales.InvoiceNumber AS SaleINVNo,  ; 
        qSales.InvoiceDate AS SaleDATE, ; 
        qItemSaleLines.Quantity AS SaleQTY, ; 
        qItemSaleLines.TaxExclusiveTotal AS SALE, ; 
        qItemSaleLines.CostOfGoodsSoldAmount AS COGS, ;  
        qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount AS MARGIN, ; 
        (qItemSaleLines.TaxExclusiveTotal - qItemSaleLines.CostOfGoodsSoldAmount) / qItemSaleLines.TaxExclusiveTotal AS MPERCENT, ;
        "1" as TierLevel,;
        "1" as SubTier,;
        SPACE(50) as GroupCaption; 
    FROM ;
        qSales, ;
        qCustomers, ;  
        qItemSaleLines, ;
        qItems, ;
    WHERE ;
            qSales.CardRecordID = qCustomers.CardRecordID ;
        AND qSales.SaleID = qItemSaleLines.SaleID ;
        AND qItemSaleLines.ItemID = qItems.ItemID ;
        AND qSales.InvoiceDate > {^2009-06-30} ; 
    ORDER BY ;
        qItems.ItemNumber, ;
        qSales.InvoiceDate ; 
    INTO ;
        CURSOR C_TmpAllLineItemResults READWRITE 

*/ NOW, get your individual "ITEM GROUP" totals from ABOVE results...
SELECT ;
        TR.ItemID, ; 
        TR.ItemNumber AS ProdCODE, ; 
        TR.ItemName AS StkNAME, ; 
        "1" as TierLevel,;
        "2" as SubTier,;
        "Subtotal by " + TR.ItemNumber as GroupCaption; 
        SUM( TR.SaleQty ) as SaleQty,;
        SUM( TR.Sale ) as Sale,;
    FROM ;
        C_TmpAllLineItemResults TR;
    GROUP BY ;
        1, 2, 3, 4, 5, 6;
    ORDER BY ;
        1;
    INTO ;
        CURSOR C_SubTotalPerItem READWRITE 


*/ NOW, get your REPORT totals from ABOVE results...
SELECT ;
        "2" as TierLevel,;
        "Report Totals " as GroupCaption; 
        SUM( STBI.SaleQty ) as SaleQty,;
        SUM( STBI.Sale ) as Sale,;
    FROM ;
        C_SubTotalPerItem STBI;
    GROUP BY ;
        1, 2;
    INTO ;
        CURSOR C_ReportTotals READWRITE 

*/ Finally, merge them together... since the append from will add records with matching columns,
*/ even though a subtotal cursor doesn't have all the same columns, VFP doesn't care, just 
*/ adds for columns that DO match the table its going into.
SELECT C_TmpAllLineItemResults
APPEND FROM DBF( "C_SubTotalPerItem" )
APPEND FROM DBF( "C_ReportTotals" )

*/ Now, they are all merged... Finally, build an index for your report
INDEX on TierLevel + ItemID + SubTier TAG RptOrder

*/ Now, browse / review the order and adjust as needed...

DRapp 2010-05-14 18:41:28

Drapp, Thanks very much for your help with the SQL.I was getting no where.Tass-man

Tass-man 2010-05-16 23:04:21

Yes, but did either one of them work for you... For your reputation score, its good to select the "check mark" next to the solutions for acceptance recognition. With a low reputation, others may not offer help. Just an FYI to the forum...

DRapp 2010-05-17 10:26:47

Sorry DRApp, I am very new to this and am still tying to figure out how to put it into my sql, so at this stage do not know which will work. I think I need probably need to be spoon fed.

Tass-man 2010-05-18 07:01:55

What I would like to achieve is to show all the individual lines for each ItemID with a subtotal of that group of lines in a separate column, but I fear I do not have the knowledge to do this yet.

Tass-man 2010-05-18 07:04:22

Answer 2

+1 A:

Additionally, if you do just want the totals as columns in the table, you could do something like

select ;
       Tbl.YourColumns,;
       PerItem.TotalPerItem,;
       RptTotal.TotalPerAll;
   from ;
       YourOtherTables Tbl,;
       ( select YourSalesTable.ItemID,;
                sum( CalculatedSales ) as TotalPerItem;
             From;
                YourSalesTable;
             Group by ;
                ItemID ) PerItem,;
       ( select sum( CalculatedSales ) as TotalPerAll;
             From ;
                YourTalesTable ) RptTotal;
   where ;
       YourOtherJoinConditions;
      AND YourOtherTables.ItemID = PerItem.ItemID;
   order by ;
       whatever;
   into ;
       cursor YourReportResults

By doing an SQL-Select as your last 2 tables (one grouping by itemID) will create the total per item. By having the final where join only to the ItemID of the aliased PerItem, you'll get whatever that columns total was. However, since NO join on the aliased RptTotal, you'll get a Cartesian join... but since its always 1 record, each row will have the same value as its "TotalPerAll" column.

I hope these two solutions work for your needs.

DRapp 2010-05-14 18:50:03

ansaurus

tags:

views:

answers:

Sub-total and total columns

related questions