Reports! Reports! Reports! the easy, the annoying, and the ugly

1. Reports! Reports! Reports!the easy, the annoying, and the ugly Amelia C. VanGundyThe University of Virginia's College at Wiseacv6d@uvawise.edu http://people.uvawise.edu/acv6d/ Virginia SirsiDynix Libraries Users GroupRandolph-Macon College Nov. 14, 2013

2. Types of Reports • Counts • A single number • Lists -- standard table style Copy & paste into Excel • Transactions statistics – standard table style Copy & paste into Excel • Lists – pipe delimited table Copy & paste into Excel • Lists -- XML / XSL "table" Opens directly into Excel • Lists -- paragraph style Requires word processing before open/import into Excel

3. What do you need?Why do you need it? • Snapshot vs Activity • Grand total vs Categorized • Template vs On-the-fly • Selection options vs Print options • Know what you want In (report data) • Know what you want Out (Excel data)

4. Scheduling ReportsPrint options • Transaction statistics – no formatting options • List reports formatting – standard, pipe delimited, XML

5. Finished report options • View: Log and/or Result • Format: Formatted (checked) or Unformatted (unchecked) Note: view XML output by selecting the .xslstylesheet • For standard formatted reports: • Change the "size" of the page to view a table on "one" page

6. Count ReportsBook count • Purpose: count of book vols. in Archive Coll. • Result: a single number -- part of the report Log • report: Bibliographic / Count item numbers • Selections: by itemtype & by home location • Report Log • 1365 item record(s) selected

7. Transaction statistics – table styleCurrent Holds • Purpose: number of active holds • Result: table with column/row selections • report: Circulation / Current hold statistics • Selections: by patron type & by item type

8. Current Holds -- formatted • Finished report: Results & Formatted • Can not easily copy/paste into Excel as a table • Hidden formatting symbols turned-on for display (blank spaces, paragraph markers)

9. Current Holds (formatted) in Excel • Copy from Finished report & past into Excel • Excel places data into only one column • Excel does have multiple rows

10. Transaction statistics – table styleCurrent Holds • Finished report: Results & Unformatted • Easy to copy/paste into Excel as a table • Hidden formatting symbols turned-on for display (tabs, paragraph markers)

11. Current Holds (unformatted) in Excel • Copy from Finished report & past into Excel • Excel has multiple columns / rows • Column / row headers often need to be relabeled

12. Current Holds (Excel transpose) • Paste / Paste Special / Transpose • (pasted below the existing data)

13. Transaction statistics -- table styleCirculation statistics • Purpose: yearly circulation statistics by call# • Result: table with column /row selections • Example: Admin / Transaction report • transaction commands: Charge Item Part B Renew Item Renew User Part B • Selections: by call# range & by patron type ( Note: Make a copy of the report template and change the ownership from ADMIN to CIRC )

14. Circulation statistics • Finished report: Results & Unformatted

15. Circulation statistics & selections • Use the Gadget to enter the call number ranges • Save selection list as text file to reuse

16. Circulation stats (unformatted) in Excel • Copy from Finished report & pasted into Excel • Excel makes the "wrong" assumptions about the data

17. Circulation stats (unformatted) in Excel • Copy from Finished report & past into Excel • before pasting, column for call# range set to: Text Format

18. List reports -- pipe-delimitedVideo Pick List • Purpose: Video pick list • Result: List with bibliographical data as fields • Print option: pipe delimited • View unformatted & Copy / paste into Excel • Results also available thru Fixed Format Manager wizard the Log tracks the file name • Example: Bibliographic / Inventory by Item • Selections: by Item1 category & date cataloged

19. Fixed Format Manager wizard • the Log tracks the file name (.pipe files) • usually opens in MS Word

20. List reports -- pipe-delimitedVideo Pick List • Ready to copy/paste into Excel • Unformatted

21. List reports -- pipe-delimitedVideo Pick List • Ready to sort by Call number • Labels precede most data • Note: the quasi-markup structure

22. List reports -- XML filesLibrary Budget -- Reserve Funds • Purpose: Fund account info -- Reserve funds • Result: Table with Fund info • Displays XML / XSL tags • Print option : XSL (stylesheet) • Results do not "display" with the Log • Session / Properties: View XML Reports -- excel.exe • Example: Acquisitions / List Funds report • Selections: by FY & by Fund Levels

23. Report Session / Properties • View XML Reports – excel.exe

24. Library Budget -- Reserve FundsStandard Formatted report for draft review • Extended info "Note:" displays on multiple lines

25. Library Budget -- Reserve FundsXML /XSL (stylesheet) into Excel • Multiple "Notes:" make multiple Excel rows

26. Library Budget -- Reserve Funds XML /XSL (stylesheet) into Excel • Better output (does not print "Note" info): no multiple lines • The "labels" correctly display as column headers

27. Library Budget -- Reserve FundsXML /XSL (stylesheet) into Excel • More formatting in Excel • Hide unwanted columns • Format fund amount columns to display as Currency • Ready to Total

28. List reports -- paragraph styleLate GovtDoc receipts • Purpose: GovtDoc serials with late predictions • Result: List with bibliographical data as fields • Print option: none available (Prefer using reports that have XML/XSL output) • View unformatted & open in MS Word • Process/save in MS Word (as text) & import into Excel • Session / Properties: View Reports -- winword.exe • Example: Serial / Prediction as Late • Selections: by category1 & by date

29. List reports -- paragraph styleLate GovtDocreceipts • Unformatted Result line wrap

30. MS Word processingFind / Replace • Symbols ^p (paragraph mark: ¶) ^t (tab: ) ^l (new line:  ) • Rules of thumb (unformatted results) • Page header only at the beginning of the "page" • Manually delete page header • One paragraph mark at the end of a line • Two paragraph marks between records • Dollar sign & Less than sign before the "label" • Colon & number/letter & Greater than sign between "label" &"data"

31. Processing stepsPhase 1 -- MS Word • Convert all paragraph marks to new line marks • FindAll: ^p → Replace: ^l • Restore the paragraph mark between records • FindAll: ^l^l → Replace: ^p • Convert the new line marks to tabs • FindAll: ^l → Replace: ^t • Convert the "beginning of label" marks to tabs • FindAll: $< → Replace: ^t • Convert the "ending of label" marks to tabs • FindAll: :3> → Replace: ^t

32. List reports -- paragraph styleLate GovtDoc receipts • Result: Each record is now one line long (with tabs between most fields) • SaveAs text file (.txt)

33. Processing stepsPhase 2 -- Excel import (step 1 & 2) • Open/Import file into Excel • Process with the Text Import Wizard

34. Processing stepsPhase 2 -- Excel import (Step 3) • Excel makes the "wrong" assumptions about the data Caution: bad for numerical text

35. List reports -- paragraph styleLate GovtDocreceipts • More formatting in Excel • Create column headers (labels preceded most data) • Determine if columns with combined “labels”/”data” need to be separated (Text-to-columns) • Hide unwanted columns • Save as Excel file ( .xls / .xlsx ) • Ready to Print

36. List reports -- paragraph styleLate GovtDocreceipts (Legacy report) • Formatted to fit onto standard sheet of paper page headers repeat multi-line problem

37. MS Word processingFind / Replace • Symbols ^p (paragraph mark: ¶) ^t (tab: ) ^l (new line:  ) • Rules of thumb (formatted results) • Page header repeated at the beginning of a "page" • Usually easier to manually delete additional page headers • One paragraph mark at the end of a line • Two paragraph marks between records • Colon between "label" and "data"

38. List reports -- paragraph styleLate GovtDoc receipts (Legacy report) • Result: Each record is now one line long • with tabs between most fields • includes unexpected tab in the title

39. Late GovtDoc receipts (Legacy report) • Result: Columns display mixed data Sort & Shift columns until data "stacks" correctly

40. Convert Text to Columns Wizard • Highlight column & Convert

41. Excel formulasSubtotaling/Summing -- Circulation stats • Sum icon • Highlight columns and click AutoSum icon • Sum function: =SUM(D15:E15) • Highlight adjacent cells (left-click & drag) • Sum function: =SUM(C15, F15) • Highlight specific cells (ctrl-left-click each cell)

42. Advanced Excel formulasUsing: =IF(“true”, then, else) • Summing FACULTYand FACULTYADJ (AutoSum Icon) • Summing CVCSTAFF and LIBRSTAFF (use "fill handle") =IF( ISNUMBER(D15)=TRUE, SUM(D15,H15), "" ) • Combining Call# ranges (use "fill handle") =IF( LEFT(A16,1)="-", CONCATENATE(A15,A16), IF( ISBLANK(A16)=TRUE, A15, "" ) )

43. Reports! Reports! Reports!Thank you -- Amelia C. VanGundy The University of Virginia's College at Wiseacv6d@uvawise.edu http://people.uvawise.edu/acv6d/ Virginia SirsiDynix Libraries Users GroupRandolph-Macon College Nov. 14, 2013

44. Reports! Reports! Reports! Presentation revisions Originally presented Nov. 14, 2013 • New slides: • Slide 23) Report Session / View properties • Slide 40) Convert Text to Columns wizard • Revised slides • Slide 28) List reports -- paragraph style / Late GovtDoc receipts[1] Added line: (Prefer using reports that have XML/XSL output) • Slide 41) Excel formulas Formula corrected: Sum function: =SUM(D15:E15) Minor correction: Sum function: =SUM(D15:E15) -- Highlight adjacent cells (left-click & drag) Minor correction: Sum function: =SUM(C15, F15) -- Highlight specific cells (ctrl-left-click each cell))