SSRS Sum value #Error

I don't have SSRS set up right now to fully test this, but I believe this syntax will work to handle the 0 problem.

=IIF(Sum(Fields!PreYr.Value)=0,0,(Sum(Fields!CurYr.Value)-Sum(Fields!PreYr.Value)) / Sum(Fields!PreYr.Value))

Hi Chris - Thank you for the fast reply - I did try that (reverted back to my original expression since nothing is working) but, no dice. Still the same results!

if you can see what the exact error is, it may give a clue.  is it a float and wants = 0.0, is it really evaluating to NULL and you need the check to look for ISNULL instead of an equality?  Like I said, without an environment and the data to see what is really happening, it is hard to pinpoint the issue.  Try these other ideas and see if you can identify the exact error in the logs somewhere.
Good luck,
Chris

Hi Chris - Not exactly sure how to answer your question intelligibly but, here goes my best shot.

I wrote a small SP (code below) that is 1 dataset within the total report structure of the report. The customerID and endDate are standard parameters for every dataset. For this one table in particular, the data type is decimal. This works perfectly within SSRS, until we put an agg sum in an expression for the group row. The Grand Totals aren't affected since there will never a zero or null value.


Not exactly sure how to answer your question but, here goes my best shot.

I wrote a small SP (code below) that is 1 dataset within the total report structure. The customerID and endDate are standard parameters for every dataset. For this one table, in particular, the data type is decimal. This works perfectly within SSRS, until we put an agg sum in an expression for the group row. The Grand Totals aren't affected since there will never a zero or null value.  I've noticed that where value is 0 for CurYr and PreYr has a value, the error does not occur, and the sum and division work properly. It's only when PreYr is 0 that the #Error comes up - again, turning to the Experts since this is eluding me and need some fresh eyes.


ALTER PROCEDURE [dbo].[xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx]
@xCustomerID Int,
@xEndDate Datetime
AS
BEGIN


Create table #S
(VendorID nVarchar(50),
 Vendor nVarchar(50),
 Name nVarchar(50),
 PreYr Decimal(18,6),
 CurYr Decimal(18,6))

Select x.* into #T from
(Select L.supplier_id as VendorID, S.supplier_name as Vendor, addr.name, YEAR(H.Invoice_date) as Yr, sum(L.extended_price) as Total,
365 - DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @xEndDate), '19000101'),DATEADD(d, -0, DATEDIFF(d, 0, @xEndDate))) as Days_Remaining

            from SummitCenter.dbo.invoice_line L inner join
            SummitCenter.dbo.invoice_hdr H on H.invoice_no = L.invoice_no
            inner join
            SummitCenter..p21_view_address addr on  h.ship_to_id = addr.id
            inner join
            SummitCenter.dbo.supplier S on L.supplier_id = S.supplier_id
where
      Year(H.invoice_Date)= Year(@xEndDate) and
      DATEADD(dd, 0, DATEDIFF(dd, 0, H.Invoice_Date)) <= @xEndDate and
      H.customer_id = @xCustomerID and
      S.supplier_id not in ('1015198','1017489','1015635','1017089','1014174','1000345')
group by  addr.name, L.supplier_id, S.supplier_name,  YEAR(H.Invoice_date)
having sum(L.extended_price)<>0

union all

Select L.supplier_id as VendorID, S.supplier_name as Vendor, addr.name, YEAR(H.Invoice_date) as Yr, sum(L.extended_price) as Total,
365 - DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @xEndDate), '19000101'),DATEADD(d, -0, DATEDIFF(d, 0, @xEndDate))) as Days_Remaining
                  from SummitCenter.dbo.invoice_line L inner join
            SummitCenter.dbo.invoice_hdr H on H.invoice_no = L.invoice_no
            inner join
            SummitCenter..p21_view_address addr on  h.ship_to_id = addr.id
            inner join
            SummitCenter.dbo.supplier S on L.supplier_id = S.supplier_id
where
      Year(H.invoice_Date)= Year(@xEndDate)-1 and
      DATEADD(dd, 0, DATEDIFF(dd, 0, H.Invoice_Date)) <= (DATEADD(year, -1, @xEndDate)) and
      H.customer_id = @xCustomerID and
      S.supplier_id not in ('1015198','1017489','1015635','1017089','1014174','1000345')
group by addr.name, L.supplier_id, S.supplier_name,  YEAR(H.Invoice_date)
having sum(L.extended_price)<>0) X


Insert into #S
Select Distinct VendorID,Vendor, Name, 0,0 from #T

update #S set PreYr=X.Total from
(Select sum(Total) as Total, Name, VendorID from #T where Yr=Year(@xEndDate)-1
group by VendorID, Name) X
where
X.VendorID=#S.VendorID
and
X.name=#S.name


update #S set CurYr=X.Total from
(Select sum(Total) as Total,  Name, VendorID from #T where Yr=Year(@xEndDate)
group by VendorID, Name) X
where
X.VendorID=#S.VendorID
and
X.name=#S.name

Select *, (CurYr-PreYr) as Diff, case when PreYr<>0 then cast((CurYr-PreYr)/PreYr AS Decimal(18,6)) else 1 END AS YOY from #S

drop table #T
drop table #S
END

Thank you, James! I will give this a go and update you. Many thanks for your answer.

Hi James - just one small adjustment was needed - it works like a champ. Thank you!


Final Expression:

=IIF(Sum(Fields!PreYr.Value)=0,1,(Sum(Fields!CurYr.Value)-Sum(Fields!PreYr.Value)) / IIF(Sum(Fields!PreYr.Value)=0,1,Sum(Fields!PreYr.Value)))

Yes! Only one minor adjustment needed and now all percentages are calculating properly. Thanks, James!

You're welcome.  Glad I could help.

 James