1 / 34

Welcome To...

Welcome To. ReportSmith & ADP PC/Payroll For Windows. Welcome To. Database Grouping CheckView Tables & SUM(DISTINCT ). Presented By. CharlesCook.com Specializing In ReportSmith Training & Consulting Charles@CharlesCook.com. Challenge. CheckView Tables

javan
Download Presentation

Welcome To...

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Welcome To... ReportSmith & ADP PC/Payroll For Windows

  2. Welcome To... Database Grouping CheckView Tables & SUM(DISTINCT )

  3. Presented By CharlesCook.com Specializing In ReportSmith Training & Consulting Charles@CharlesCook.com

  4. Challenge • CheckView Tables • Many Records For Each Employee Check • One Code Per Record • Can Not Add Up Most Amounts

  5. CheckView Tables • Architecture • One Record Per • Employee • Check • Code

  6. CheckView Tables • Architecture • One Record Per • Employee • CompanyCode • File# • SocialSecurity# • Check • Code

  7. CheckView Tables • Architecture • One Record Per • Employee • Check • CheckViewChkSeq# • CheckViewPayroll# • CheckViewWeek# • CheckViewYear# • Code

  8. CheckView Tables • Architecture • One Record Per • Employee • Check • Code • CheckViewDedCode • CheckViewDedAmt

  9. CheckView Tables • Vertical Data • Each Code And Amount Pair Is On A Separate Record

  10. CheckView Tables • Vertical Data

  11. CheckView Tables • Horizontal Data • All The Other Columns Have The Same Values On Each Record

  12. CheckView Tables • Horizontal Data

  13. CheckView Tables

  14. Solution • Database Grouping • Records Are Compressed • Data is Extracted And/Or Summarized • Using Derived Fields And Summary Functions

  15. Database Grouping • Records Are Compressed • Using Unique Values Of Fields • Include In Report • Value Only

  16. Database Grouping • Always Include In Report • CompanyCode • File# • SocialSecurity# • CheckViewChkSeq# • CheckViewPayroll# • CheckViewWeek# • CheckViewYear#

  17. Database Grouping • Always Include In Report • This Will Result In One Record Per • Employee • Check

  18. Database Grouping • Always Include In Report • This Will Result In One Record Per • Employee • CompanyCode • File# • SocialSecurity# • Check

  19. Database Grouping • Always Include In Report • This Will Result In One Record Per • Employee • Check • CheckViewChkSeq# • CheckViewPayroll# • CheckViewWeek# • CheckViewYear#

  20. Database Grouping • Always Include In Report • Before Database Grouping

  21. Database Grouping • Always Include In Report • After Database Grouping

  22. Database Grouping • Code And Amount Must Be Query Only • Or The Values Will Change How The Records Are Compressed • Only Used In SQL Derived Fields

  23. Database Grouping • All Other Amounts Should Be Query Only • Only Used In SQL Derived Fields

  24. Database Grouping • Any Other Fields Can Be Include In Report Or Query Only • Each Record Will Have The Same Value And Will Not Effect The Database Grouping

  25. Solution • Summarized Data • Using Derived Fields And Summary Functions • Add Up Compressed Vertical Data • Sum All Values SUM(Column Name)

  26. Data is Summarized • Summarized Data • Add Up All Deduction Amounts SUM(CheckViewDedAmt)

  27. Solution • Extracted Data • Using Derived Fields And Summary Functions • Extract One Occurrence Of The Duplicated Values • Sum Only Unique Values SUM(DISTINCT Column Name)

  28. Data is Summarized • Extracted Data • Add Up Only Unique Gross Pay Amounts SUM(DISTINCT CheckViewGrossPayA)

  29. Data is Summarized • Summary Functions

  30. Conclusion • Use Database Grouping To Get One Record Per Check • Remember • What You Include In Your Report Will Be Used To Compress The Records

  31. Conclusion • Always Include In Report • CompanyCode • File# • SocialSecurity# • CheckViewChkSeq# • CheckViewPayroll# • CheckViewWeek# • CheckViewYear#

  32. Conclusion • Summarize Vertical Data • Use Sum() To Add Up Compressed Vertical Data

  33. Conclusion • Extract Horizontal Data • Use Sum(DISTINCT ) To Extract One Occurrence Of The Duplicated Values

  34. Presented By CharlesCook.com Specializing In ReportSmith Training & Consulting Charles@CharlesCook.com

More Related