SQL Reporting for Office Equipment

1. SQL Reporting for Office Equipment

2. Welcome Connect Conference 2013 This is an exciting opportunity for everyone involved. The benefits go beyond just training and education—collaborate with your peers and inspire others around you. educate • collaborate • inspire

3. SQL Reporting for Office Equipment In this informative session, we will cover the SQL report options for Machines. Did you know that you can also create your own customized SQL reports? Come learn about the SQL database relationships and how to put it all together.

4. SQL Reporting for Office Equipment Using Preview Reports • Using the Extended Dollar Report for Machines • Machine Contact Report Introduction to SQL Reporting • Connecting to Ensitedatasource • Component 1 Report Designer • Microsoft SQL Server Management Studio Express Other Reporting Tools for Equipment

5. SQL Reporting for Office Equipment Preview Reports

6. SQL Reporting for Office Equipment Preview Reports

7. SQL Reporting for Office Equipment Preview Reports

8. SQL Reporting for Office Equipment Preview Reports

9. SQL Reporting for Office Equipment Design a Report Path to files may vary, consult your IT person. Examples: \\192.168.1.100\reporting\eci2\ \\ddms-server\reporting\eci2\ \\ddms-server\reporting\eci2\ \\ddms-server\DDMS\reporting\eci2\

10. SQL Reporting for Office Equipment Design a Report

11. SQL Reporting for Office Equipment Microsoft SQL Server Management Studio • Download and install Microsoft® SQL Server® 2008 Management Studio Express • See Install Instructions here: • http://www.microsoft.com/en-us/download/details.aspx?id=7593

12. SQL Reporting for Office Equipment Connecting to Ensitedatasource • Consult your IT person for server name or IP address. Examples: • 192.168.1.100\eci2 • ddms-server\eci2 • Login: netclient • Password: db_user

13. SQL Reporting for Office Equipment Ensite_Machine

14. SQL Reporting for Office Equipment Ensite_Machine: Sample Queries

15. SQL Reporting for Office Equipment Ensite_Machine: Sample Query with Join

16. SQL Reporting for Office Equipment Ensite_Machine: Sample Query with Join Machine to Meter Query: Selectmachine.Id,machine.SerialNumber,CAST(machine.SoldDateasdate)ASSoldDate,meter.MeterType,meter.BeginningClicks,cast(meter.BeginningClicksDateasDATE)asBeginningClicksDate fromEnsite_Machine.dbo.MachineMeter meter innerjoinEnsite_Machine.dbo.Machinemachine onMachine.Id=meter.SystemNumber+meter.SubNumber

17. SQL Reporting for Office Equipment Ensite_Machine: Sample Query with Join BEI FixbillQuery (Machines Sold in April 2013): Selectmachine.Id,machine.SerialNumber,CAST(machine.SoldDateasdate)ASSoldDate,meter.MeterType,meter.BeginningClicks,cast(meter.BeginningClicksDateasDATE)asBeginningClicksDate fromEnsite_Machine.dbo.MachineMeter meter innerjoinEnsite_Machine.dbo.Machine machine onMachine.Id=meter.SystemNumber+meter.SubNumber whereyear(SoldDate)='2013'andmonth(SoldDate)='04'

18. SQL Reporting for Office Equipment Ensite_Machine: Sample Queries on Sales History Multiple Queries to Bring up Details for One Invoice SELECT*FROMDBO.SalesOrderDetail WHEREDBO.SalesOrderDetail.IdLIKE'%100193%' SELECT*FROMdbo.SalesOrder WHEREDBO.SalesOrder.IdLIKE'%100193%' SELECT*FROMdbo.SalesOrderDetailNotes WHEREdbo.SalesOrderDetailNotes.OrderNumberLIKE'%100193%' SELECT*FROMdbo.SalesOrderDetailNotesDetail WHEREdbo.SalesOrderDetailNotesDetail.IdLIKE'%100193%'

19. SQL Reporting for Office Equipment Other Reporting Tools for Equipment • Sales Order History and Analysis • Service Workorder Journal Reporting • Parts Return (Core/Recyclables) • Toner Yield • Tech Recap

20. SQL Reporting for Office Equipment Sales Order History and Analysis

21. SQL Reporting for Office Equipment Sales Order History and Analysis 1-9=Meter billing C=Contract (contract, rent, demo, loan) sell/bill E=Lease sell/billing S=Serial item on work order or Supply bill d=Demo picker D=Deferred income-system generated invoice I= Machine Trade-in L=Labor N=Supply item on work order or supply bill P=Part-serialized T=Travel X=Part-non serialized Y=Machine line when selling a machine

22. SQL Reporting for Office Equipment Sales Order History and Analysis

23. SQL Reporting for Office Equipment Sales Order History and Analysis

24. SQL Reporting for Office Equipment Parts Return

25. SQL Reporting for Office Equipment Parts Return

26. SQL Reporting for Office Equipment Parts Return

27. SQL Reporting for Office Equipment Toner Yield

28. SQL Reporting for Office Equipment Toner Yield

29. SQL Reporting for Office Equipment Tech Recap

30. SQL Reporting for Office Equipment Additional Resources • Setting Up & Using SQL Preview Reports for DDMS • http://www.ddms.com/Resources/Support/faq/utilities/SQLReports.pdf • Advanced SQL Reporting Guide • http://www.ddms.com/Resources/Support/faq/utilities/SQLReportsAdv.pdf • Setting up and using the C1 Report Designer • http://www.ddms.com/resources/support/faq/utilities/c1ReportSetup.pdf • Download and install Microsoft® SQL Server® 2008 Management Studio Express • http://www.microsoft.com/en-us/download/details.aspx?id=7593 • Machine Report Manual • http://www.ddms.com/Resources/Support/faq/machine/MachineReports.pdf

31. SQL Reporting for Office Equipment Don’t forget to turn in your session survey for a chance to win a $250 gift certificate Surveys can be turned in at the Conference Registration in the Trinity Foyer or in the Lab located in the Pegasus Ballroom (both located on the lobby level)

32. SQL Reporting for Office Equipment

33. Customer Service & Support Contact Information • Support • 800-366-4778 • Monday – Friday • 7AM-7PM CST • Training • 800-959-3367 Option 5 • professional.services@ecisolutions.com • Virtual class training • One on One training • Onsite training