Trying to create a 50%/50% payment schedule in a report. The issue is when the number doesn’t split clean in half. For example, if I have $1800.01, I want to show first half $900.00 and second half $900.01. SSRS seems to want to lose the extra penny and give me $900.00 twice.
Formulas for my two halves:
=Floor((Fields!Subtotal.Value / 2.00) * 100.00) / 100.00
=Ceiling((Fields!Subtotal.Value / 2.00) * 100.00) / 100.00
Google doesn’t seem to be helping. Any ideas?
First half to the nearest cent
=Floor(Fields!Subtotal.Value / 2 * 100) / 100
$900.00
Second half
=Fields!Subtotal.Value - Floor(Fields!Subtotal.Value / 2 * 100) / 100
$900.01
Including any pennies.
me right now
Try this combined formula
=IIF((Fields!Subtotal.Value - Floor(Fields!Subtotal.Value)) >= 0.01,
Floor(Fields!Subtotal.Value / 2 * 100) / 100,
Ceiling(Fields!Subtotal.Value / 2 * 100) / 100
)
Try with the decimal places…
Second Half
=Fields!Subtotal.Value - Floor(Fields!Subtotal.Value / 2.00 * 100.00) / 100.00
First Half
=Floor(Fields!Subtotal.Value / 2.00 * 100.00) / 100.00
No change with either…
Are you sure you have the decimal places set on the textbox props…
I have them set to number rather than currency because the currency symbol might change, so that is added on separately.
Let’s try it as a function…
Public Function SplitValue(ByVal value As Decimal, ByVal isFirstHalf As Boolean) As Decimal
Dim halfValue As Decimal = value / 2
Dim roundedHalf As Decimal = Math.Round(halfValue, 2)
If isFirstHalf Then
Return roundedHalf
Else
Return value - roundedHalf
End If
End Function
First half
=Code.SplitValue(Fields!Subtotal.Value, True)
Second half
=Code.SplitValue(Fields!Subtotal.Value, False)
That’s using code? Nah fuck off… I’m out!
Stand by… asking ChatGPT.
ChatGPT has spoken…
I understand that you need to perform this splitting within the SSRS report itself. Given the complexities of SSRS rounding behavior, achieving this within the report can be challenging. However, I can suggest another approach that might work for your specific case.
You can use a combination of custom code and calculated fields to split the values while avoiding rounding issues. Here’s a step-by-step guide:
vbnetCopy code
Public Function SplitValue(ByVal value As Decimal, ByVal isFirstHalf As Boolean) As Decimal
Dim splitValue As Decimal
If isFirstHalf Then
splitValue = Math.Truncate(value * 100) / 100
Else
splitValue = Math.Ceiling(value * 100) / 100
End If
Return splitValue
End Function
csharpCopy code
=Code.SplitValue(Fields!Subtotal.Value / 2, True)
Second half:
csharpCopy code
=Code.SplitValue(Fields!Subtotal.Value / 2, False)
This approach uses custom code to perform the splitting without rounding issues by explicitly truncating or ceiling the values as needed. It should ensure that you get the desired results even when the value doesn’t split evenly. Please ensure that you’ve correctly set the number formatting for these calculated fields to display two decimal places.
Tell ChatGPT it has the following error and see what it comes back with. It is an easy fix, just curious what it says:
There is an error on line 27 of custom code: [BC30290] Local variable cannot have the same name as the function containing it.
Ok it still doesn’t work.
Not sure why I didn’t think of this sooner but I think I just brute force it. If Subtotal is Odd then add a penny to one of the halves.
The error message “Local variable cannot have the same name as the function containing it” indicates that you have defined a local variable within your custom code with the same name as the custom function itself. This is a syntax error in VB.NET.
To fix this error, you should rename the local variable in your custom code so that it doesn’t have the same name as the function. Here’s the corrected code:
vbnetCopy code
Public Function SplitValue(ByVal value As Decimal, ByVal isFirstHalf As Boolean) As Decimal
Dim splitResult As Decimal
If isFirstHalf Then
splitResult = Math.Truncate(value * 100) / 100
Else
splitResult = Math.Ceiling(value * 100) / 100
End If
Return splitResult
End Function
In this corrected code, I’ve changed the local variable name from splitValue to splitResult to avoid the error. After making this change, the code should work without any issues.