× Keystone Training

Forum Q&A: Let's Talk Reporting

4 years 2 months ago #606 by patrick20k
KLAS has a lot of powerful options for finding information, from reports (there are some great ones featured in the Snapshot Reports blog post) to queries (and there are some good query tips on the Forum... check out the KLAS Core section of the Best of the Forum 2018 page). You can even combine queries and excel to search for information that isn't there .

But sometimes it's hard to know just where to look, and sometimes a piece of data you need can remain tantalizingly just out of reach.

So I'd like you to tell us what you're searching for!

Log in and reply in this thread to see if you can stump us on:
  • "Where do I find how many..."
  • or "Is there a report that shows..."
  • or even "Can I tell who..."

Bring all your reporting and data-finding questions and we'll do our best to bring you an answer!

Please Log in to join the conversation.

4 years 2 months ago #610 by NC-Craig
Here's one for you. I've been trying to solve this for years.

I'd like to be able to query/report on a patron's BARD subscription against demographic data (such as county code, veteran status, etc.) or for that matter any magazine subscription against demographic data. Is there any way to combine serials and patron module to do some finding?

Thanks for any insight.

Please Log in to join the conversation.

4 years 2 months ago #611 by patrick20k
Ooh, that is a good one. I'll do some looking and some asking around... most likely, this is one that will need to be solved by combining queries in Excel.

Please Log in to join the conversation.

4 years 2 months ago #612 by patrick20k
Alright, Craig, here we go! This is going to get a bit long, but I think you'll find it helpful.

You have a couple options here depending on what you're trying to do. If you have a specific demographic category you want to check on (for example, if you're going to a veterans' group in Raleigh and want to know how many vets in that area have a BARD subscription), you can use the same method described in the "How to Find Missing Information" download I linked to in the first post.

However, if you want to take a full list of the BARD subscribers and get various demographic breakdowns for it, you can do that in Excel using a pivot table! I had Mitake help me create one (I'm still learning how they work myself) and while this is one of Excel's more advanced functions, I think you'll really like what it lets you do with your data.

I've attached a spreadsheet with example data from our Users Conference database (meaning that all names, addresses, and contact info has been randomized and/or replaced with generated nonsense).
  • On sheet one is an export from the Patron Module--I used Active Raleigh Veterans just to get a reasonable amount of records to play around with.
  • Three new columns have been added to make some of the data easier to put into categories: Column I translates the patrons age into a standardized grouping (31-45, 46-55, etc); Column J truncates the full postal code down to 5 digits.
  • Sheet two is an export from the Serials module of all our DDB9 subscribers; this is what Sheet one Column K is using to look up whether or not a patron is subscribed.

If you don't want to narrow down your list of subscribers at all before you start looking at demographics, start with your exported list of subscribers and copy just the KLAS ID column to notepad, delete the heading and save the file. Then, use Import Query Set in the Patron Module, and export those results. That will get you the Sheet One patron demographic information without needing to add a DDB9 lookup column.

Now, the Pivot Table. This is on Sheet Three in the example file, and was created using just the data on Sheet One. When you click into the table, all of the columns from your export are available to add as rows, allowing you to easily get counts of the different demographics. Click the Plus Sign on a row to see the subcategory, or drag ZIP5 to the top of the Rows section to switch the order of the breakdown.

Play around with the example, then give it a shot! If you need help setting up your own, just let us know B) Thanks for the great question!

Please Log in to join the conversation.

Time to create page: 0.107 seconds

KLAS Users - Keystone Systems, Inc. logo

Keystone Systems, Inc.
8016 Glenwood Ave., Suite 200
Raleigh, NC 27612