SSRS Sum value #Error

Hi again, Experts - another SSRS challenge on the final leg of designing a report as I'm receiving a #Error summing totals.

I am trying to sum totals for previous and current year using the following expression:

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

Of course, this works for 'grand totals' since there is never going to be any '0' values for previous or current year aggregate. However, for the individual manufacturer rows, there will be. Can you guys help? If you need any additional info, please let me know.

Thank you!

SSRS Report Design Screenshot
SSRS Report Preview Screenshot
SSRS Report Totals Screenshot
Jeremy PoissonPresidentAsked:
Who is Participating?
 
James0628Commented:
I think that the IIF is still giving you an error because SSRS always evaluates all parts of the IIF, so the false part is still evaluated, even when the figure is 0.  One way to handle that is to use another IIF in that part of the first IIF, to replace the 0 divisor with something else.

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

Open in new window


 James
2
 
Chris LuttrellSenior Database ArchitectCommented:
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))
0
 
Jeremy PoissonPresidentAuthor Commented:
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!


SSRS Report Design with new ExpressionSSRS Report Preview with new Expression
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Chris LuttrellSenior Database ArchitectCommented:
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
0
 
Jeremy PoissonPresidentAuthor Commented:
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
0
 
Jeremy PoissonPresidentAuthor Commented:
Thank you, James! I will give this a go and update you. Many thanks for your answer.
0
 
Jeremy PoissonPresidentAuthor Commented:
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)))
0
 
Jeremy PoissonPresidentAuthor Commented:
Yes! Only one minor adjustment needed and now all percentages are calculating properly. Thanks, James!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.