Custom PaperCut reports

Custom PaperCut reports

---

Can PaperCut MF report on…?

PaperCut has tons of custom PaperCut report options, and we often receive support tickets asking how to create custom reports; they usually start “can PaperCut report on (insert variable here)” and is one of those if I had a penny questions. So, if your question is can PaperCut report on…, more than likely the answer is yes. You have probably missed it in all the pre-built reports available (don’t forget to look at the ad-hoc report options). But, sometimes your customer needs a bespoke report based on their unique print/copy/scan/user data.

Luckily, PaperCut has your back here and allows you to create custom reports. Now, this is nothing new as you could have just used any number of tools to query the data in the external database but, to make life easier, PaperCut has now opened up the third-party reports engine it uses (Jasperstudio). Plus, these custom reports show up in the PaperCut Reports tab – cool, huh?!

One frequently requested custom PaperCut report is user activity. Granted, it was more of an issue before PaperCut offered unlimited users in v19, but site Admins still wish to see who is not actively using the system. Perhaps they need some training, maybe they are printing in an untracked manner (and you need to fix that), or perhaps they left the organisation and you need to do a bit of housekeeping. 

Who knows? Either way, our partners often ask us if PaperCut includes a report that will show inactive users.

How do you make a custom PaperCut report? 

Making a report isn’t a five-minute job; you’ll need to think about what you want to report on and how you want to display that data… but there are also some prerequisites. 

Getting started:

1. PaperCut MF needs to be hosted on an external database (not strictly true, but it’s not worth the extra effort… so pretend we never mentioned it).
2. You’ll need to be familiar with the PaperCut database schema. 
3. You will need to understand how to query and manage the external database you are working with.
4. You need to understand how Jasper Studio works.

I can see I’ve lost some of you already, sorry! There are plenty of guides for Jasper out there but be warned; there are going to be some hair-pulling moments.

Stop! Report Time – Break it down! 

Whenever we get any request for Professional Services work, we start by taking the requirement and breaking it down into smaller chunks. 

For a Custom Report, the steps will usually be something like:

• Create the SQL Query (and check it returns the data we expect)
• Add the query to Jasper Studio (the magic)
• Create a basic report to test (the hard bit)
• Tweak the design as needed (the really hard bit)

We wanted to show who was not using PaperCut so we kept the SQL query simple in this example. 

SELECT

tbl_user.user_name AS ‘Username’,

tbl_user.full_name AS ‘Full Name’,

tbl_user.created_date AS ‘Created On’,

tbl_user.created_by AS ‘Created By’

FROM

tbl_user

WHERE

tbl_user.last_user_activity IS NULL

AND

tbl_user.deleted = ‘N’;

This will return users with no activity – ever. After adding the query to Jasper Studio and some drop and drag, we can then run a report from PaperCut that looks like this (as a CSV file).

After that, there is not much to do other than to use your imagination. No activity ever is a bit extreme, so you might want to report on anyone inactive in the last 30 days or three months. You can do that by tweaking the WHERE:

WHERE

(

tbl_user.last_user_activity <= dateadd(DAY, –30, getdate())

OR

tbl_user.last_user_activity is NULL

What’s next?

What else would/could be useful for this report? Perhaps you might want to add the sync source? Maybe you will see a pattern and that all internal users are inactive, that may lead you to disable that feature to make the system more secure. You could then expand on the SQL query (like this):

SELECT

tbl_user.user_name AS ‘Username’,

tbl_user.full_name AS ‘Full Name’,

tbl_user.created_date AS ‘Created On‘,

tbl_user.created_by AS ‘Created By’,

tbl_user.primary_user_source_type AS ‘Sync Source’,

tbl_user.internal AS ‘Internal User’,

tbl_user.disabled_printing AS ‘Disabled In PaperCut?’

FROM

tbl_user

WHERE

tbl_user.last_user_activity IS NULL

AND

tbl_user.deleted = ‘N’;

We get something like this: 

You could also work out how to group users, so it is obvious who you need to worry about. For example, you might want anyone inactive for 90+ days to have a red background while anyone inactive for less than five days is green (perhaps they are new so have not had time to use it yet!). 

Pimp my report

You have a lot of control over what appears on a report and how that is presented – below is a quick example:

Our advice is: don’t waste precious time on making the report look pretty, more than likely you are going to export the report to a CSV and a CSV does not care about your logo, HTML markup or coloured borders around the footer. You can (and we have) make fairly pretty reports, but experience has shown us it was not worth the effort. If we make a report for you, feel free to dress it up a bit 🙂

If you wish to talk to us about custom PaperCut reports, then reach out via email, Slack (like all the cool cats) or good old fashioned phone.