sqlreporting-servicesssrs-2012reportbuilder3.0

SSRS count working days only

I need some help in this my case is

1-two parameters date from , date to

2-number of team parameter that manually enter by user for later on use in some calculation

rquirement

count only working days (6days per week ) without Friday based on filtered period (date from and date to)

Code

=(COUNT(IIF(Fields!Job_Status.Value="Closed",1,Nothing))) / 
((DateDiff(DateInterval.day,Parameters!DateFrom.Value,Parameters!ToDate.Value
)) * (Parameters!Number_of_teams.Value))

Note

this code is working fine but it calculate all days

thanks in advance

Solution

  • Try this:

    =(DATEDIFF(DateInterval.Day, CDATE("2016-02-14"), CDATE("2016-02-17")) + 1)
-(DATEDIFF(DateInterval.WeekOfYear, CDATE("2016-02-14"), CDATE("2016-02-17")) * 2)
-(IIF(WeekdayName(DatePart(DateInterval.Weekday,CDATE("2016-02-14"),FirstDayOfWeek.System))="sunday",1,0)
-(IIF(WeekdayName(DatePart(DateInterval.Weekday,CDATE("2016-02-17"),FirstDayOfWeek.System))="saturday",1,0)
))

    It will ruturn count of monday to friday between the given range in the above case it returns 3. For StartDate = 2016-02-14 and EndDate = 2016-02-21 it returns 5.

    UPDATE: Expression to exclude friday from the count.

    =(DATEDIFF(DateInterval.Day, Parameters!DateFrom.Value, Parameters!ToDate.Value) + 1)
-(DATEDIFF(DateInterval.WeekOfYear, Parameters!DateFrom.Value, Parameters!ToDate.Value) * 1)
-(IIF(WeekdayName(DatePart(DateInterval.Weekday,Parameters!ToDate.Value,FirstDayOfWeek.System))="friday",1,0))

    Tested with:

     DateFrom     ToDate     Result
2016-02-12  2016-02-19     6
2016-02-12  2016-02-18     6
2016-02-12  2016-02-15     3

    It is very strange to me see a saturday and sunday as working days instead of friday.

    Let me know if this helps you.