dbr.sum with dynamic sql

(3 posts) (2 voices)

Tags:

  1. ajdjackson, Member

    Hi

    I can't seem to get dbr.sum to work if I use a prepare statement.


    declare v_table varchar(30); select mis_journal into v_table from tblsage_import_criteria where mis_comp=inComp; select 'dbr.title',concat((select accountname from tblcoa where AccountNumber=inNom),' Transactions');
    select 'dbr.sum','SVal';
    select 'dbr.colstyle','SVal','%0.0f;-;[color:red](%0.0N);'; SET @t1 =CONCAT("select a.Details as 'Tran. Details[Desc]',
    date_format(a.`Date`,'%m/%d/%Y') as 'Tran. Date[TDate]',
    a.`Type` as 'Type',
    a.Amount/d.fx_rate as 'Value, $[SVal]'
    from ",v_table," a
    join tblcompanies c on c.tblcompanies_ID = ",inComp,"
    join tblfx_rate d on c.tblcompanies_cur=d.fx_cur
    where last_day(a.Date)<=last_day(date_add(curdate(),interval -1 month)) and d.fx_date=last_day(date_add(curdate(),interval -1 month)) and a.nominal_code=",inNom,"
    order by 2"); PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;

    Any thoughts?

    Thanks

    Jake

  2. myDBR Team, Key Master

    Should work as expected.

    What is the problem, it does not appear / calculates incorrectly?

    --
    myDBR Team

  3. ajdjackson, Member

    Hi

    My fault - it was the carry over from hiding the summary row in the main report. Now fixed due to me understanding how to limit summary levels.

    Thanks

    Jake


Reply

You must log in to post.