How to add additional rows below the report results?

(5 posts) (2 voices)

Tags:

No tags yet.

  1. Steve44, Member

    I have created the report below which displays data from our loan table and totals at the bottom (I've removed some of the columns in the query below). It is working correctly. Now the manager wants to add two lines at the bottom of the report after the totals. The first line contains an adjustment value, and the second line contains an adjusted total which will be the total minus the adjustment. Right now I have dummy amounts in the vAdjustments and vAdjustedTotal variables. I will load them with good values once I get this working. Right now the report shows only the vAdjustedTotal amount and it appears above the total in the Funding Amount column. How can I get these two values to appear below the total in that column?

    CREATE PROCEDURE sp_DBR_Funding_Report( IN vStartDate DATE, vEndDate DATE) BEGIN

    DECLARE vAdjustments DECIMAL(10, 2); DECLARE vAdjustedTotal DECIMAL(10, 2);

    SELECT 1234.56 INTO vAdjustments; SELECT 201234.56 INTO vAdjustedTotal;

    SELECT 'dbr.title', 'Company Name'; SELECT 'dbr.text', 'Funding Report', 'ParamHeaderClass'; SELECT 'dbr.sum', 'Total Sales Price', 'Down Payment', 'Funding Amount'; SELECT 'dbr.summary.text', 'Funding Amount', vAdjustments; SELECT 'dbr.summary.text', 'Funding Amount', vAdjustedTotal;

    SELECT l.loan_dt AS 'Sales Date', l.client_app_id AS 'App#', l.amount_financed + l.down_payment AS 'Total Sales Price', l.down_payment AS 'Down Payment', l.amount_financed - (l.interest_balance + l.policy_cost) AS 'Funding Amount', l.policy_term AS 'Policy Term' FROM payasr.loan l WHERE l.funding_dt >= vStartDate AND l.funding_dt <= vEndDate ORDER BY l.id;

    END $$

  2. myDBR Team, Key Master

    Steve,
    what you want to do is to have two sets of data as their own header levels: one for the actual data and the second for the adjustment row. This way myDBR will calculate subtotal for the rows and then total with the adjustment.

    To add the set you do:

    select 'dbr.hdr', 'header';
    select 'dbr.hidecolumn', 'header';
    select 'dbr.summary.options', 'skip_single_line_summary'; select 1 as 'header', l.loan_dt AS 'Sales Date', l.client_app_id AS 'App#',....
    from payasr.loan l....
    union
    select 2, null, null , null, null, vAdjustments, 'Adjustment'
    order by 1;

    --
    myDBR Team

  3. Steve44, Member

    Very nice solution. You continue to amaze me. Management would prefer to see only the Funding Amount column in the final total since that's the only column that changes. Is there any way to hide the totals in the other columns?

  4. myDBR Team, Key Master

    With a CSS rule, yes.

    First define a CSS rule that will hide a total row's footer's content (which is wrapped inside a DIV) and add the rule to columns in question:

    select 'dbr.css', '.summary_level0.hidefooter>div {display:none;}';
    select 'dbr.footer.colclass', 'Total Sales Price', 'hidefooter';

    --
    myDBR Team

  5. Steve44, Member

    Excellent. That worked. Thanks again.


Reply

You must log in to post.