i have several calculated fields that are not showing summary lines.
http://xxx.xxx.com/mydbr/report.php?r=39&m=10&h=d971eb69db0f45fc7924c14da70718e0bfcd3aae
here is my report code:
select 'dbr.sum', 2, 3, 4, 5, 7, 9, 10, 11, 12, 14, 19;
select 'dbr.summary.calc', 6, 'round((c5/c4)*100,2)';
select 'dbr.summary.calc', 8, 'round((c7/c5)*100,2)';
select 'dbr.summary.calc', 13, 'round((c3/c11)*100,2)';
select 'dbr.summary.calc', 15, 'round((c22/c23),2)';
select 'dbr.summary.calc', 16, 'round((c14/c23)*100,2)';
select 'dbr.summary.calc', 17, 'round((c20/c23),2)';
select 'dbr.summary.calc', 18, 'round((c20/c21)*100,2)';
select 'dbr.hidecolumns', 4;
select 'dbr.colstyle', 6, '%.2f%%';
select 'dbr.colstyle', 8, '%.2f%%';
select 'dbr.colstyle', 13, '%.2f%%';
select 'dbr.colstyle', 16, '%.2f%%';
select 'dbr.colstyle', 18, '%.2f%%';
SELECT fa.user_id as 'Sales Person'
, sum(queue_60_sec) as 'Inbound Queue'
, sum(web_of_120 + inbound_120_sec + outbound_120_sec) as 'Web Overflow Calls'
, sum(inbound_120_sec + outbound_120_sec + queue_60_sec + web_of_120) as 'Total Calls'
, sum(deals) as 'Total Deals'
, (sum(deals) / sum(web_of_120 + inbound_120_sec + outbound_120_sec + queue_60_sec)) * 100 as 'Close Ratio - Target >= 5%'
, sum(backouts) as 'Total Backouts'
, (sum(backouts)/sum(deals)) * 100 as 'Backout %'
, sum(overflow) as 'Overflow'
, sum(web) as 'Web'
, sum(overflow + web) as 'Overflow/Web Total'
, sum(web_of_120 + inbound_120_sec + outbound_120_sec) as 'Contacts'
, (sum(web_of_120 + inbound_120_sec + outbound_120_sec)/sum(overflow + web))*100 as 'Web Overflow Contact Ratio Target >= 60%'
, sum(over_18_mo) as 'Deals > 18'
, sum(b.sumft)/sum(b.sumdeal) as 'Avg Fin Term'
, (sum(over_18_mo)/sum(b.sumdeal))*100 as 'Deals > 18 months - Target <= 35%'
, sum(b.sumdp)/sum(b.sumdeal) as 'Avg Down'
, (sum(b.sumdp)/sum(b.sumcc))*100 as 'Avg Down % - Target >= 8%'
, 1 as 'GP Bonus'
, sum(b.sumdp)
, sum(b.sumcc)
, sum(b.sumft)
, sum(b.sumdeal)
from dw_auto.fact_activity fa
left outer join
(select fc.user_id
, sum(fc.down_pmnt) as 'sumdp'
, sum(fc.cust_cost) as 'sumcc'
, sum(fc.fin_term) as 'sumft'
, count(*) as 'sumdeal'
, fc.activity_purch_dt as 'pdate'
from dw_auto.fact_activity fa
inner join dw_auto.fact_client fc on fc.activity_purch_dt = fa.activity_dt
and fc.user_id = fa.user_id
where fin_flag = 'fin' group by fc.activity_purch_dt, fc.user_id) as b
on b.user_id = fa.user_id and b.pdate = fa.activity_dt
where activity_dt between vStartDate and vEndDate
group by fa.user_id
having (sum(inbound_120_sec + outbound_120_sec + queue_60_sec + web_of_120) > 0 or sum(deals) > 0)
order by 5 desc;
as you can see im using the last 4 columns
, sum(b.sumdp)
, sum(b.sumcc)
, sum(b.sumft)
, sum(b.sumdeal)
to calculate the values the prior columns
, sum(over_18_mo) as 'Deals > 18'
, sum(b.sumft)/sum(b.sumdeal) as 'Avg Fin Term'
, (sum(over_18_mo)/sum(b.sumdeal))*100 as 'Deals > 18 months - Target <= 35%'
, sum(b.sumdp)/sum(b.sumdeal) as 'Avg Down'
, (sum(b.sumdp)/sum(b.sumcc))*100 as 'Avg Down % - Target >= 8%'
the prior columns are missing the summary lines.
do i have to have my last 4 columns to the left of the calculated columns for a summary line to show up? or am i doing something else wrong?
