reports reports reports the easy the annoying and the ugly n.
Download
Skip this Video
Download Presentation
Reports! Reports! Reports! the easy, the annoying, and the ugly

Loading in 2 Seconds...

play fullscreen
1 / 44

Reports! Reports! Reports! the easy, the annoying, and the ugly - PowerPoint PPT Presentation


  • 162 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Reports! Reports! Reports! the easy, the annoying, and the ugly' - vivi


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
reports reports reports the easy the annoying and the ugly

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

types of reports
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

what do you need why do you need it
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)
scheduling reports print options
Scheduling ReportsPrint options
  • Transaction statistics – no formatting options
  • List reports formatting – standard, pipe delimited, XML
finished report options
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
count reports book count
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
transaction statistics table style current holds
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
current holds formatted
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)
current holds formatted in excel
Current Holds (formatted) in Excel
  • Copy from Finished report & past into Excel
  • Excel places data into only one column
  • Excel does have multiple rows
transaction statistics table style current holds1
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)
current holds unformatted in excel
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
current holds excel transpose
Current Holds (Excel transpose)
  • Paste / Paste Special / Transpose
    • (pasted below the existing data)
transaction statistics table style circulation statistics
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 )

circulation statistics
Circulation statistics
  • Finished report: Results & Unformatted
circulation statistics selections
Circulation statistics & selections
  • Use the Gadget to enter the call number ranges
  • Save selection list as text file to reuse
circulation stats unformatted in excel
Circulation stats (unformatted) in Excel
  • Copy from Finished report & pasted into Excel
    • Excel makes the "wrong" assumptions about the data
circulation stats unformatted in excel1
Circulation stats (unformatted) in Excel
  • Copy from Finished report & past into Excel
    • before pasting, column for call# range set to: Text Format
list reports pipe delimited video pick list
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
fixed format manager wizard
Fixed Format Manager wizard
  • the Log tracks the file name (.pipe files)
  • usually opens in MS Word
list reports pipe delimited video pick list1
List reports -- pipe-delimitedVideo Pick List
  • Ready to copy/paste into Excel
    • Unformatted
list reports pipe delimited video pick list2
List reports -- pipe-delimitedVideo Pick List
  • Ready to sort by Call number
  • Labels precede most data
  • Note: the quasi-markup structure
list reports xml files library budget reserve funds
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
report session properties
Report Session / Properties
  • View XML Reports – excel.exe
library budget reserve funds standard formatted report for draft review
Library Budget -- Reserve FundsStandard Formatted report for draft review
  • Extended info "Note:" displays on multiple lines
library budget reserve funds xml xsl stylesheet into excel
Library Budget -- Reserve FundsXML /XSL (stylesheet) into Excel
  • Multiple "Notes:" make multiple Excel rows
library budget reserve funds xml xsl stylesheet into excel1
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
library budget reserve funds xml xsl stylesheet into excel2
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
list reports paragraph style late govtdoc receipts
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
ms word processing find replace
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"
processing steps phase 1 ms word
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
list reports paragraph style late govtdoc receipts2
List reports -- paragraph styleLate GovtDoc receipts
  • Result: Each record is now one line long

(with tabs between most fields)

    • SaveAs text file (.txt)
processing steps phase 2 excel import step 1 2
Processing stepsPhase 2 -- Excel import (step 1 & 2)
  • Open/Import file into Excel
  • Process with the Text Import Wizard
processing steps phase 2 excel import step 3
Processing stepsPhase 2 -- Excel import (Step 3)
  • Excel makes the "wrong" assumptions about the data

Caution: bad for

numerical text

list reports paragraph style late govtdoc receipts3
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
list reports paragraph style late govtdoc receipts legacy report
List reports -- paragraph styleLate GovtDocreceipts (Legacy report)
  • Formatted to fit onto standard sheet of paper

page headers repeat

multi-line problem

ms word processing find replace1
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"
list reports paragraph style late govtdoc receipts legacy report1
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
late govtdoc receipts legacy report
Late GovtDoc receipts (Legacy report)
  • Result: Columns display mixed data

Sort & Shift columns until data "stacks" correctly

convert text to columns wizard
Convert Text to Columns Wizard
  • Highlight column & Convert
excel formulas subtotaling summing circulation stats
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)
advanced excel formulas using if true then else
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, "" ) )

reports reports reports thank you

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

reports reports reports presentation revisions originally presented nov 14 2013
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))

ad