Calculation in SSRS don't match SQL Server query

Question

I have a SQL query that works perfectly but does not match the numbers when i put the query in SSRS. SQL View How can I get the numbers to show up right in my SSRS report to reflect the same numbers in my SQL Query View?

This is the view in my query:
The view in SSRS

As you can see the numbers dont match.

My origional data set query is.

Select  
'11-30' as strAgeing,  
SUM(mnyRent0to30) 'A/R Rent',  
Sum(mnyFees0to30) 'A/R Fees',  
SUM(mnyRent0to30) + Sum(mnyFees0to30)  'Total A/R',  
Sum(mnyRent0to30) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',  
1 as intOrder  
FROM tblUBMReport  
LEFT JOIN tblUBMInsuranceShare  
on tblUBMInsuranceShare.intProp = tblUBMReport.intProp  
  
Where tblUBMReport.intProp IN (@Props)   
 and dtReport = @ReportDate   
  
UNION  
  
SELECT  
  
'31-60' as strAgeing,  
SUM(mnyRent31to60) 'A/R Rent',  
Sum(mnyFees31to60) 'A/R Fees',  
Sum(mnyRent31to60) + Sum(mnyFees31to60)  'Total A/R',  
Sum(mnyRent31to60) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',  
2 as intOrder  
FROM tblUBMReport  
LEFT JOIN tblUBMInsuranceShare  
on tblUBMInsuranceShare.intProp = tblUBMReport.intProp  
  
Where tblUBMReport.intProp IN (@Props)   
 and dtReport = @ReportDate   
  
UNION  
  
  SELECT  
'61-90' as strAgeing,  
SUM(mnyRent61to90) 'A/R Rent',  
Sum(mnyFees61to90) 'A/R Fees',  
Sum(mnyRent61to90) + Sum(mnyFees61to90) 'Total A/R',  
Sum(mnyRent61to90) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',  
3 as intOrder  
FROM tblUBMReport  
LEFT JOIN tblUBMInsuranceShare  
on tblUBMInsuranceShare.intProp = tblUBMReport.intProp  
  
Where tblUBMReport.intProp IN (@Props)   
 and dtReport = @ReportDate  
  
UNION  
  
  SELECT  
'Over 90' as strAgeing,  
SUM(mnyRent90Plus) 'A/R Rent',  
Sum(mnyFees90Plus) 'A/R Fees',  
Sum (mnyRent90Plus) + Sum(mnyFees90Plus) 'Total A/R',  
Sum(mnyRent90Plus) / CAST(Sum(mnyOccupiedRent) as float) * 100 as 'Percent A/R',  
4 as intOrder  
FROM tblUBMReport  
LEFT JOIN tblUBMInsuranceShare  
on tblUBMInsuranceShare.intProp = tblUBMReport.intProp  
  
Where tblUBMReport.intProp IN (@Props)   
 and dtReport = @ReportDate  
ORDER BY intOrder  


  
  
  

Answer 1

Remove *100 from your view parts ... * 100 as 'Percent A/R'.

Answer 2

As far as I can tell the numbers are the same, save for the percentage column. Now, before I go on, I need to give a warning: I don't know anything about SSRS.

However, the behaviour appears reminiscent for Excel. You multiply the values with 100 in your query to get percentages. But when you specify the column as per cent in SSRS, SSRS assumes that you are sending decimal fractions. Thus, you should remove the multiplication with 100 in your SQL query.

A completely unrelated tip: Most of the time you use UNION, you actually want UNION ALL. UNION will strip out duplicate rows which sometimes can bite you. So always use UNION ALL, unless you know that you want distinct values.

Answer 3

Hi @Jannette Jones ,
You could set number and data formatting in the Text Box Properties of the report, as shown below:

Preview：

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

How would I get % sign in there as well