1

My SSRS report creates a summarized table. I need to do a calculation based on 2 pieces of data from the table and add it as an expression at the bottom of the report. My expression errors out as it is not reading the data output from the table.

I have followed up on all examples I could find with the error I get, but found no resolution. I am new to SSRS. I have tested the expression by simply using the IIf statement to return the values I'm looking for (without the rest of the calculations I need) and it doesn't return either value. I get "#Error" as the result. I have copied the lookup values directly from the SQL code, so I KNOW there are no typos in my comparison values.

I have this for the code in my expression:

=Code.Divide
(IIf(Fields!Results.Value = "BKR3 - Total Overtime Hours", Fields!Results.Value, 0)) , 
(IIf(Fields!Emp_Type.Value = "BKR1 - Total Paid Hours", Fields!Results.Value, 1))

Through another stackoverflow question I found this code and have added it to my report: 

Public Function Divide(ByVal dividend as Double, ByVal divisor as Double) As Double
    If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend=0 THEN
        Return 0
    Else
        Return dividend/divisor
    End If
End Function

I am getting this error:

The Value expression for the textrun ‘Textbox3.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'divisor' of 'Public Function Divide(dividend As Double, divisor As Double) As Double'.

This is what my output looks like:

*Emp_Type                              Results
*A3-Facility Payroll Hours            28,252.20
*A4-Provider Payroll Hours             1,998.50
*BKR1-Total Paid Hours                30,250.70
*BKR2-Total Worked Hours              27,037.62
*BKR3-Total Overtime Hours               504.20
*BS1-Hospital FTEs                        99.72
*BS2-Clinic FTEs                          23.25

Overtime % #Error

(I'm very sorry, I cannot get the list to indent!)

I am expecting this as my results:

      Overtime %                               0.18%

This is what I am getting instead:

      Overtime %                               #Error
AnkUser
  • 5,421
  • 2
  • 9
  • 25
user2611375
  • 37
  • 7
  • GAH! I see a glaring error in my expression. It should read this instead: – user2611375 Apr 23 '19 at 18:17
  • ``` =Code.Divide(IIf(Fields!Emp_Type.Value = "BKR3 - Total Overtime Hours", Fields!Results.Value, 0), IIf(Fields!Emp_Type.Value = "BKR1 - Total Paid Hours", Fields!Results.Value, 1)) I am now getting 0.00% as the result - still incorrect result! – user2611375 Apr 23 '19 at 18:20
  • can you show us your data coming from dataset, I mean without any IIF condition it would be helpful to find an issue – AnkUser Apr 24 '19 at 06:25
  • In my explanation above, I showed the data where I said "This is what my output looks like:". It has these fields: Emp_Type, Results. I put an * at the beginning of each row of data. I'm sorry I can't figure out how to format it correctly. I tried using spaces, but it just clumped it all together when I posted it. – user2611375 Apr 24 '19 at 13:45
  • alright, so how are you calculating overtime %. I mean what should be the Formula to calculate it? – AnkUser Apr 24 '19 at 14:18
  • Dividend: if the Emp_Type = "BKR3 - Total Overtime Hours", give the value for "Results" for that row and Divisor: (if the Emp_Type = "BKR1 - Total Paid Hours", give the value for that row) I have the code I tried using in the code box above. This is just a layman's version of what I want to do. – user2611375 Apr 24 '19 at 18:07
  • Ok but for a particular row you have either bkr3 or bkr1 or bkr2 or so on. I mean you do not have from your dataset on a single row both values. Do you wish to achieve overtime % per row or for a complete dataset? – AnkUser Apr 24 '19 at 18:57
  • Each row has a unique Emp_Type. I ONLY need to divide overtime hours by Total Paid Hours. None of the other rows matter. – user2611375 Apr 25 '19 at 13:52

0 Answers0