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;
