web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Attendance Customize R...
Microsoft Dynamics RMS (Archived)

Attendance Customize Report in RMS HQ

(0) ShareShare
ReportReport
Posted on by Umar Khitab 750

Dear All,

I want to customize the report for RMS HQ. I did all the exercise but i have one problem. When i am take the difference between two time its ok. but when i am make the total for this time it give me the zero. I made this report in .qrp format. I am pasting my code here. please help me to get the accurate hours calculations.

The Code is as below,

//--- Report Summary --- //

Begin ReportSummary
   ReportType = reporttypeMisc
   ReportTitle = "Daily Attendance"
   PageOrientation = pageorientationportrait
   OutLineMode = True
   Groups = 2
   GroupDescription = ""
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   TablesQueried = <BEGIN>
  
      FROM   TimeClock WITH(NOLOCK) Inner JOIN   Cashier WITH(NOLOCK)
   ON TimeClock.CashierID = Cashier.ID AND Timeclock.StoreID = Cashier.StoreID
   LEFT JOIN   Store ON TimeClock.StoreID = Store.ID
<END>

   SelCriteria = ""
   GroupBy = ""
   SortOrder = ""
End ReportSummary


//--- Title Rows ---//

Begin TitleRow
   Text = "<Store Name>"
   Font = "Arial"
   FontBold = True
   FontSize = 16
   Color = "Blue"
End TitleRow

Begin TitleRow
   Text = "<Report Title>"
   Font = "Arial"
   FontBold = True
   FontSize = 12
   Color = "Black"
End TitleRow

Begin TitleRow
   Text = "Generated On <Report Date>"
   Font = "Arial"
   FontBold = True
   FontSize = 10
   Color = "Black"
End TitleRow


//--- Filters ---//

Begin Filter
   FieldName = "TimeClock.TimeIn"
   FilterOp = reportfilteropGreaterEqual
   FilterLoLim = "<LastWeekStart>"
   FilterHilim = ""
End Filter


//--- Columns ---//

Begin Column
   FieldName = "Store.Name"
   DrillDownFieldName = "Store.Name"
   DrillDownReportName = ""
   Title = "Store"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2085
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.ID"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store ID"
   VBDataType = vbLong
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 870
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.StoreCode"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store Code"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1095
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.Region"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store Region"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1335
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.City"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store City"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1020
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.State"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store State"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1155
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Cashier.Number"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Employee Number"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1600
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Cashier.Name"
   DrillDownFieldName = "Cashier.Name"
   DrillDownReportName = ""
   Title = "Employee Name"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1900
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column


Begin Column
   FieldName = "TimeClock.TimeIn"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Date"
   VBDataType = vbDate
   Formula = "convert(varchar, timeclock.timein)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1875
   GroupMethod = groupmethodsum
   ColFormat = "dd-mm-yyyy"
   ColAlignment = flexAlignRightCenter
End Column

Begin Column
   FieldName = "TimeClock.TimeOut"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Time Out"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1875
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignRightCenter
End Column

Begin Column
   FieldName = "LogIn"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Log In"
   VBDataType = vbString
   Formula = "CONVERT(nvarchar,Timeclock.TimeIn,20)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 1875
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignRightCenter
End Column

Begin Column
   FieldName = "LogOut"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Log Out"
   VBDataType = vbString
   Formula = "CASE WHEN (Timeclock.TimeOut >= Timeclock.TimeIn) THEN CONVERT(nvarchar, Timeclock.TimeOut, 20) ELSE 'Not Punch Out' END"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = True
   ColWidth = 1875
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignRightCenter
End Column

Begin Column
   FieldName = "Hours"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Hours"
   VBDataType = vbDouble
   Formula = "convert(nvarchar, timeclock.timeout - timeclock.timein)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1410
   GroupMethod = groupmethodSum
   ColFormat = "h:m:ss"
End Column

Please help me to correct it. my email id is (umar@imtiaz.com.sa)

Thanks

Umar Khitab

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    You need to change your formula for the last column to check for null entries.

    Try the following:

    CASE WHEN (Timeclock.TimeOut >= Timeclock.TimeIn) THEN Convert(nvarchar, timeclock.timeout - timeclock.timein) ELSE 0 END

    Also your VBDataType should be vbString because your of your ColFormat.   If you just want a decimal then remove the ColFormat and leave the VBDataType = Double

    Ed Boyer

    bmepos.com

  • Umar Khitab Profile Picture
    Umar Khitab 750 on at

    Dear Ed Boyer,

    Thank you very much for your reply. I tried your suggestion but after that i am getting the following error,

    " The Report Data could not be loaded due to the following error:

    (-2147217913) Conversion Failed when converting the nvarchar value ' Jan 1 1900 2:01AM' to data type int."

    Please help me regarding this.

    thanks in advance,

    Umar Khitab

  • Verified answer
    Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at

    Hi Umar,

    Active Reports' groupmethods are notoriously unreliable, and math operations with datetimes in SQL can behave in unexpected ways. I think your best bet is going to be to display your employees' time in hours rather than the hh:mm:ss format you currently have by employing the DATEDIFF() SQL function.

    Just replace your last column with

     

    Begin Column
       FieldName = "Hours"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Hours"
       VBDataType = vbDouble
       Formula = "CAST(DATEDIFF(mi, timeclock.timein, COALESCE(timeclock.timeout,timeclock.timein)) as decimal)/60"
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1410
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

     

    and the totals should start summing correctly.

  • Umar Khitab Profile Picture
    Umar Khitab 750 on at

    Dear Spencer M Thank you very much for your help.

    This column is working fine know.

    I want to add one more column in my report that will show me only date.

    Currently in Timein column it is showing me date&time both in one column. Because of that i am not be able to group my data by date.

    Can you please help me to make an new Date column through which i can group the data by Date.

    thanks

    Umar Khitab

  • Verified answer
    Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at

    No problem. The issue is that even when the report formats the datetime to only show the date, the value itself still includes the time, and thus the column won't group properly.

    To fix this issue, we just have to alter the value that the query itself returns using the convert function. I usually use style 101, as I grew up with the illogical American date format and can't seem to shake it, but 105 should give you the dd-mm-yyyy format it looks like you were trying for above. If you decide you'd like it otherwise, a list of all the formats is available at msdn.microsoft.com/.../ms187928.aspx.

    So, the finished column will look something like:

    Begin Column
       FieldName = "TimeClock.TimeIn"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Date"
       VBDataType = vbDate
       Formula = "Convert( varchar (10), TimeClock.TimeIn, 105)"
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1875
       GroupMethod = groupmethodsum
       ColFormat = ""
       ColAlignment = flexAlignRightCenter
    End Column

  • Umar Khitab Profile Picture
    Umar Khitab 750 on at

    Dear Spencer,

    Thank you very much for you input. It realy make my life very easy...

    thanks Again

    Umar

  • Umar Khitab Profile Picture
    Umar Khitab 750 on at

    Dear Spencer,

    I want to ask one question that if a cashier is forget to punch out at night, the next morning system is giving him again Ponch Out option.

    How we set the system that if he a cashier forget ponch out then next day system will give him automaticaly Ponch In option.

    Thanks

    Umar

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,087 on at

    Hi Umar,

    I think the only way to do this would be to create a job to log out anyone that is left logged in and schedule it to go off at some set time every night (probably midnight). If you've upgraded to the full version of SQL from the free version of SQL express that comes with RMS, you can do this using SQL Server Agent. Otherwise, you'll need to write a script utilizing some way of communicating with the database (in the past I've used osql with batch files, which is apparently out of date; lately, I've used a module for python called pyodbc) and use windows task scheduler to schedule it.

    In other words, it's going to be a good bit more involved than just writing a column. I could likely help you out. To give a full disclaimer, I am self-taught and my knowledge is pretty ad-hoc. In general, the way I do things works, but its not according to standards and there may be better ways of accomplishing the same thing. I'd check with your Microsoft Partner first. If that doesn't work,  feel free to shoot me an email using the address on my profile, and we can work something out.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans