<!-- wp:paragraph -->
<p>Can PaperCut MF report on…?</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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 <em>if I had a penny</em> questions. So, if your question is can&nbsp;<em>PaperCut report on</em>...,&nbsp;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.</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>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 (<a href="https://community.jaspersoft.com/project/jaspersoft-studio">Jasperstudio</a>). Plus, these custom reports show up in the PaperCut Reports tab - cool, huh?!</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:image {"id":8353,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://staging.selectec.com/wp-content/uploads/2020/01/papercut-custom-reports.jpg" alt="custom PaperCut reports" class="wp-image-8353"/></figure>
<!-- /wp:image -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>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.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Who knows? Either way, our partners often ask us if PaperCut includes a report that will show inactive users.</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":50} -->
<div style="height:50px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:heading {"align":"left","level":3} -->
<h3 class="has-text-align-left">How do you make a custom PaperCut report?&nbsp;</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:heading {"align":"left","level":4} -->
<h4 class="has-text-align-left">Getting started:</h4>
<!-- /wp:heading -->

<!-- wp:list {"ordered":true} -->
<ol><li>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).</li><li>You'll need to be familiar with the&nbsp;<a rel="noreferrer noopener" target="_blank" href="https://www.papercut.com/kb/Main/DatabaseSchema#overview">PaperCut database schema</a>.&nbsp;</li><li>You will need to understand how to query and manage the external database you are working with.</li><li>You need to understand how Jasper Studio works.</li></ol>
<!-- /wp:list -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":50} -->
<div style="height:50px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:heading {"align":"left","level":3} -->
<h3 class="has-text-align-left">Stop! Report Time - Break it down!&nbsp;</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Whenever we get any request for Professional Services work, we start by taking the requirement and breaking it down into smaller chunks.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>For a Custom Report, the steps will usually be something like:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>Create the SQL Query (and check it returns the data we expect)</li><li>Add the query to Jasper Studio (the magic)</li><li>Create a basic report to test (the hard bit)</li><li>Tweak the design as needed (the really hard bit)</li></ul>
<!-- /wp:list -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>We wanted to show who was not using PaperCut so we kept the SQL query simple in this example.&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<p><span style="font-weight: 400; color: #770088;">SELECT</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.user_name</span> <span style="font-weight: 400; color: #770088;">AS</span> <span style="font-weight: 400; color: #aa1111;">'Username'</span><span style="font-weight: 400;">,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.full_name</span> <span style="font-weight: 400; color: #770088;">AS</span> <span style="font-weight: 400; color: #aa1111;">'Full Name'</span><span style="font-weight: 400;">,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.created_date</span> <span style="font-weight: 400; color: #770088;">AS</span> <span style="font-weight: 400; color: #aa1111;">'Created On'</span><span style="font-weight: 400;">,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.created_by</span> <span style="font-weight: 400; color: #770088;">AS</span><span style="font-weight: 400; color: #aa1111;"> 'Created By'</span></p>
<p><span style="font-weight: 400; color: #770088;">FROM</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span></p>
<p><span style="font-weight: 400; color: #770088;">WHERE</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.last_user_activity</span> <span style="font-weight: 400; color: #770088;">IS</span> <span style="font-weight: 400; color: #221199;">NULL</span></p>
<p><span style="font-weight: 400; color: #770088;">AND</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.deleted</span><span style="font-weight: 400;"> = </span><span style="font-weight: 400; color: #aa1111;">'N'</span><span style="font-weight: 400;">;</span></p>

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>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).</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:image {"id":8358,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://staging.selectec.com/wp-content/uploads/2020/01/papercut-basic-custom-report.jpg" alt="custom PaperCut reports in progress" class="wp-image-8358"/></figure>
<!-- /wp:image -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>After that, there is not much to do other than to use your imagination. No activity <em>ever</em> 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:</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<p><span style="font-weight: 400; color: #770088;">WHERE</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">(</span></p>
<p style="padding-left: 80px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.last_user_activity</span><span style="font-weight: 400;"> &lt;= dateadd</span><span style="font-weight: 400;">(</span><span style="font-weight: 400;">DAY, -</span><span style="font-weight: 400; color: #116644;">30</span><span style="font-weight: 400;">, getdate</span><span style="font-weight: 400;">())</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400; color: #770088;">OR</span></p>
<p style="padding-left: 80px;"><span style="font-weight: 400;">tbl_user</span><span style="font-weight: 400; color: #0055aa;">.last_user_activity</span> <span style="font-weight: 400; color: #770088;">is</span> <span style="font-weight: 400; color: #221199;">NULL</span></p>

<!-- wp:spacer {"height":50} -->
<div style="height:50px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:heading {"align":"left","level":3} -->
<h3 class="has-text-align-left">What's next?</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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):</p>
<!-- /wp:paragraph -->

<p><span style="font-weight: 400; color: #770088;">SELECT</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.user_name</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Username'</span>,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.full_name</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Full Name'</span>,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.created_date</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Created On</span>',</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.created_by</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Created By'</span>,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.primary_user_source_type</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Sync Source'</span>,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.internal</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Internal User'</span>,</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.disabled_printing</span> <span style="color: #770088;">AS</span> <span style="color: #aa1111;">'Disabled In PaperCut?'</span></span></p>
<p></p>
<p><span style="font-weight: 400; color: #770088;">FROM</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user</span></p>
<p><span style="font-weight: 400; color: #770088;">WHERE</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.last_user_activity</span> <span style="color: #770088;">IS</span> <span style="color: #221199;">NULL</span></span></p>
<p><span style="font-weight: 400; color: #770088;">AND</span></p>
<p style="padding-left: 40px;"><span style="font-weight: 400;">tbl_user<span style="color: #0055aa;">.deleted</span> = <span style="color: #aa1111;">'N'</span>;</span></p>

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>We get something like this:&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:image {"id":8359,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://staging.selectec.com/wp-content/uploads/2020/01/papercut-custom-reporting-customisation.jpg" alt="" class="wp-image-8359"/></figure>
<!-- /wp:image -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>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!).&nbsp;</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":50} -->
<div style="height:50px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:heading {"align":"left","level":3} -->
<h3 class="has-text-align-left">Pimp my report</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>You have a lot of control over what appears on a report and how that is presented - below is a quick example:</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:image {"id":8360,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://staging.selectec.com/wp-content/uploads/2020/01/papercut-customised-report.jpg" alt="" class="wp-image-8360"/></figure>
<!-- /wp:image -->

<!-- wp:spacer {"height":20} -->
<div style="height:20px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:paragraph -->
<p>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 :)</p>
<!-- /wp:paragraph -->

<!-- wp:spacer {"height":50} -->
<div style="height:50px" aria-hidden="true" class="wp-block-spacer"></div>
<!-- /wp:spacer -->

<!-- wp:heading {"align":"left","level":4} -->
<h4 class="has-text-align-left">If you wish to talk to us about custom PaperCut reports, then reach out via <a href="https://staging.selectec.com/contact/">email</a>, Slack (like all the cool cats) or <a href="https://staging.selectec.com/contact/">good old fashioned phone</a>.</h4>
<!-- /wp:heading -->