dbr.calc issue in summary row

(4 posts) (2 voices)
  • Started 4 years ago by ajdjackson
  • Latest reply from myDBR Team
  1. ajdjackson, Member

    Hi

    I've created a simple report that has 7 calculated fields.

    The report works great except that last two calculated fields in the summary row are incorrect - the 'New Land %' and 'Old Land %' columns. They are showing the same value as the 5th calculated field - 'Less Direct %'.

    Any thoughts?

    Cheers

    Jake

    select 'dbr.css', '.title {font-size:2em; font-weight:bold; color:#1254B8;}'; select 'dbr.title',concat('Financial Performance by Site at ',date_format(inDate,'%d-%b-%Y'));

    select 'dbr.colstyle','Svalue','%0.0f'; select 'dbr.colstyle','TSub','%0.0f'; select 'dbr.colstyle','SSold','%0.0f'; select 'dbr.colstyle','SMat','%0.0f'; select 'dbr.colstyle','Site','[color:blue; border-right-style:solid;border-right-color:black;border-right-width:thin;text-align:right]'; select 'dbr.colstyle','SUnits','[border-right-style:solid;border-right-color:black;border-right-width:thin;column-width:25px:word-break:keep-all;]'; select 'dbr.colstyle','TMat','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.0f'; select 'dbr.colstyle','TSpend','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f'; select 'dbr.colstyle','SSiteWide','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.0f'; select 'dbr.colstyle','TCOS','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f'; select 'dbr.colstyle','WIP','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.0f'; select 'dbr.colstyle','Cont','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f'; select 'dbr.colstyle','ContPer','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.2f'; select 'dbr.colstyle','DirPer','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.2f'; select 'dbr.colstyle','NewLand','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.2f'; select 'dbr.colstyle','OldLand','[border-right-style:solid;border-right-color:black;border-right-width:thin;]%0.2f;[border-right-style:solid;border-right-color:black;border-right-width:thin;]-;[color:red;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.2f)'; select 'dbr.footerstyle','background-color: #E0F7FF;font-weight:bold;border-top-style:solid;border-top-color:black;border-top-width:thin;'; select 'dbr.footer.colstyle','OldLand','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.2f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.2f)'; select 'dbr.footer.colstyle','NewLand','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.2f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.2f)'; select 'dbr.footer.colstyle','DirPer','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.2f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.2f)'; select 'dbr.footer.colstyle','ContPer','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.2f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.2f)'; select 'dbr.footer.colstyle','Cont','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','WIP','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','TCOS','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','SSiteWide','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','TSpend','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','TMat','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','SUnits','[border-right-style:solid;border-right-color:black;border-right-width:thin;font-weight:bold]%0.0f;-;[color:red;font-weight:bold;border-right-style:solid;border-right-color:black;border-right-width:thin;](%0.0f)'; select 'dbr.footer.colstyle','Site','[border-right-style:solid;border-right-color:black;border-right-width:thin;]';

    select 'dbr.calc','TSpend','[TSub]+[TMat]'; select 'dbr.calc','TCOS','[SSold]+[SMat]+[SSiteWide]'; select 'dbr.calc','WIP','([TSub]+[TMat])-([SSold]+[SMat]+[SSiteWide])'; select 'dbr.calc','Cont','([Svalue])-([SSold]+[SMat]+[SSiteWide])'; select 'dbr.calc','ContPer','((([Svalue])-([SSold]+[SMat]+[SSiteWide]))/[Svalue])*100'; select 'dbr.calc','DirPer','(((([Svalue])-([SSold]+[SMat]+[SSiteWide]+([Svalue]*.04)))/[Svalue])*100)'; select 'dbr.calc','NewLand','(((([Svalue])-([SSold]+[SMat]+[SSiteWide]+[LNew]+([Svalue]*.04)))/[Svalue])*100)'; select 'dbr.calc','OldLand','(((([Svalue])-([SSold]+[SMat]+[SSiteWide]+[LOld]+([Svalue]*.04)))/[Svalue])*100)';

    select 'dbr.sum','TUnits','SUnits','Svalue','TSub','TMat','TSpend','SSold','SMat','SSiteWide';

    select 'dbr.hidecolumn','LNew'; select 'dbr.hidecolumn','LOld';

    select b.Site_Name as 'Site[Site]', a.FTUnits as 'Unit #[TUnits]', a.FSUnits as 'Units Sold[SUnits]', a.FSValue as 'Sales Value[Svalue]', a.FSub as 'Subbie Total[TSub]', a.Fmat as 'Materials[TMat]', null as 'Total Spend[TSpend]', a.FSubSold as 'Subbie Sold[SSold]', a.FMatSold as 'Materials Sold[SMat]', a.FSiteWide as 'Site Wide[SSiteWide]', a.LandNew as 'Land New[LNew]', a.LandOld as 'Land Old[LOld]', null as 'Total COS[TCOS]', null as 'WIP[WIP]', null as 'Contrib.[Cont]', null as '% Contrib.[ContPer]', null as 'Less Direct %[DirPer]', null as 'New Land %[NewLand]', null as 'Old Land %[OldLand]' from fin_perf_tmp a join hilmark.tblsitesjake b on a.FSiteID=b.Site_ID order by b.Site_Name;

    Posted 4 years ago #
  2. myDBR Team, Key Master

    Check the summary calculation for LOld and LNew.

    --
    myDBR Team

    Posted 4 years ago #
  3. ajdjackson, Member

    Doh!

    Thanks.

    One other thing.

    In dbr.calc how do I construct and IF statement.

    For example if the calculation results in an negative number I would like to display zero such as

    select 'dbr.calc','WIP','if(([TSub]+[TMat])-([SSold]+[SMat]+[SSiteWide])>0, ([TSub]+[TMat])-([SSold]+[SMat]+[SSiteWide]),0)';

    Cheers

    Haje

    Posted 4 years ago #
  4. myDBR Team, Key Master

    You can use the "a > 10 ? 9 : 11" format.

    So the formula would look something like:

    select 'dbr.calc','WIP','(([TSub]+[TMat])-([SSold]+[SMat]+[SSiteWide]))>0 ? (([TSub]+[TMat])-([SSold]+[SMat]+[SSiteWide])) : 0';

    You seem to be pushing the boundaries for the dbr.calc ;)

    --
    myDBR Team

    Posted 4 years ago #

Reply

You must log in to post.