Reports reports reports the easy the annoying and the ugly
Download
1 / 44

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


  • 145 Views
  • Uploaded on
  • Presentation posted in: General

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

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.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 BRenew ItemRenew 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 wizardthe 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


  • List reports paragraph style late govtdoc receipts1
    List reports -- paragraph styleLate GovtDocreceipts

    • Unformatted Result

    line wrap


    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
  • Login