1 / 44

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

Reports! Reports! Reports! the easy, the annoying, and the ugly. Amelia C. VanGundy The University of Virginia's College at Wise acv6d@uvawise.edu http://people.uvawise.edu/acv6d/ Virginia SirsiDynix Libraries Users Group Randolph-Macon College Nov. 14, 2013. Types of Reports. Counts

vivi
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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))

More Related