Filtering by MARC fields

Are you subscribed to  the Evergreen Reports Mailing List? If not -- and you are reading this blog -- you might want to consider joining the list.

If you are on the list, then you saw the following question posted earlier this week:

How do you filter by a MARC field in reports? For instance, I'd like to find all bib records where "CGCC" is present in 856 $y. Or I'd like to find all bib records where the 5th position of the leader is "d".

I have created report templates based on the MARC record before.  Specifically, I created a shelflist report based on keyword to help us create thematic book displays, but I lost that template somewhere along the way. (The reports module does not make it easy to stay organized, does it?) I have also created reports based on MARC fixed fields to help with database clean-up. (Check out this handout.)

To figure out how to get started on the requested template, I first needed to review how Evergreen organizes MARC data. You can do this yourself by creating a report template that will spit out all the MARC data for just one particular bibliographic record (using the TCN value as the filter):

Source = Bibliographic Record (BR)

Display Fields
BR -> TCN Value
BR -> Flattened Marc Fields -> Field ID
BR -> Flattened Marc Fields -> Tag
BR -> Flattened Marc Fields -> Indicator 1
BR -> Flattened Marc Fields -> Indicator 2
BR -> Flattened Marc Fields -> Subfield
BR -> Flattened Marc Fields -> Normalized Value

Base Filters
BR -> TCN Value [EQUALS]

Check out the example I ran.

As you see in the example, Evergreen creates a data record for each subfield within each MARC tag.

Therefore, to run a report that creates a simple list of TCNs with title and call number information based on some specific value within a MARC tag, you could create the following template:

Source = Bibliographic Record (BR)

Display Fields
BR -> TCN Value
BR -> Simple Record Extracts -> Title Proper
BR -> Call Numbers -> Copies -> Shelving Location -> Name (Rename "Shelving Location")
BR -> Call Numbers -> Call Number Label
BR -> Call Numbers -> Copies ->Copy Status -> Name (Rename "Copy Status")

Base Filters
BR -> Is Deleted [EQUALS]  false
BR -> Flattened Marc Fields -> Tag [CONTAINS MATCHING SUBSTRING IGNORE CASE]
BR -> Flattened Marc Fields -> Subfield [CONTAINS MATCHING SUBSTRING IGNORE CASE]
BR -> Flattened Marc Fields ->Normalized Value [CONTAINS MATCHING SUBSTRING IGNORE CASE]
BR -> Call Numbers ->Owning Library ->Org Unit ID [IN LIST]

Going back to the original question posted on the list, let's consider the first part of the question:
 "I'd like to find all bib records where "CGCC" is present in 856 $y."

We can run a report with filter parameters set to:
- Tag = 856
- Subfield = y
- Normalized Value= CGCC

Now this is a report very specific to a particular library's needs, so let's consider a couple other possible uses for this template:

LIST OF BOOKS BY A SPECIFIC PUBLISHER
- Tag = 260
- Subfield = b
- Normalized Value= Lonely Planet

LIST OF BOOKS BY SUBJECT HEADING
- Tag = 650
- Subfield = a
- Normalized Value = Astronomy

Now for the second part of the question:

I'd like to find all bib records where the 5th position of the leader is "d".

For this we would set the user parameters to:
- Tag = LDR
- Subfield = ]
- Normalized Value = d

However, this is not necessarily the best solution for every library. The original requester really just wants those records that have "d" in the fifth position in the leader. (The leader is a long string that includes all sorts of information about the record.) Unfortunately, the reports module does not allow us to filter for data in a specific position in a string. If you have back end access to the template, a SQL report could provide this level of granularity in the filtering no problem.

If you clicked the link above, you will see that there are 3 different reasons why "d" would appear in the leader:

05 - Record status = Deleted (THIS IS WHAT WE WANT)
06 - Type of record =  Manuscript notated music
07 - Bibliographic level =  Subunit

If you work at a public library, you probably don't have to worry about having records with manuscript notated music, nor subunits of archival material. However, in an academic library you could have issues with the report, getting unwanted manuscript and archival records in your results.   Fortunately, these types of materials can be filtered out since these settings also show up in the fixed fields.

Just add the following to the base filters in the template:

 BR -> Fixed Field Entry -> BLvl [NOT IN LIST] d

 BR -> Fixed Field Entry -> Type [NOT IN LIST] d

If you are hoping for a list of records for manuscript notated music that ALSO have a deleted record status, then you really need an SQL report.

I have one last thing to add. You will notice that I had suggested entering the end bracket as the parameter for the subfield.  The end bracket is the truncation symbol used for Evergreen reports when entering your filtering parameters. (Very useful to remember!) I used the end bracket here because the leader does not have a subfield. In the Evergreen table, that cell is blank. Yet, when creating the report you cannot leave the parameter field blank; Evergreen refuses to run a report with any blank parameter fields. So an easy workaround is to just use the end bracket. Another option would be to create a brand new template without a filter for subfield, but why have two templates when you just need one? (Anything to help keep our templates organized, right?)

Holds List

I received the following request a while back:

Could I get your help, at your convenience, with creating a template for available holds by date that have not been picked up yet?

The information I need is
1. Pickup library
2. Title, author and barcode of available hold
3. Date received here
4. Patron barcode and first and last names

...

I want to be able to retrieve this report weekly for items still here that exceed the 7 days. We hold them here and use that list, arranged in excel alphabetically by patron last name, to remove the items from the hold shelf. Right now we use the Browse holds shelf tab and put them in order by timestamp but of course there is no way to arrange that batch of items alphabetically.

Fortunately, coming up with a holds shelf list that can be sorted by multiple fields is easy to do.  Painfully easy. But before I spill that lil' secret, let me first share my story on how I created this report.

I started constructing a report template using the "Hold Transit" source list, but I soon remembered that this would only include items on the holds shelf that have been transited to our library. It would not include holds on items already in the library. Try again.

I began afresh using the "Hold Request" item source. At first, I thought this report would be so easy to construct. (Oh, foolish me!) Thankfully, adding the display fields was pretty straightforward.  All I needed to do next was add filters that limited by:

- Pickup Library
- Available Date
- Item's Copy Status = "On Holds Shelf"

Not so simple. I soon realized that the "Holds Request" source list does not provide an "Available Date" field. It does have a "Shelf Time" field, but I was not familiar with that field (and thus skeptical of it - more on that later.) I decided to filter by "Last Edit Date/Time," based on the assumption that the last time an item is edited should be when it is checked in and placed on the holds shelf.

I ran the report.  Knowing that I rarely create a report right the first time, I was ready for a li'l weirdness, and weirdness I got. Sure I got some "good results," but they were followed by a long, long list of "bad results."

And these were clearly "bad results." They only displayed patron data, with no item data at all. Just blank fields. I had no idea why Evergreen was providing me a list of patron names, when I was specifically filtering for items. (I later learned that some of these were lingering hold requests that need cleaning up.)

When I tried filtering out those bad results by adding the filter:

- Item barcode NOT NULL OR BLANK

That had no effect at all. Grrrr.... I then tried:

- Item barcode => 1.

Yet, I continued to get rows of blank fields where barcodes should be. (I still don't understand that.) So I added all sorts of display fields to troubleshoot the report. I added:

- Hold ID
- Hold Cancel Date
- Hold Expire Date
- Capture Date
- Request Date

I will spare y'all the details about all the template permutations that I had created, with no success at ridding the report of those bad results. I was so very frustrated, wondering what I was not seeing, begging Evergreen to be nice to me. (Did I mention that there is a punch line to this story?)

But I did finally get rid of the blank rows by including the following filters:

- Fulfillment Date IS NULL
- Item is NOT DELETED

I was happy. Progress. But then I realized that my results were still not right. I was getting information on items that were on some other library's holds shelf. WHAT?!? Apparently, these items were showing up because we had been the pickup library for that item once upon a time.

So to clear that problem I added the following filter:

- Last Editing User's Home Library (Org Unit IN LIST)

Results looked good at first glance. I thought I was done. I printed the results. Woo-hoo.

Then I saw that I had duplicate data. The hold ID and the patron info were different, but the item info was the same on several rows. It was like I had multiple copies of the same item on the holds shelf. I realized that previous holds for items currently on the shelf were showing up on the list. These were previous holds requests where we were the pickup library, but the hold had been canceled. (I had already filtered out any previous holds that had been fulfilled.)

So I added the filter:
- Hold Cancel Date/Time NOT NULL

YAY! That time I got it. I was so happy and proud of myself. Until...

I got smart. I was going to double check my results. I went to check my results in Evergreen, comparing my results with Evergreen's "Browse Holds Shelf." That was when it all clicked. We didn't need no stinking report.

*blink blink*

Evergreen has the option right there to export the holds shelf list to a .csv file. Just take that file, open in Excel, and the data can be sorted any which way.

*slaps forehead*

Yes, it is true. A report is not always necessary.

*sigh*

Fortunately, it was a process of discovery. (Frustrating discovery, yes, but discovery nevertheless.)

I learned -- the hard way -- that while it would seem like asking for hold request information on a "Currently Targeted Copy" would only provide information about the current hold request, Evergreen begs to differ. Past data has to be carefully filtered out.

I also discovered new things like:

- Items with the status "On Holds Shelf" but not captured for a hold
- Weird, lingering hold requests that could use cleaning up
- The Shelf Time field

I'll write more about those exciting topics in my next post.

Thanks for reading. And if you would like detailed instructions on how to create my report -- even though Evergreen already provides you with all the information you need to clean up your holds shelf -- please just let me know.

Copy and Paste Part 2: Formulas

This is the second in a series about copying and pasting in Excel.  It may seem a little too basic of a topic, but Excel offers several helpful pasting options that many folks do not even know exist. In this series, I will show y'all those features that I use regularly when working with my Evergreen reports.

In the previous post in this series, I talked about selecting multiple cells in Excel so you can copy and paste data throughout a table. The example I used in the video demonstration came from a question about step #5 in this handout where we must paste a formula in multiple cells.

What I did not happen to mention in the video was that Excel was doing more than just simple copying and pasting. It was not just taking the same data and duplicating it "verbatim" into each cell. Excel was taking the formula that I had copied and reconstructing it relative to each cell.

Now for simple numbers, text and dates, Excel handles copying and pasting like most other computer programs. It pastes the information exactly as it was copied, including any special formatting like font color, size, etc.  BUT for formulas, Excel does things differently. 



Take the following simple example:

For Excel to add the total, we type the following formula into B5:

  =SUM(B2:B4)

This will add up everything from B2 through B4,
giving us 15.



But there's a little more to know about the formula we just typed into B5:

It is using what is called relative cell references. We know this because we do not see the symbol "$" in the formula.  The "$" indicates absolute cell references, which we will talk about later.

Because the formula is using relative cell references, what the formula is really stating is "add together the 3 cells above me" ...as illustrated here.




We then add two more columns of data, which means we need the "add together the 3 cells above me" formula again:

All we need to do is simply copy B5, select the C5 and D5, and then paste!






Excel will automatically change the cell references relative to the cell in which you paste the formula:

(Toggle to view formulas, instead of values, by clicking Ctrl + `)

The automatic updating of cell references is a lovely feature of Excel.  It is so useful that relative cell references are the default for formula creation.  BUT sometimes you do not want Excel to change the cell reference when you are copying and pasting formulas.  This is when you want to use absolute cell references in a formula.

As I mentioned previously the "$" sign before a cell reference indicates that it is absolute. In other words, it is unchanging; it is not relative.  

• Both column and row references can be absolute ($A$1). 
• Or only the column reference, but the not the row ($A1). 
• Or vice versa (A$1).

Basically, whatever part of the reference you want to stay the same in the formula, regardless of where you move that formula, you must add a "$" before it. 

So to continue with this simple example, we have totaled the number of programs by month and by type, with the grand total in cell E5:

We now want to know how the programs were distributed across the months as a percentage of the total programs.  So we add a new header to column F and then add the following formula to cell F2:

=E2/$E$5

The first part of the formula uses relative cell reference and the second part uses absolute.  The formula translates to "divide the cell directly to my left by E5," as illustrated below:



Now when we copy and paste that formula into the cells below, Excel will change the cell references appropriately  in first part of the formula, but always use the grand total value in E5.

 









Now that we understand absolute and relative cell references, I can keep my promise and explain the formula I introduced in my last post: 
 

Copy and Paste Part 1: Selecting Multiple Cells

This is the first in a series about copying and pasting in Excel.  It may seem a little too basic of a topic, but Excel offers several helpful pasting options that many folks do not even know exist. In this series, I will show y'all those features that I use regularly when working with my Evergreen reports.

Someone had recently asked me how to copy and paste a formula throughout a table. (Specifically, they were stuck on step 5 on this handout.) I thought, "What a great idea for a post!" So I jumped right into writing a detailed explanation of formulas and ways to construct formulas so that they can be pasted throughout a table. I was almost done with post before realizing that I had totally jumped past the original question.  The original question was really about the mechanics of selecting multiple cells in Excel so that the formula could be pasted in one fell swoop.

Everyone needs to know how select multiple cells to really take advantage of Excel. It might be easier to understand how this works and why it is useful by watching Excel in action:




To reiterate what I stated in the video, to select multiple cells you can either:

-- Click one cell with the mouse, then hold down the Shift key while using the arrow keys to select adjacent cells,  
OR
-- Click one cell with the mouse, then while continuing to press down the mouse button, drag the mouse to select adjacent cells

I also mentioned the following keyboard shortcuts:

To copy --> Ctrl + C
To paste --> Ctrl + P

Now there are other ways to select multiple cells in Excel that I did not cover in the video, and I imagine I will cover those as the need arises.  But for good measure, I will go ahead and mention them here:

-- You can select an entire column or row by clicking on the column or row headings. The headings are the grey boxes with either letters (above each column) or numbers (beside each row).

-- You can then select multiple rows and columns following the same method that you learned in the video (that is, using the Shift and arrow keys, or by using the mouse).

-- You can also select non-adjacent cells by holding down the Ctrl key while clicking each of the individual cells you want to select.

I hope this was helpful to some of y'all. If you have any questions about anything mentioned above, please feel free to ask me in the comments section.

Current State of the To Do List

The next few posts will focus on responding to questions I have received since the conference session and last week's PINES reports training.

Currently, that list includes:

• Copying and pasting formulas in Excel
• A holds shelf report
• An empty volumes report
• A "last item on bib record" report to help maintain OCLC holdings 

(Edited for typos on 5/15/11.)

 

Why this blog...

For a few months now I have toyed with the idea of starting this blog. Nothing glamorous. Just a blog to keep track of what I have learned while working with the reporting module of Evergreen.  I thought a couple of my fellow PINES folk might even find the blog helpful.

But while presenting at the 2011 Evergreen International Conference, I suddenly met all sorts of folks struggling to get the data they needed out of Evergreen. I also learned at the conference that the Evergreen community has been growing quite fast. (Go here to learn how to submit your prediction of when the 1000th library will migrate to Evergreen.)  Of course with more libraries adopting the Evergreen ILS, there will be more folks struggling with Evergreen reports.

So I decided to go for it. Share what I have learned thus far. And share my current challenges. And hope that it is helpful. 

This blog is basically an extension of my conference session, Making the Most of Evergreen Reports with Excel.  Except this time I do not have to worry about a 20 minute time limit, the small screen size of my netbook, nor my internet connection not working. (Of course, the lesson there is to always create an offline backup for any presentation. Always!).

I certainly do not know everything about Evergreen reports; I am still learning. My experience is limited to the staff client, although one day I hope to learn more about SQL reporting. 

I most certainly do not know everything there is to know about Excel.  I learn new tricks as they are needed.

My hope is that this blog will be useful not only to those just getting started with reports but also to those more advanced reporters.  I am hoping this blog will be a place where folks feel comfortable asking questions -- simple and advanced.  Of course, I hope folks will also share their tips, tricks, and best practices along the way.

Finally, my greatest hope is that through the work of all the libraries and consortia paying for development, through the work of the Evergreen's Reports Taskforce, and through the discussions we have here, at the conferences, and elsewhere, we one day might not have to struggle quite so hard to get the data we need. Until then, let us embrace the struggle!

(Edited for grammar on 5/15/11.)