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.
"There is no try, there is only do..."
The administrator has disabled public write access.
Forum Q&A: Let's Talk Reporting
1 year 6 months ago #611
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 Thanks for the great question!