1. 

   ajdjackson, Member

   Hi

   I've a query here that I can't seem to solve.

   I have created a crosstab as below and I would like to link a report of one of the columns.

   I can't find a way to select the column I want to link from. In y case it's the column named 'Booked #' and it's the 4th column.

   I've tried select 'dbr.report', 'sp_DBR_Mon_ExpComp',4,'popup'; and select 'dbr.report', 'sp_DBR_Mon_ExpComp','[Booked #]','popup'; but these don't work. The only thing I can det to work is select 'dbr.report', 'sp_DBR_Mon_ExpComp','[Val]','popup'; but this makes all the Val columns links.

   Any thoughts on how I would do this? My only other thought is to create a temp table and populate the various columns and treat as a normal report.

   Cheers

   Jake

   select 'dbr.subtitle',concat(year(current_date),' Sales Performance'); select 'dbr.crosstab','Cat'; select 'dbr.crosstab.col','Sold #','Sold £','Booked #','Booked £','Total #','Total £';

   select 'dbr.colstyle','Val','[border-right-style:solid;border-right-color:#CCCCCC;border-right-width:1px;width:100px;]%0.0f'; select 'dbr.colstyle','Site','[border-right-style:solid;border-right-color:#CCCCCC;border-right-width:1px;text-align:right;]'; select 'dbr.sum','Val'; select 'dbr.report', 'sp_DBR_Mon_ExpComp','[Val]','popup';

   select c.Site_Name as 'Site[Site]', 'Sold #' as '[Cat]', count(a.tblSalesInv_XIDJob) as '[Val]' from hilmark.tblsalesinv_j a join hilmark.tblsitesplotsjake b on a.tblSalesInv_XIDJob = b.tblContractXIDJobNo join hilmark.tblsitesjake c on b.Site_ID = c.Site_ID where year(a.tblsalesinv_date) = year(current_date) group by b.Site_ID

   union

   select c.Site_Name as 'Site[Site]', 'Sold £' as '[Cat]', sum(a.tblsalesinv_net) as '[Val]' from hilmark.tblsalesinv_j a join hilmark.tblsitesplotsjake b on a.tblSalesInv_XIDJob = b.tblContractXIDJobNo join hilmark.tblsitesjake c on b.Site_ID = c.Site_ID where year(a.tblsalesinv_date) = year(current_date) group by b.Site_ID

   union

   select b.Site_Name as 'Site[Site]', 'Booked #' as '[Cat]', count(a.tblContractXIDJobNo) as '[Val]' from hilmark.tblsitesplotsjake a join hilmark.tblsitesjake b on a.Site_ID = b.Site_ID where year(a.Plot_ExpHOD) = year(current_date) and a.tblContractXIDJobNo not in (select tblSalesInv_XIDJob from hilmark.tblsalesinv_j) group by a.Site_ID

   union

   select b.Site_Name as 'Site[Site]', 'Booked £' as '[Cat]', sum(a.Plot_ExpSP) as '[Val]' from hilmark.tblsitesplotsjake a join hilmark.tblsitesjake b on a.Site_ID = b.Site_ID where year(a.Plot_ExpHOD) = year(current_date) and a.tblContractXIDJobNo not in (select tblSalesInv_XIDJob from hilmark.tblsalesinv_j) group by a.Site_ID

   union

   select b.Site_Name as 'Site[Site]', 'Total #' as '[Cat]', count(a.tblContractXIDJobNo) as '[Val]' from hilmark.tblsitesplotsjake a join hilmark.tblsitesjake b on a.Site_ID = b.Site_ID where year(a.Plot_ExpHOD) = year(current_date) group by a.Site_ID union

   select b.Site_Name as 'Site[Site]', 'Total £' as '[Cat]', sum( case when a.tblContractXIDJobNo in (select tblsalesinv_xidjob from hilmark.tblsalesinv_j) then c.tblsalesinv_net else a.Plot_ExpSP end) as '[Val]' from hilmark.tblsitesplotsjake a join hilmark.tblsitesjake b on a.Site_ID = b.Site_ID left outer join hilmark.tblsalesinv_j c on a.tblContractXIDJobNo = c.tblsalesinv_xidjob where year(a.Plot_ExpHOD) = year(current_date) group by a.Site_ID

   order by 1;
2. 

   myDBR Team, Key Master

   A crosstab is a structure where same columns repeats itself (like monthly sale). It is not intended to display different items (like in your case). You do not need crosstab for that, as you can simply query the values in different columns. Will make your query more efficient as well as you do not need so many separate queries.

   --
   myDBR Team

Reply

You must log in to post.