Hi ,
I need to make this work in SSRS.
It works perfectly in SSMS but not in SSRS
Find below the script:
declare @total money, @exclusion money, @revised_result money, @MBE_TOTAL as money, @afA MONEY, @H MONEY, @AI MONEY, @AP MONEY
, @WBE MONEY
create table #temp1 (f1 nvarchar(255), f2 nvarchar(255), f3 nvarchar(255), f4 nvarchar(255))
insert into #temp1(f1, f2, f3,f4) values(‘Phase Breakdown’, ‘Total Payments’, ‘Percentage MBE Payments’, ‘Participation Goal’)
select @total = c.phase_total
, @exclusion = c.phase_less
,@revised_result = (c.phase_total - c.phase_less)
,@mbe_total = d.total
, @afa = AFA
, @h = H
, @ai = AI
, @ap = AP
, @WBE = WBE
from goma_check_register_analysis c inner join GOMA_D6_payment_analysis d
on c.cal_type = d.cal_type
and c.procurement_phase = d.procurement_phase
and c.report_month = d.report_month
and c.report_year = d.report_year
where c.cal_type = ‘C’ and d.procurement_phase = ‘A&E’
and c.report_year * 100 + c.report_month = 201602
order by c.report_year, c.report_month,c.procurement_phase
INSERT #TEMP1 VALUES(‘Architectural & Engineering’, ‘’, ‘’, ‘’)
INSERT #TEMP1 VALUES(‘Total Check Register Payments’, cast(@total as nvarchar(255)), ‘’, ‘’)
INSERT #TEMP1 VALUES(‘Less Exclusions’, cast(@exclusion as nvarchar(255)), ‘’, ‘’)
INSERT #TEMP1 VALUES(‘Revised Check Register Payments’, cast(@revised_result as nvarchar(255)), ‘’, ‘’)
INSERT #TEMP1 VALUES(‘MBE Payments To Date’, cast(@mbe_total as nvarchar(255)), cast(round(100*@mbe_total/@revised_result, 2) as nvarchar)+ ‘%’, ‘19.00%’)
INSERT #TEMP1 VALUES(‘Subgroup - African American’, cast(@afa as nvarchar(255)), cast(round(100*@afa/@revised_result, 2) as nvarchar)+ ‘%’, ‘6.00%’)
INSERT #TEMP1 VALUES(‘Subgroup - Hispanic American’, cast(@h as nvarchar(255)), cast(round(100*@h/@revised_result, 2) as nvarchar)+ ‘%’, ‘2.00%’)
INSERT #TEMP1 VALUES(‘Subgroup - Woman*’, cast(@wbe as nvarchar(255)), cast(round(100*@wbe/@revised_result, 2) as nvarchar)+ ‘%’, ‘9.00%’)
INSERT #TEMP1 VALUES(’’, ‘’, ‘’, ‘’)
select @total = c.phase_total
, @exclusion = c.phase_less
,@revised_result = (c.phase_total - c.phase_less)
,@mbe_total = d.total
, @afa = AFA
, @h = H
, @ai = AI
, @ap = AP
, @WBE = WBE
from goma_check_register_analysis c inner join GOMA_D6_payment_analysis d
on c.cal_type = d.cal_type
and c.procurement_phase = d.procurement_phase
and c.report_month = d.report_month
and c.report_year = d.report_year
where c.cal_type = ‘C’ and d.procurement_phase = ‘C&D’
and c.report_year * 100 + c.report_month = 201602
order by c.report_year, c.report_month,c.procurement_phase
INSERT #TEMP1 VALUES(‘Construction & Development’, ‘’, ‘’, ‘’)
INSERT #TEMP1 VALUES(‘Total Check Register Payments’, cast(@total as nvarchar(255)), ‘’, ‘’)
INSERT #TEMP1 VALUES(‘Less Exclusions’, cast(@exclusion as nvarchar(255)), ‘’, ‘’)
INSERT #TEMP1 VALUES(‘Revised Check Register Payments’, cast(@revised_result as nvarchar(255)), ‘’, ‘’)
INSERT #TEMP1 VALUES(‘MBE Payments To Date’, cast(@mbe_total as nvarchar(255)), cast(round(100*@mbe_total/@revised_result, 2) as nvarchar)+ ‘%’, ‘26.00%’)
INSERT #TEMP1 VALUES(‘Subgroup - African American’, cast(@afa as nvarchar(255)), cast(round(100*@afa/@revised_result, 2) as nvarchar)+ ‘%’, ‘7.00%’)
INSERT #TEMP1 VALUES(‘Subgroup - Asian American’, cast((@AP + @AI) as nvarchar(255)), cast(round(100*(@AI+@AP)/@revised_result, 2) as nvarchar)+ ‘%’, ‘4.00%’)
select * from #temp1
DROP TABLE #temp1
Also the result set from SSMS:
I am a newbie.
Thank you