Welcome To... - PowerPoint PPT Presentation

welcome to n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Welcome To... PowerPoint Presentation
Download Presentation
Welcome To...

play fullscreen
1 / 34
Welcome To...
79 Views
Download Presentation
javan
Download Presentation

Welcome To...

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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