Good morning
Yes - I did have mcat (plus a few others hidden - for use ass parameters for linked reports) in the crosstab columns. I have now moved these to before the crosstab and all is now working as expected.
Before I did that I tried the dbr.hmax solution and that worked also - although I do not understand why that should work!
My second question was - how do I do something similar to:
<?php
if ($a > $b)
echo "a is bigger than b";
?>
that is - a simple IF with no ELSE.
No biggie as I removed the 0 with dbr.colstyle.
Here is the full query:
select 'dbr.subtitle','click a value for analysis';
select 'dbr.crosstab','Comp';
select 'dbr.crosstab.title','Operational Companies';
select 'dbr.hdr','MCat';
select 'dbr.hdr','SCat';
select 'dbr.hsum','Val','bud','bvar','tsales';
select 'dbr.sum','Val','bud','rat';
select 'dbr.summary.options','limit_summary_level','=1';
select 'dbr.summary.options','skip_single_line_summary';
select 'dbr.colstyle','MCat','[font-weight:bold;min-width:75px;max-width:175px;font-size:1.2em;]';
select 'dbr.colstyle','SCat','[font-weight:bold;min-width:150px;max-width:250px;text-align:right;border-right:1px solid #a9a9a9;]';
select 'dbr.colstyle','Val','bud','bvar','[min-width:70px;text-align:right;]%0.0f;[min-width:70px;text-align:right;]-;[min-width:70px;color:red;text-align:right;](%0.0N);';
select 'dbr.colstyle','rat','[width:35px;text-align:right;]%0.1f;[width:35px;text-align:right;] ;[width:35px;color:red;text-align:right;](%0.1N);';
select 'dbr.css','th[rowspan="2"].cell_ct_top.align_c {background: url("../user/images/MASTER1.png") center center no-repeat;}';
select 'dbr.css','.rbold {border-top:2px solid black;border-bottom:2px solid black;font-weight:bold;font-size:1.2em;}';
select 'dbr.css','.bcolor td {background-color:#e6e6e6;}';
select 'dbr.css','td.summary_level1 {font-weight:bold;border-top:1px solid black;border-bottom:2px solid black;text-align:right;}';
select 'dbr.hidecolumn','bdate','scat','mcat','rType','bcomp','tsales';
select 'dbr.calc','bvar','[mcat]<>20 ? [bud]-[Val] : ([bud]-[Val])*-1';
select 'dbr.calc','rat','[mcat]<>20 ? ([Val]/[tsales])*-100:0';
select 'dbr.hnull','rat';
select 'dbr.report','sp_DBR_PL_SubCat_Det','[Val]','popup','inComp=bcomp','inSCat=scat','inDate=bdate','inType=rType', 'show_link=[mcat]!=50&&[mcat]!=60&&[mcat]!=35';
with cte_psum (pl_company,pl_maincat,pl_subcat,pl_period,pl_val)
as
(select pl_company,pl_maincat,pl_subcat,pl_period, sum(pl_val)
from dbo.tmp_pandl
where iuser=@inLogin
group by pl_company,pl_maincat,pl_subcat,pl_period),
cte_tsales (pl_company,tsales)
as
(select pl_company,sum(pl_val)
from dbo.tmp_pandl
where pl_maincat=20 and iuser=@inLogin
group by pl_company)
select b.Rep_Desc as "[MCat]",
c.Rep_Desc as "[SCat]",
a.pl_period as "[bdate]",
a.pl_subcat as "[scat]",
a.pl_maincat as "[mcat]",
@inRType as "[rType]",
d.tblcompanies_comp as "Company[Comp]",
iif(b.Rep_ID in (20),isnull(a.pl_val,0)*-1,isnull(a.pl_val,0)) as "Value, $[Val]",
null as "Ratio,%[rat]",
f.bVal as "Budget[bud]",
null as "Variance[bvar]",
a.pl_company as "[bcomp]",
g.tsales as "[tsales]"
from cte_psum a
join dbo.tblfin_rep_grp b on a.pl_maincat=b.rep_id
left outer join dbo.tblfin_repmaincat c on a.pl_subcat=c.rep_id
join dbo.tblcompanies d on a.pl_company=d.tblcompanies_ID
join (SELECT pl_subcat as pcat FROM dbo.tmp_pandl where iuser=@inLogin group by pl_subcat HAVING sum(pl_val)<>0) e on a.pl_subcat=e.pcat
left join #budgets f on b.rep_id=f.bGrp and c.rep_id=f.bCat and a.pl_company=f.bComp
join cte_tsales g on a.pl_company=g.pl_company
where d.Ops=1
order by d.tblcompanies_ID,b.rep_sortorder,c.rep_desc;
One other thing I forgot to mention was the summarylevel_1 total of the Variance column has the wrong sign. Can you see why that is?
Many thanks
Jake
