1. 

   ajdjackson, Member

   Hi

   I need a wee bit help or suggestion with a crosstab I hope to create.

   Here's the code so far:

   select 'dbr.crosstab','[yr]'; select 'dbr.crosstab.header', '[metric]'; select 'dbr.crosstab.header.col','Net Sales','% VA','% GM'; select 'dbr.lockcolumns','cust'; select 'dbr.colstyle','val','[width:65px;]%0.0f'; select 'dbr.hidecolumn','cid'; select 'dbr.sum','val';

   select a.[Customer] as "Customer[cust]" ,a.[CustID] as "CustID[cid]" ,'Net Sales' as "[metric]" ,b.[AccYear] as "Financial Year Metrics[yr]" ,sum(a.[Net]) as "[val]" FROM [pharma].[dbo].[vw_mon_inv_sales] a cross apply (select [AccYear], [AccYearDesc] from [pharma].[dbo].[tblAccountYears_v2] b where a.InvMon between b.[AccYearSDate] and b.[AccYearEDate]) b where a.[InvMon]>='2017-07-01' and a.Comp=1 and len(a.FPO)=5 group by a.[CustID],a.[Customer],b.[AccYear] union all select a.[Customer] ,a.[CustID] ,'% VA' ,b.[AccYear] ,(sum(a.[Net]-a.[mcosts])/sum(a.[Net]))*100 FROM [pharma].[dbo].[vw_mon_inv_sales] a cross apply (select [AccYear], [AccYearDesc] from [pharma].[dbo].[tblAccountYears_v2] b where a.InvMon between b.[AccYearSDate] and b.[AccYearEDate]) b where a.[InvMon]>='2017-07-01' and a.Comp=1 and len(a.FPO)=5 group by a.[CustID],a.[Customer],b.[AccYear] union all select a.[Customer] ,a.[CustID] ,'% GM' ,b.[AccYear] ,(sum(a.[Net]-a.[mcosts]-a.[Hours]*@labcost)/sum(a.[Net]))*100 FROM [pharma].[dbo].[vw_mon_inv_sales] a cross apply (select [AccYear], [AccYearDesc] from [pharma].[dbo].[tblAccountYears_v2] b where a.InvMon between b.[AccYearSDate] and b.[AccYearEDate]) b where a.[InvMon]>='2017-07-01' and a.Comp=1 and len(a.FPO)=5 group by a.[CustID],a.[Customer],b.[AccYear] order by 1 ;

   This crosstab is working fine except for two things:

   

   The % VA and % GM summary values are sum of their respective columns - how do I get them as %?

   Is there a way to style the & VA and % GM independently? I'd like to show those columns with 1 decimal place, from the Net Sales - they all use the alias [val].

   Is a crosstab the best way to do this?

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   since you are repeating the same query and putting different things in the same column (net sum, VA% and GM%), this would be inefficient and inpractical.

   You could consider a more efficient approach like something like this:

   select 'dbr.crosstab','yr';
   
   select 'dbr.lockcolumns','cust';
   
   select 'dbr.colstyle','val','[width:65px;]%0.0f';
   
   select 'dbr.colstyle','va_percent','%0.1f';
   
   select 'dbr.colstyle','gm_percent','%0.1f';
   
   select 'dbr.hidecolumn','cid', 'va_val', 'gm_val';
   
   select 'dbr.sum','val', 'va_val', 'gm_val';
   
   select 'dbr.calc', 'va_percent', '[va_val]/[val]*100';
   
   select 'dbr.calc', 'gm_percent', '[gm_val]/[val]*100';
   
   select
   
      a.[Customer] as "Customer[cust]"
   
     ,a.[CustID] as "CustID[cid]"
   
     ,b.[AccYear] as "Financial Year Metrics[yr]"
   
     ,sum(a.[Net]) as "'Net Sales'[val]",
   
     null as '% VA[va_percent]',
   
     null as '% GM[gm_percent]',
   
     sum(a.[Net]-a.[mcosts]) as 'va_val',
   
     sum(a.[Net]-a.[mcosts]-a.[Hours]*@labcost) as 'gm_val'
   
   FROM [pharma].[dbo].[vw_mon_inv_sales] a
   
         cross apply (select [AccYear], [AccYearDesc] from [pharma].[dbo].[tblAccountYears_v2] b where a.InvMon between b.[AccYearSDate] and b.[AccYearEDate]) b
   
   where a.[InvMon]>='2017-07-01' and a.Comp=1 and len(a.FPO)=5
   
   group by a.[CustID],a.[Customer],b.[AccYear]

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Thanks for your suggestion.

   The problem is that would like to have the 3 metrics, Net Sales, % VA, % GM as the main crosstab group and then the Years - see my layout above ie Net Sales with the 5 years, then % VA with the 5 years etc - it's to show a trend in those 3 metrics.

   I can't think how to do this. Like I say I have it working 95% except for the totals of the % and not being able to have differing decimal places.

   Maybe not possible? 3 separate crosstabs?

   Jake
4. 

   myDBR Team, Key Master

   If you want to keep the Net Sales, % VA, % GM as separate items, the easiest way to do it would be to show them as separate tables. For now even if you seem to be close, you are mixing different data in same columns and you do not have enough information to calculate the totals of the %.

   --
   myDBR Team

Reply

You must log in to post.