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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.