Example SQL Queries (when running on an external database)

KB Home   |   Example SQL Queries (when running on an external database)

Main.ExampleSQLQueries History

Hide minor edits - Show changes to output

June 16, 2016, at 04:12 PM by timg - Added links to custom reporting example KB
Added lines 6-7:

Note: You can find a detailed run-through of [[https://www.papercut.com/kb/Main/CustomReportingByExample|building custom reports with Crystal Reports]] as an example. Additionally you can refer to the [[https://github.com/PaperCutSoftware/PaperCutExamples/tree/master/Reports/Standard_PaperCut_Queries|repository of the SQL queries]] used in the PaperCut reports as starting points for your customized queries.
January 17, 2014, at 11:00 PM by TimG - Added link to DB schema page
Added lines 7-8:
There is also more information about the structure of the database in this link detailing the [[https://www.papercut.com/kb/Main/DatabaseSchema|Database Schema]] being used.
Changed line 43 from:
[-Keywords: custom SQL, custom reports, data access-]
to:
[-Keywords: custom SQL, custom reports, data access-]
Changed line 1 from:
(:title Example SQL Queries (when running on external database):)
to:
(:title Example SQL Queries (when running on an external database):)
Added lines 1-41:
(:title Example SQL Queries (when running on external database):)
(:description Example SQL queries for custom data lookup and reports when running PaperCut on an external database.:)
(:keywords custom SQL, custom reports, data access:)

When running PaperCut on one of the [[https://www.papercut.com/products/ng/manual/ch-ext-db.html|supported external databases]] SQL queries may be run to find specific data or produce reports beyond the [[https://www.papercut.com/tour/reporting-in-detail/|many standard reports]] available.  Below is a collection of SQL select statements that may assist when writing a custom query.

!!Any Supported External Database (standard SQL)

!!!Print logs
->''Extended print log details, one line per job.''
[@
SELECT u.user_id, u.user_name, u.full_name AS user_full_name, p.server_name AS printer_server_name, p.printer_name, p.display_name AS printer_display_name, p.location AS printer_location, pul.printer_usage_log_id, pul.usage_date, pul.usage_day, pul.used_by_user_id, pul.charged_to_account_id, pul.usage_cost, pul.usage_allowed, pul.printer_id, pul.job_id, pul.document_name, pul.client_machine, pul.total_pages, pul.total_sheets, pul.copies, pul.paper_size, pul.paper_height_mm, pul.paper_width_mm, pul.printer_language, pul.document_size_kb, pul.denied_reason, pul.duplex, pul.gray_scale, pul.printed, pul.cancelled, pul.refunded, pul.assoc_with_account_id, pul.total_color_pages, pul.color_pages_estimated, pul.job_type, pul.invoiced, pul.job_comment, pul.protocol, pul.original_usage_cost, pul.refund_status, pul.refund_request_id
  FROM tbl_printer_usage_log pul
  JOIN tbl_printer p ON pul.printer_id = p.printer_id
  JOIN tbl_user u ON pul.used_by_user_id = u.user_id;
@]

!!Database Specific (uses specific functions)

!!!Summary by month by department
->''The number of pages printed by each department each month.''
!!!!Microsoft SQL Server
[@
SELECT u.department "Department", Month(pul.usage_date) "Month", SUM(pul.usage_cost) "Cost", COUNT(pul) "Jobs", SUM(pul.total_sheets) "Sheets", SUM(pul.total_pages) "Pages", SUM(pul.total_color_pages) "Color Pages", SUM(pul.total_pages - pul.total_color_pages) "Grayscale Pages"
  FROM tbl_printer_usage_log pul
  JOIN tbl_user u ON pul.used_by_user_id = u.user_id
  GROUP BY u.department, Month(pul.usage_date)
  ORDER BY u.department, Month(pul.usage_date);
@]
!!!!`PostgreSQL
[@
SELECT u.department "Department", date_part('month', pul.usage_date) "Month", SUM(pul.usage_cost) "Cost", COUNT(pul) "Jobs", SUM(pul.total_sheets) "Sheets", SUM(pul.total_pages) "Pages", SUM(pul.total_color_pages) "Color Pages", SUM(pul.total_pages - pul.total_color_pages) "Grayscale Pages"
  FROM tbl_printer_usage_log pul
  JOIN tbl_user u ON pul.used_by_user_id = u.user_id
  GROUP BY u.department, date_part('month', pul.usage_date)
  ORDER BY u.department, date_part('month', pul.usage_date);
@]
----
''Categories:'' [[Category.Databases|+]]
----
[-Keywords: custom SQL, custom reports, data access-]

Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.

Article last modified on June 16, 2016, at 04:12 PM
Printable View   |   Article History   |   Edit Article