Works well in SSMS, but not in SSRS

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:

Capture.PNG

I am a newbie.

Thank you

is there anyother dataset in that SSRS , first all the datasets are executed in SSRS regardless what displays on the report. Restart the SQl server reporting servvices

Hi kennyzita, is your question related to Microsoft Dynamics AX? If not, please ask in an SSSR forum.
Also, you will have to describe you problem in better detail than just “doesn’t work in SSRS”.