PaperCut Blog

Tech & DevCoding

NEW IN 19.1: Custom Reports and a tale of two datasets

NEW IN 19.1: Custom Reports and a tale of two datasets

This is a story about two separate datasets – regular printing and specialty printing – and one troublesome request:

Is it possible to report on both as one?

It’s a tale wherein almost anything’s possible with a few SQL tricks up your sleeve, the know-how to navigate the PaperCut database schema, and our fancy new reporting feature.

Let’s jump in!

Prologue

Yours truly and the Technical Services team

As a proud PaperCutter in the Technical Services team, I regularly point PaperCut’s channel partners in the right direction.

This involves helping them get the most out of scripting and reporting, and providing product education via Technical Certification Programs and the like.

Specialty printing data in PaperCut

PaperCut’s workflow tool for 3D and specialty printing (formerly known as Job Ticketing) provides a simple interface for operators to easily organize the barrage of job requests from print rooms and FabLabs.

It also integrates with PaperCut MF’s users, shared accounts, and balances. So when jobs are submitted and printed:

Still with me? Excellent. Let’s get back to the crux…

Part 1: Two datasets in one report – fact or fiction?

Let’s say fact with a few twists

If an organization has a FabLab or print room, plus a fleet of everyday-use MFDs, surely it’s easy to see cost recovery with a PaperCut MF built-in report?

Sigh. Sadly, it’s not that easy. But fret not. It’s entirely possible with a bit of clever thinking and SQL-ing. First, let’s look at the request putting us to the test.

The request

A curious manager wants to produce a report showing two things:

  1. The number of people printing, copying, scanning, and faxing; and
  2. The departments and faculties people are charging to (our ‘Shared account print/copy/scan/fax – breakdown’ report is perfect for this).

The kicker? They want 3D printing, CNC milling, and booklet printing included in the breakdown as well. Great.

Actually, it is great. Because our shiny new reporting feature can save the day.

Custom Reports to the rescue

PaperCut has oodles of detailed reports, but they can’t cover everything. There’s always going to be specific data sought by a specific manager in a specific department.

With release 19.1, however, we’ve come pretty close to a solution in Custom Reports (definitely have a read if this announcement has you bouncing off the walls).

The short of it? You can now embed JasperReports with custom SQL into the PaperCut Application Server – and it’s awesome. And we’ll come back to it soon.

Jasper the friendly solution

With embedded JasperReports, you can one-click the report without having to open specific reporting software. You can even schedule the report right in PaperCut.

Basically, if you wanted to, you could:

  1. write a custom report in SQL
  2. format it in Jasper
  3. install the report file on the customer’s app server; and…

Boom! There it is: a new tab in the Reports section. Reports are natively supported in PaperCut as well, so you don’t have to install anything else on the server.

Now for the juicy stuff.

Part 2: How to unify two datasets for one report

Heads up! Skip over this part if you’re not interested in the nitty gritty of writing a custom report. Better yet, yell out for your favourite techie to come read it.

Using the right source report

To start, grab the source SQL of the built-in report we mentioned before (‘Shared account print/copy/scan/fax – breakdown’).

Hot tip: we publish the SQL of all our reports, so all you wizards who dive right into custom reporting should definitely bookmark that link above.

A sample of the ‘Shared account print/copy/scan/fax – breakdown’ report. Gorgeous, ain’t she?

You’ll see this report grabs information from the Job Log, which is where data gets plopped when anyone prints, copies, scans, or faxes.

It’s also where things get tricky:

On top of being stored in a separate database, every specialty print log is accounted for in the PaperCut MF database as a transaction (remember those?).

So how do we mash specialty print information into a report that does all its work in the Job Log, and has no business in the Transaction Log?

An “SQL-egant” solution

Remember when I mentioned SQL tricks at the outset? We made it, y’all. We made it. Here’s what you need to do:

  1. Write two SQL queries that produce the same layout of results – one from the Job Log, and one from the Transaction Log; and
  2. Smoosh (term coined by Damien, Global Technical Services Manager) these two ways of storing completed jobs together with a UNION statement, which makes them play (and look) nice together.

Here’s the source SQL of the original PaperCut report for print, copy, and scan by account (don’t ask why I decided on PostgreSQL):

SELECT 
	CASE WHEN ta.sub_name <> '' THEN
		concat(ta.account_name, '\\', ta.sub_name)
	ELSE
		ta.account_name
	END as account_name, --handle sub-accounts
	ta.pin, 
	ta.sub_pin,
	ta.balance,
	tpul.job_type, 
	sum(tpul.total_pages) as total_pages, 
	sum(tpul.usage_cost) as usage_cost, 
	sum(tpul.total_color_pages) as total_color_pages, 
	sum(tpul.duplex_pages) as total_duplex_pages,  
	count(*)  as total_jobs
FROM 
	tbl_printer_usage_log tpul 
	JOIN tbl_account ta on ta.account_id = tpul.assoc_with_account_id 
WHERE 
	ta.account_type = 'SHARED'  
	AND tpul.usage_date > (NOW() - interval '1 year')
	AND tpul.refunded = 'N'
	AND ((tpul.job_type = 'PRINT' and tpul.printed='Y') OR tpul.job_type != 'PRINT')   
GROUP BY 
	ta.account_type, 
	ta.account_name,
	ta.sub_name, 
	ta.pin, 
	ta.sub_pin,
	ta.balance, 
	tpul.job_type 
ORDER BY 
	ta.account_type, 
	ta.account_name, 
	ta.sub_name,
	tpul.job_type;

Here’s the resultant output:

account_namebalancejob_typetotal_pagesusage_costtotal_color_pagestotal_duplex_pagestotal_jobs
Admin-0.5PRINT30.5201
Admin-0.5SCAN10101
BIOLOGY-3.1SCAN6006006
Channel\\Asia-5.5PRINT30.5201
Channel\\Europe-24.95PRINT40.8401
Finance-2PRINT122804
Marketing-0.5PRINT30.5201
test-0.1COPY10.1001
test\\test-child-0.2COPY10.2101
test 2-0.6SCAN20.2201

And here’s my SQL query to pull specialty print transactions and job type (CNC milling, booklet printing, and so on) from the Transaction Log and present them in the same layout.

SELECT
	CASE WHEN ta.sub_name <> '' THEN
		concat(ta.account_name, '\\', ta.sub_name)
	ELSE
		ta.account_name
	END as account_name,   --handle sub-accounts
	ta.pin as PIN, 
	ta.sub_pin as sub_PIN,
	ta.balance as balance,
	SUBSTRING(tat.txn_comment, '.*\-\s\#\d{4,}\s\-\s(.*)\s\-') as job_type, 
	0 as total_pages, 
	sum(tat.amount) as usage_cost, 
	0 as total_color_pages, 
	0 as total_duplex_pages,  
	count(*)  as total_jobs
FROM 
	tbl_account_transaction tat
	JOIN tbl_account ta on ta.account_id = tat.account_id
WHERE 
	ta.account_type = 'SHARED'  
	AND tat.transaction_date > (NOW() - interval '1 year')
	AND tat.transaction_type = 'JARRAH_PRINT_ORDER'
GROUP BY 
	ta.account_type, 
	ta.account_name,
	ta.sub_name, 
	ta.pin, 
	ta.sub_pin,
	ta.balance, 
	job_type 
ORDER BY 
	ta.account_type, 
	ta.account_name, 
	ta.sub_name,
	job_type;

Here’s the resultant output:

account_namebalancejob_typetotal_pagesusage_costtotal_color_pagestotal_duplex_pagestotal_jobs
Channel\\Asia-5.53D print0-5001
Channel\\Europe-24.953D print0-10002
Channel\\Europe-24.95Document0-14.15001

We can see that:

  • account Channel\\Asia has one 3D print processed; and
  • Channel\\Europe has two 3D prints processed, plus one Document processed (similar to the original report where we saw print, copy, and scan jobs in the same layout).

And that means we have two queries that produce the same layout. Hooray! Now we just need them to display their datasets harmoniously together as if that’s what they were designed to do in the first place.

Weaving it together with a UNION statement

Not sure how to make a beautiful custom reporting union? Not to worry – I’m a certified celebrant. Here’s the general gist:

SELECT
[column names consistent in both queries]
FROM
(
QUERY 1

UNION

QUERY 2
) 
ORDER BY
[how you’d like to order things]

And viola! Our queries are happily smooshed together to produce the following glorious output:

account_namebalancejob_typetotal_pagesusage_costtotal_color_pagestotal_duplex_pagestotal_jobs
Admin-0.5PRINT30.5201
Admin-0.5SCAN10101
BIOLOGY-3.1SCAN6006006
Channel\\Asia-5.53D print0-5001
Channel\\Asia-5.5PRINT30.5201
Channel\\Europe-24.953D print0-10002
Channel\\Europe-24.95Document0-14.15001
Channel\\Europe-24.95PRINT40.8401
Finance-2PRINT122804
Marketing-0.5PRINT30.5201
test-0.1COPY10.1001
test 2-0.6SCAN20.2201
test\\test-child-0.2COPY10.2101

So now we have an SQL query that produces an organized dataset from both PaperCut and our specialty printing workflow tool.

Thing is, it’s not all that useful to a manager who wants a pretty report. So how can we leverage PaperCut’s new Custom Reports feature to beautify this haggard thing?

Part 3: Reports as ravishing as they are customizable

PaperCut’s Custom Reports have been designed to natively consume jrxml (JasperReports files), and render them through the standard PaperCut interface as either PDF or HTML. They can also look downright delicious.

Here’s your new step-by-step for stunning reports:

  1. Create a new JasperReport to house your newly written SQL.
  2. Tinker with it in Jaspersoft Studio to meet your beauty standards.
  3. Save the report.
  4. Edit variables, headings, and custom company logos.
  5. Save the file and copy it straight into the PaperCut Custom Reports directory: [app-server]\server\reports\custom\jasper.

So here’s what you’ll see in your PaperCut Admin interface:

Just one click away from the Fabio of reports

And here’s what your report can look like after it’s been schmick’d up in Jaspersoft Studio, and generated in PaperCut MF (by hitting the PDF icon pictured above):

MAGNIFIQUE!

Looks like a pretty tidy report, right? You betcha.

Epilogue

So there you have it. Different datasets mingling to make breathtaking data babies. And that’s just the tip of the Custom Reports iceberg.

Dare I say, anything you whip up in Jaspersoft Studio – using the PaperCut database as your data source – can be slid into PaperCut MF or NG to put a smile on your finance person’s dial.

Written a custom report for us in the past, used report-designing software, or simply have some ideas for Custom Reports? We’d love to hear from you in the comments.

Comments