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_at_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 .xsl
  stylesheet

• 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 Funds XML /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 GovtDoc
receipts

• 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 ll ? Replace p
• Convert the new line marks to tabs
• FindAll l ? Replace t
• Convert the "beginning of label" marks to tabs
• FindAll lt ? Replace t
• Convert the "ending of label" marks to tabs
• FindAll 3gt ? 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 GovtDoc
receipts

• 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 GovtDoc
receipts (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(D15E15)
• 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 FACULTY and 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_at_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 receipts1 Added line (Prefer
  using reports that have XML/XSL output)
• Slide 41) Excel formulas
• Formula corrected Sum function SUM(D15E15)
• Minor correction Sum function SUM(D15E15)
  --
• Highlight adjacent cells (left-click drag)
• Minor correction Sum function SUM(C15,
  F15) --
• Highlight specific cells (ctrl-left-click each
  cell))