Datepart for Report Builder

[dashmarley11]

In MS Report Builder, I'm trying to create a new Calculated Field (StartActAccrual) that renders the exact same results as this SQL Declare statement:

DECLARE @StartActAccrual FLOAT = 1.0 * Day(EOMONTH(@StartTapeDate,0)) / (SELECT CASE WHEN DAY(EOMONTH(DATEFROMPARTS(YEAR(@StartTapeDate),2,1))) = 29 THEN 366 ELSE 365 END)

[burakkaragoz · Accepted solution]

Hi @dashmarley11 ,

For MS Report Builder, you can mimic that SQL logic in a calculated field, but the syntax will be a bit different since Report Builder uses Expressions (which are more like VB.NET). Here’s one way you could do it:

If your goal is to calculate the number of days in the month of @StartTapeDate divided by either 366 or 365 (based on if the year is a leap year), try something like this in your calculated field expression:

vb

=Day(DateAdd("d", -1, DateAdd("m", 1, Fields!StartTapeDate.Value))) / 
    (IIf(Day(DateSerial(Year(Fields!StartTapeDate.Value), 2, 29)) = 29, 366, 365))

• The first part: Day(DateAdd("d", -1, DateAdd("m", 1, ...))) gets the last day of the month (like EOMONTH in SQL).
• The IIf checks if Feb 29 exists for the year (so, leap year logic), then picks 366 or 365.

Just make sure to replace Fields!StartTapeDate.Value with your actual field name if it’s different.

Let me know if you need help fitting this into your report or if you get any errors!

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

Translation & text editing supported by AI