Books not been loaned
Submitted by markhughes on Mon, 2006-10-30 13:32.
Can anyone think of a way in BO of doing a report which shows books, in stock, that have NOT been loaned for say 2 years?
I can get a list of those in stock, and I can get a list of all loan transactions, but I then can't join the tables.
Any ideas?
joining queries in Talis Decisions
I don't know if you have seen the article on joining queries.
You want to link every dimension possible. This should at least be the item barcode but without knowing the objects you have picked in both queries it is difficult to say what other objects should be linked. Where possible dimension objects will be automatically linked. But location type objects usually have to be done manually as the names of the objects vary.
You also need to be careful when you list all the items. If you pick items that are in stock now you may be missing items that were in stock a year ago and issued. Also you don't necessarily want every loan transaction. You only need one row for each item loaned.
Brian Crampton
Developer, Talis
Book Loans
Brian, What I'm trying to achieve is getting a list of in stock items, at a branch, which have not had any loans for over two years, so the staff can see if the stock would be better suited at another branch, or a booksale, etc.
I have tried doing a report showing all items in stock: ITEM BARCODE, AUTHOR TITLE, where HOME SITE = 'IRBY' and ITEM STATUSES = 'In Stock'.
The problem I have is: How do I then select which items have not had a loan for over 2 years?
I can get a list of ALL loan transactions: BARCODE, TRANSACTION DATE where HOME SITE='IRBY', ITEM STATUSES='In Stock', ISSUE/RENEW/DISCHARGE = 'ISSUE/RENEW' but this will give me a list of ALL issues/renewals. Then I cannot link the two tables to get an 'overall' picture of what is in stock and what hasn't been loaned for over 2 years, as it returns ALL transaction per barcode, not just the latest one.
I think I'll need to join 2 tables because some books may be in stock but never had a loan, so it wouldn't show up in the loans table. So I'm guessing I'd need a table showin ALL books in stock, and another showing, if possible, the last issue/renewal transaction date for a book, then somehow combine the 2 tables...
I'm sure I must be able to do it somehow.....
http://www.wirral-libraries.net/
http://www.stembystem.co.uk/
book loans
If you add the Total loan transactions object to the second query then you will only get one row per item.
The actual number does not matter in this instance or picking up the last transaction. As the measure is an aggregate object it is just a way of returning a single row.
You also need to set a date range condition on the loan query to pick up the loans in the period you are looking at.
You will then be able to join the two queries and then create the variable and filter as detailed in the article to filter out the items that have not had a loan in the period.
The article looks at borrowers who have not had a loan as an example. The logic would be the same for items with no loans in a period.
What may cause a problem is if the stock has been moving around e.g. it is currently at IRBY but was not loaned in the period because it was somewhere else.
If you have stock that moves around a lot then a breakdown by site might not be so useful. You probably want to take a more global view in that instance and a breakdown by item type might be more useful.
Thanks
Brian Crampton
Developer, Talis