Subtotal not always being generated

(3 posts) (2 voices)

Tags:

No tags yet.

  1. shhedrick, Member

    I have a report with 2 dbr.hdr lines. See the report code below. Whenever the first HDR changes but the value of the second HDR does not, the subtotal for the second HDR is not generated before the total for the first hdr.

    I will send a report sample separately.

    MyDBR 3.5.1 Build 1504

    Thanks.

    DROP PROCEDURE IF EXISTS sp_DBR_SWEPI_LoadData
    $$
    CREATE PROCEDURE sp_DBR_SWEPI_LoadData(rprtCompanyID int, rprtSiteName varchar(100), rprtProductID int, startDate date, endDate date)
    BEGIN

    DECLARE rprtMainTitle, lineBreak, rprtSubTitle, rprtCompanyName, allSiteName, rprtProductName varchar(100);

    SET allSiteName = ' All Sites';

    if rprtCompanyID = 0 then
    SET rprtCompanyName = 'All Companies';
    else
    SELECT companyName
    FROM hrManagement.company
    WHERE companyID = rprtCompanyID
    INTO rprtCompanyName;
    end if;

    if rprtProductID = 0 then
    SET rprtProductName = 'All Products';
    else
    SELECT productName
    FROM hrManagement.product
    WHERE productID = rprtproductID
    INTO rprtProductName;
    end if;

    SET rprtMainTitle = 'dbr.purehtml:Hydro Recovery Blossburg';
    SET lineBreak = '
    ';

    IF (endDate IS NULL or endDate = startDate) THEN SET rprtSubTitle = CONCAT_WS(' ','Load Data for', DATE_FORMAT(startDate, '%M %e, %Y'));
    ELSE SET rprtSubTitle = CONCAT_WS(' ','Load Data from', DATE_FORMAT(startDate, '%M %e, %Y'), 'to', DATE_FORMAT(endDate, '%M %e, %Y'));
    END IF;

    IF endDate IS NULL THEN SET endDate = startDate;
    END IF;

    select 'dbr.export.options', 'orientation', 'landscape';
    select 'dbr.export.options', 'paper_size', 'LETTER';

    select 'dbr.title', CONCAT(rprtMainTitle,lineBreak,rprtSubTitle,lineBreak,rprtCompanyName,' - ',rprtProductName);

    select 'dbr.hdr', '[pad]';
    select 'dbr.hdr', '[io]';
    select 'dbr.count', '[ld]';
    select 'dbr.sum', '[bbls]';
    select 'dbr.hidecolumns', 1;

    select 'dbr.editable', '[ld]', 'sp_DBR_TDLoadID_Update', 'tdID=[id]', 'newData<=[ld]';
    select 'dbr.report', 'sp_DBR_TDProduct_Update', '[pd]', 'popup', 'tdID=[id]', 'newData<=[pd]';
    select 'dbr.report', 'sp_DBR_TDFluidType_Update', '[ft]', 'popup', 'tdID=[id]', 'newData<=[ft]';
    select 'dbr.report', 'sp_DBR_TDTruckUpdate', '[lic]', 'popup', 'tdID=[id]', 'newLicenseNo<=[lic]';
    select 'dbr.report', 'sp_DBR_TDUpdate_SWEPI_ExtraData', '[SWEPITkt]', 'popup', 'tdID=[id]', 'newSWEPI<=[SWEPITkt]',
    'newMan<=[man]', 'newNum<=[SWEPITrk]', 'newOSR<=[SWEPIOSR]';

    select 'dbr.report', 'sp_DBR_TransactionDetailRecord', 'tdID=[id]';
    select 'dbr.report', 'sp_DBR_Truck_Detail', 'truckKey=[lic]';
    select 'dbr.report', 'sp_DBR_Pad_Insert';

    select h.SITENAME as 'Pad/Well Name[pad]',
    if(h.TRANSTYPE='I','Inbound','Outbound') as 'In/Out[io]',
    h.PRODUCT as 'Product[pd]',
    h.FLUIDTYPE as 'Fluid Type[ft]',
    DATE_FORMAT(h.ACTIVITYDAY, '%b %e') as 'Activity Day[act]',
    ROUND(h.ESTSITEVOLUME/42,2) as 'Volume (bbls)[bbls]',
    h.extraData1 as 'SWEPI Ticket #[SWEPITkt]',
    h.extraData2 as 'SWEPI Tracking #[SWEPITrk]',
    h.extraData3 as 'SWEPI OSR/Ordered By[SWEPIOSR]',
    h.DRILLERMANIFEST as 'Manifest #[man]',
    h.TRAILERCOMPANYNAME as 'Trucking Company[trkco]',
    IF(h.TRAILERLICENSENO<>'' and h.TRAILERLICENSENO IS NOT NULL,h.TRAILERLICENSENO,'none') as 'License[lic]',
    h.LOADID as 'Load ID[ld]',
    h.id as 'Id[id]'
    from xhrBlossburg.hrTransactionDetail h
    where h.ACTIVITYDAY between startDate and endDate
    and (h.USEWEIGHT is null or h.USEWEIGHT <> 1)
    and (h.SCHEDULINGCOMPANYNAME = rprtCompanyName)
    AND (h.SITENAME = rprtSiteName OR rprtSiteName = allSiteName)
    and (h.PRODUCT = rprtProductName or rprtProductID = 0)
    order by h.SITENAME, h.TRANSTYPE, h.PRODUCT, h.FLUIDTYPE, h.ACTIVITYDAY;

    END
    $$

  2. myDBR Team, Key Master

    Do you have few lines of data or an image that would demonstrate the problem?

    --
    myDBR Team

  3. shhedrick, Member

    I sent a report sample to support@mydbr.com.

    If you don't already have it, please let me know.

    Thanks.


Reply

You must log in to post.