Reports reports reports the easy the annoying and the ugly
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

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


  • 103 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 [email protected] http://people.uvawise.edu/acv6d/ Virginia SirsiDynix Libraries Users Group Randolph-Macon College Nov. 14, 2013. Types of Reports. Counts

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 [email protected]

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 [email protected]

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


  • Login