I really could do with a bit of advice/help.
I'm currently in the process of moving a myDBR instance from MySQL to SQL Server.
I have several crosstab reports and they are all give me a headache in converting - primarily to do with the group by statement.
Here is the MySQL code:
select b.tblGroup_Cat_Desc as "[MCat]",
c.subcategory as "[SCat]",
d.tblcompanies_comp as "Company[Comp]",
if(b.tblGroup_CAT_ID in (20),ifnull(sum(a.pl_val),0)*-1,ifnull(sum(a.pl_val),0)) as "Value, $[Val]",
a.pl_company as "[bcomp]",
a.pl_period as "[bdate]",
a.pl_subcat as "[scat]",
a.pl_maincat as "[mcat]",
f.b_maincat as "[bcat]",
inRType as "[rType]",
if(f.b_maincat=20,ifnull(f.b_val,0),ifnull(f.b_val,0)*-1) as "Budget, $[BVal]",
null as "Variance[bVar]",
null as "% Var.[pVar]"
from tmp_pandl a
join tblgroup_coa_category b on a.pl_maincat=b.tblGroup_CAT_ID
left outer join tblgroup_coa_subcat c on a.pl_subcat=c.subcatID
join tblcompanies d on a.pl_company=d.tblcompanies_ID
join (SELECT pl_subcat as pcat FROM tmp_pandl where iuser=inLogin group by 1 HAVING sum(pl_val) !=0) e on a.pl_subcat=e.pcat
left outer join budget_sum f on a.pl_subcat=f.b_subcat
where a.iuser=inLogin /*and a.pl_val!=0*/ and d.Ops=0
group by a.pl_company,a.pl_subcat
order by d.tblcompanies_ID,b.tblGroup_CAT_ID,c.subcatID;
The issue I'm getting is that T-SQL requires me to add nearly every field in the above query to the group by statement. All the fields from Value to Budget are hidden are are used as parameters for linked reports. The above works as expected in MySQL but I have to add so many fields to the group by statement in T-SQL I end up getting duplicate lines in the report.
As I said I have so many similar reports to I was hoping you could give me a steer on the best way to rewrite the above.