Hi, I want to add sub totals on my reports but I have been unable to.
I would like to have a sub total for c.Vendor_ID, c.Store.
Here is my code:
CREATE PROCEDURE sp_DBR_Com_Report(vVendor_ID1 varchar(6),
vVendor_ID2 varchar(6),
vVendor_ID3 varchar(6),
vVendor_ID4 varchar(6),
vVendor_ID5 varchar(6),
vLineCode1 varchar(10),
vLineCode2 varchar(10),
vLineCode3 varchar(10),
vLineCode4 varchar(10),
vLineCode5 varchar(10),
vDateBegin varchar(10),
vDateEnd varchar(10))
BEGIN
SELECT 'dbr.sum', 'Sales','Cost','Freight','Rebate','Promo','Insurance','Profit';
SELECT 'dbr.avg', 'F%', 'R%','P%','I%','%';
SELECT 'dbr.avg_prefix', 'F%', '';
SELECT 'dbr.avg_prefix', 'R%', '';
SELECT 'dbr.avg_prefix', 'P%', '';
SELECT 'dbr.avg_prefix', 'I%', '';
SELECT 'dbr.avg_prefix', '%', '';
SELECT 'dbr.colstyle','Order', '[text-align:right]';
SELECT 'dbr.colstyle','Invoice', '[text-align:right]';
select c.Vendor_ID as 'Vendor',
c.Store as 'Store',
c.Client as 'Client',
c.Date as 'Date',
c.Invoice as 'Invoice',
c.Order as 'Order',
c.Sales as 'Sales',
c.Cost as 'Cost',
c.Freight as 'Freight',
c.Freight_Per as 'F%',
c.Rebate as 'Rebate',
c.Rebate_Per as 'R%',
c.Promo as 'Promo',
c.Promo_Per as 'P%',
c.Insurance as 'Insurance',
c.Insurance_Per as 'I%',
c.Profit as 'Profit',
c.Profit_Per as '%'
from t1.commission_report c
Where (c.Vendor_ID = ifnull(vVendor_ID1, c.Vendor_ID) or
c.Vendor_ID = vVendor_ID2 or
c.Vendor_ID = vVendor_ID3 or
c.Vendor_ID = vVendor_ID4 or
c.Vendor_ID = vVendor_ID5) and
(C.LineCode = ifnull(vLineCode1, c.LineCode) or
C.LineCode = vLineCode2 or
C.LineCode = vLineCode3 or
C.LineCode = vLineCode4 or
C.LineCode = vLineCode5) and
(c.Date >= ifnull(vDateBegin, c.Date) and
c.Date <= ifnull(vDateEnd, c.Date))
Group By c.Vendor_ID, c.Store,c.Client, c.Invoice,c.Order;
