Report "Circulation Stat 7: Charges by Circ Hap Loc Item Type and Item Loc" results do not match Access results or output

[gibsonjc]

Local Circulation Statistics > Annual Circulations Statistics Package > Circulation Stat 7: Charges by Circ Hap Loc Item Type and Item Loc

Original at: https://www.carli.illinois.edu/products-services/i-share/reports/secure/sql-loc-circ#circstat7

When I run this query in MS Access for KCC with start date 1/1/2016 and end date 1/15/2016, I get: CircGroup ItemType ItemCurrentLocCode ItemCurrentLocName Charges Main Circ Group AV2 KCCCirc Circulation Desk 2 Main Circ Group AV3 KCCAB Audio Book 7 Main Circ Group AV6 KCCCirc Circulation Desk 1 Main Circ Group Book1 KCCLower Juvenile 4 Main Circ Group Book1 KCCPopFic Popular 4 Main Circ Group Book1 KCCStacks Stacks 94 Main Circ Group Book1 KCCYA Young Adult 5 Main Circ Group BookRE KCCReserve Reserve 3 Main Circ Group BookRE KCCStacks Stacks 8 Main Circ Group default KCCCirc Circulation Desk 2 Main Circ Group Reserve1 KCCCirc Circulation Desk 5 Main Circ Group Reserve1 KCCReserve Reserve 12 Main Circ Group Reserve3 KCCCirc Circulation Desk 2 Main Circ Group VideoRE KCCVideo Video 32

When I run this in web-reports for KCC with start date 01/01/2016 and end date 01/15/2016, I get: Circ Group Item Type Item Current Location Code Item Current Location Name Charges Main Circ Group 3 KCCStacks Stacks 34 Main Circ Group 4 KCCReserve Reserve 2 Main Circ Group 15 KCCCirc Circulation Desk 1

[csaundrs]

I have looked at this one and have a query that is a closer approximation of the original, but it does not work. I will need to look at this some more....

[csaundrs]

The stated purpose of this report is:

What it does: Prompts for a starting and ending date, then counts the charge transactions by circulation policy group, item type, and item location

So I tried to check the same date range you had above and found that there were only 38 total charges in that timeframe for KCC.

1 select count(A.CHARGE_TYPE) 2 FROM KCCDB.CIRC_TRANSACTIONS A 3* WHERE A.CHARGE_DATE BETWEEN '01-JAN-2016' AND '15-JAN-2016' SQL> /

COUNT(A.CHARGE_TYPE)

              38

I believe that when the report is trying to group them by policy group, item type, and location it should be the same total number or a subset.....so I'm very confused as to how this Access Report can be delivering the results it is supposed to...

[csaundrs]

So Debbie helped me figure out what was going on with this one. The charges need to come from both circ_transactions and circ_trans_archive.... It took a decent amount of changing the query, but I think it gives the same results as the MS access version now.

Please test this Circ Stat 7 report on devel and if it looks good, push to production.

[gibsonjc]

This is much closer in that the counts appear to be correct now, but there are still a few differences in output:

1. In the Access version, the Item Type column displays the name; the web-reports-devel version displays the code. Can you translate the code to the name for this column?
2. In the Access version, instead of showing blank data in the Item Current Location Code and Item Current Location Name columns, the output shows "deleted" and "deleted item". If this cannot be duplicated in web-reports, I can add a note explaining that blank means deleted.
3. When I try to change the sorting by clicking on one of the other columns, I receive this error: DataTables warning: table id=d-tables - Ajax error. For more information about this error, please see http://datatables.net/tn/7

For this test I ran it on devel in KNXdb. Access Output: CircGroup ItemType ItemCurrentLocCode ItemCurrentLocName Charges HSL (Seymour) Circ Group Book1 deleted deleted item 1 HSL (Seymour) Circ Group Book1 hslcamp HSL: Campbell-circulating 7 HSL (Seymour) Circ Group Book1 hsldeweys HSL: Deweys 10 HSL (Seymour) Circ Group Book1 hslfacdev HSL: Faculty Development 1 HSL (Seymour) Circ Group Book1 hslmain HSL: Main Stacks 467 HSL (Seymour) Circ Group Book1 hslnewbks HSL: New Book Shelves 6 HSL (Seymour) Circ Group Book1 hslovlc HSL: Oversized LC 1 HSL (Seymour) Circ Group Book2 hslcurrent HSL: Current Books 3 HSL (Seymour) Circ Group BookRE hsllimited HSL: Limited Editions 2 HSL (Seymour) Circ Group Recording1 cfacd CD by Accession# 1 HSL (Seymour) Circ Group Reserve2 deleted deleted item 16 HSL (Seymour) Circ Group Reserve2 hsldvd HSL: DVDs 1 HSL (Seymour) Circ Group Reserve2 hslmain HSL: Main Stacks 48 HSL (Seymour) Circ Group Reserve2 hslnewbks HSL: New Book Shelves 1 HSL (Seymour) Circ Group Reserve3 hsldvd HSL: DVDs 17 HSL (Seymour) Circ Group Reserve3 hslequip HSL: Equipment 253 HSL (Seymour) Circ Group Reserve3 hslmain HSL: Main Stacks 1 HSL (Seymour) Circ Group Reserve4 hsldvd HSL: DVDs 14 HSL (Seymour) Circ Group Reserve4 hslequip HSL: Equipment 2 HSL (Seymour) Circ Group Reserve4 hslresreg HSL: Reserves (regular) 3 HSL (Seymour) Circ Group Reserve5 hsldvd HSL: DVDs 1 HSL (Seymour) Circ Group Reserve5 hslequip HSL: Equipment 9 HSL (Seymour) Circ Group Reserve5 hslmain HSL: Main Stacks 6 HSL (Seymour) Circ Group Reserve5 hslnewbks HSL: New Book Shelves 2 HSL (Seymour) Circ Group Reserve5 hslnprint HSL: Non-print 2 HSL (Seymour) Circ Group ReserveN deleted deleted item 1 HSL (Seymour) Circ Group Score1 hslmain HSL: Main Stacks 1 HSL (Seymour) Circ Group Video1 hsldvd HSL: DVDs 37 HSL (Seymour) Circ Group Video2 hslvideo HSL: Videos (VHS) 1 SMC (Science) Circ Group Book1 greenoaks Green Oaks 6 SMC (Science) Circ Group Book1 smcdewey SMC: Deweys 3 SMC (Science) Circ Group Book1 smcenvs SMC: ENVS 1 SMC (Science) Circ Group Book1 smcmain SMC: Main stacks 95 SMC (Science) Circ Group Book1 smcnewbks SMC: New Book Shelf 2 SMC (Science) Circ Group Reserve2 deleted deleted item 14 SMC (Science) Circ Group Reserve2 smcrefer SMC: Reference 3 SMC (Science) Circ Group Reserve2 smcresreg SMC: Reserves (regular) 19 SMC (Science) Circ Group Reserve3 smcequip SMC: Equipment 1 SMC (Science) Circ Group Reserve3 smcresfile SMC: Reserves (file) 2 SMC (Science) Circ Group Reserve4 smcequip SMC: Equipment 16 SMC (Science) Circ Group Reserve4 smcresfile SMC: Reserves (file) 1

[csaundrs]

The first 2 issues should be corrected now.

[gibsonjc]

I confirm 1 and 2 are fixed, thanks Chris!

@patrickzurek I did some additional testing on when I'm getting the datatables error:

KNXdb on devel, dates Jan. 1-15, 2016: error when I try to sort on the "Charges" column. All the other columns will re-sort just fine now unlike yesterday.

DPUdb on devel, dates Jan. 1-15, 2016: error when I try to sort on the "Charges" OR the "Item type" table. All other columns re-sort just fine. (I note there are a few entries with blank "Item type" lines, but no entries are blank in "Charges".)

BRAdb on devel, dates Jan. 1-15, 2016: error when I try to sort on the "Charges" column. All the other columns will re-sort just fine.

SXUdb on devel, dates Jan. 24-31, 2016: error when I try to sort on the "Charges" column. All the other columns will re-sort just fine.

[patrickzurek]

Ah.. this problem is related to the fix I pushed to prod last week. I can't find a github issue to reference, I think we didn't create one for it, it was relayed through email. Here's the issue that the change was supposed to fix:

I just noticed that the sorting of the results tables may be taking upper case vs. lower case into account. When I sort these results by “Item Permanent Location”, the location “Dean” comes before “archives”. Pat, is that a setting that we might be able to adjust to ignore case when sorting alphabetically? (I wasn’t sure if this was what was going on or not so didn’t want to make an Issue prematurely.)

The problem is my 'fix' failed to account for the fact that it will at some point be sorting non-string data (this was a pretty dumb mistake for me to make). I'm working on fixing it now. The point of this update is just to make you aware that the problem you're reporting in this particular issue is present now in production and will be triggered when a user attempts to sort a column in any report that contains non-string data in that column (dates & integers pretty much), and it will also automatically manifest itself when the report's first column contains non-string data on table load (because the system initially sorts the first column before display).

[patrickzurek]

Sorry for the extra comment. I forgot to mention that I just pulled the offending change from production until I can figure out a more appropriate way to sort columns (with strings!) and ignore the case of the string. Columns will sort without error once again, they've just reverted to their old behavior.

[patrickzurek]

I believe I have fixed the problem and pushed the change to production. However, I couldn't locate the original email from gibsonjc that contained the school used to replicate the data that made 'the location “Dean” comes before “archives”' so I couldn't verify that the ignore-case in alphabetic sorting was still working, but I see no reason why it shouldn't be. If someone wants to verify it tomorrow feel free.

[gibsonjc]

I retested Circulation Stat 7: Charges by Circ Hap Loc Item Type and Item Loc today and it appears to be working correctly. @patrickzurek please move this to production as your convenience.

[patrickzurek]

I want to look into a couple things before I move this report. I had some changes pending between dev and prod and I lost track of where I was with that. Basically I just want to take a longer look at some of the view code before I move this report if that's ok.

[dmcmpbll]

Sounds good- we can also re-compare it back with the annual stats Server SQL that Gordon gave us if you want.

[gibsonjc]

I want to look into a couple things before I move this report. I had some changes pending between dev and prod and I lost track of where I was with that. Basically I just want to take a longer look at some of the view code before I move this report if that's ok.

Yeah, I was concerned about this report in particular being caught up in the data sorting work happening around the same time. We can do specific additional testing on sorting of this and other reports if that would be helpful to make sure that all is well. If there are still problems with general sorting, we should decouple them from this report's Issue and make a new Issue.

[patrickzurek]

Sure, I'll follow up with more detail after I finish the higher priority Select system stuff.