dbr.sum with dynamic sql

(3 posts) (2 voices)
  • Started 1 month ago by ajdjackson
  • Latest reply from ajdjackson

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

    Posted 1 month ago #
  2. myDBR Team, Key Master

    Should work as expected.

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

    --
    myDBR Team

    Posted 1 month ago #
  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

    Posted 1 month ago #

Reply

You must log in to post.