February 19, 2015 at 8:01 am
I have a report that uses a matrix to display fill rates.
Data types are numeric(6,2)
I do not have any rounding at the sql level.
Numerator and denominator text boxes are formatted N1 and result is P1.
The report displays:
189.562.033.25%
exported Excel values:
189.51 62.01 33.2489050709725%
Reality:
62.01 / 189.51 = 0.327212284
---------------------
189.5 * .3325 = 63.00875 ???
--------------------
Do the formatting codes contain any rounding logic?
Should I be rounding at the sql level prior to returning the data?
The odd thing is most of the calculations are correct.
it occurs at the detail as well as a sub total level.
--------------
=switch
(
inscope("matrix1_Month")and inscope("matrix1_JobNumber")and (isnumeric(Sum(Fields!PSched.Value)>0)or isnumeric(Sum(Fields!PPend.Value)>0)) and isnumeric(Sum(Fields!daysnet.Value)>0)
,(Sum(Fields!PSched.Value)+Sum(Fields!PPend.Value)) / iif(Sum(Fields!daysnet.Value)=0,100000, Sum(Fields!daysnet.Value))
,inscope("matrix1_Month")and inscope("matrix1_wsstate")
,(Sum(Fields!PSched.Value)+Sum(Fields!PPend.Value)) / iif(Sum(Fields!daysnet.Value)=0, 100000, Sum(Fields!daysnet.Value))
,inscope("matrix1_Month")and not(inscope("matrix1_wsstate"))
,(Sum(Fields!PSched.Value)+Sum(Fields!PPend.Value)) / iif(Sum(Fields!daysnet.Value)=0, 100000, Sum(Fields!daysnet.Value))
)
-----------
any insight is greatly appreciated.
thanks
February 20, 2015 at 10:41 am
Hi,
It smells like SSRS is doing the SUM before the DIVIDE instead of doing the DIVIDE first and then the SUM.
So in the below there are product sales for 2 days:
Sales
Date Product A Product B Total
01/01/2015 100 50 150
02/01/2015 70 20 90
Total 170 70 240
If we then look at the breakdown of sales a % of total sales we get the below:
% of Total Sales
Date Product A Product BTotal
01/01/2015 66.67% 33.33% 100.00%
02/01/2015 46.67% 13.33% 100.00%
Total 71% 29% 100.00%
The problem comes in when we try and apportion a cost across the products and then roll the cost up to the Total level:
Cost
Date Product A Product B Total Cost
01/01/2015 £33.33 £16.67 50
02/01/2015 £31.11 £8.89 40
Total 90
So if you calculate the total cost first and then sum it up as per the above you get the Actual Totals below:
Product AProduct B
Actual Total £64.44 £25.56
But if you do the SUM first and then calculate the total you get the below:
Product AProduct B
Calculated Total £63.75 £26.25
It's pretty hard to explain over forum post! But I reckon this is your issue. I've attached a spreadsheet showing the issue so you can get an idea of what I'm trying to say. (Well I'm trying to upload it....)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply