Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Count on a column where some value where there are 2 column groups report

Yanıt Count on a column where some value where there are 2 column groups report

  • Saturday, February 02, 2013 7:35 PM
     
     
    Sign In to Vote
    0

    Hi i am facing a problem in my SSRS Report where there are two column groupings on the report. Ex: parent group is Location_Head,  chiled group is Location. i need to find out the Count of Employees attendence where it was marked as "WO" (Week off).

    Columns in my report are like following

    Location Head, Location, Attendace, Week Off (here i need to find out the count of employees where  there attendece was marked as WO)

    Please provied me a expression to find out the count off employees who's attendence been marked as WO.

    example data

    Location Head(Group)        Location(Group)        Total Attendance(total num of empls working in the location under the Loc_Head)         WeekOff

    Jack                                     USA                             9                                                                                                                      9 (0n_sundays)      

    Jim                                        UK                               10                                                                                                                    10   "

    i have a data parameeters too on this report...

    so on week days week offs will disply 0 and on week ends is displays total attendance

    Thanks & Regards $@m

     

All Replies

  • Sunday, February 03, 2013 9:03 AM
     
     
    Sign In to Vote
    0

    ok basically you want the total number of employees to show different values based on what day the report is ran, that if i understood you correctly.

    so an expression sample would be :

    =IIF(Weekdayname(Globals!ExecutionTime) = "Saturday", <<value for weekend>>, IIF( Weekdayname(Globals!ExecutionTime) = "Sunday", <<value for weekend>> , <<value for weekdays>>))

    hope this helps

    Teddy Bejjani - BI Specialist @ Netways

     
  • Sunday, February 03, 2013 11:03 AM
     
     
    Sign In to Vote
    0

     Hi teddy.. thanks for your reply... in my case we don't need to bother about day.. i mentioned days(sunday) just for understanding... i wanted to calculate count of people who's attendance has been marked as WO (week off ) in Attendance column and display in Week Off Column... if the day is week end, i should get total number of employees. since every one's status will be marked as WO in Attendance column. if the day is working day i need to get count of employees who's attendance marked as 'WO' in attendance. most of the time it will be 0. in some cases for few employees Week ends will be in working days.  So, in both cases expression will be same.

    in Attendance column i have status like WO(Week off), P(Present), OT(Over time).. etc.

    Thanks & Regards $@m

     
  • Monday, February 04, 2013 6:21 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    If I understanding correctly, you want to correct the count of "Week off" employees based on the "Attendance" filed in the report.

    Please refer to the following expression:
    =SUM(IIF(Fields!Attendance.Value="WO",1,0))

    Regards,

    Fanny Liu

    Fanny Liu
    TechNet Community Support