9 Replies Latest reply on Oct 20, 2016 1:03 PM by Scott Perkins

    Reports - Want to see Transactions by Bank

    Paul LaFollette Guide

      I see in Pharos Reports the Printer Summary by Bank, but can I get a report showing the Transactions by Bank?


      We have occasion every Semester to switch certain Release Stations to use a different Bank for a couple days and the Printer Summary by Bank report helps to show how much printing took place using the different Banks... but I don't see anywhere I can get a good report showing the transactions that took place for a specific Bank.  The Pharos Administrator, System, Transactions can show the transactions specific to a Bank, but it's rather cumbersome (for creating a report) and only shows up to 1000 of the much more than 1000 transactions associated with the specified Bank.


      What would be a good method/trick to getting such a report?


      The closest I've come (thus far) is to run the "Transactions" report for the dates where the specified Bank was used, output that report as an Excel file, then modify the excel data to remove the transactions for other Banks, fix the "Amount" column to be Numbers (instead of "Numbers stored as Text"), remove " Pages" and " Page" text from the Quantity column (so that all that remains is the number).  This provides a somewhat useful report, but it doesn't have some of the useful info such as Printer, Print Group, and Job Name and doesn't show # of Color Pages, or # of Sheets (as opposed to Total Pages).  I'd really like a Transactions Report by Bank that includes the kind of information the User Print Transactions report provides.



      - Paul L.

        • Re: Reports - Want to see Transactions by Bank
          Katherine Baynton Ranger

          Hi Paul


          It would be possible to use Data Export Profiles to export this information from the database.  While it's not as straightforward as using the built-in reports, it should certainly give you the data you are after.  You can specify a date range for the data you wish to export and the resulting data file can be imported into Excel or similar for manipulation as you wish.  It would, unfortunately, give you all transactions (for the specified period) regardless of the bank, but you can use Excel to sort on the desired bank and then remove any transactions which are not relevant.


          Data Export Profiles can be defined under the System context in Administrator.  Add a Data Export Profile there - you can define which columns you wish to include in your resulting data file (and the order of those columns).  Click the 'help' option in the Actions panel in that context - at the bottom of this help page is the 'related links' option which will provide information on how to use the ExpTrans.exe tool to run the profile - this is where you specify your date range. 



          When creating the export profile, select the 'Print Transactions' option in the 'Export Records' setting (the default is 'Transactions', which gives different column options)

          When creating the export profile don't specify any Groupings (as this will give you summarized results rather than individual transactions). 

          The ExpTrans.exe tool will produce a txt file in the Pharos\Bin folder. If you select the 'comma' as the separator in the export profile, then you can rename this file to .csv - and it can be imported into Excel

          • Re: Reports - Want to see Transactions by Bank
            lpadgett Scout

            Hey Paul,


              The ExpTrans/data export profile method is a great way to collect data for monthly metrics.  It allows for a scripted solution on a schedule or by dates selected by a user.  It also gives you as much or as little data as you need. One thing I have never liked is how it drops files in my pharos/bin folder since I'd prefer not mess with that folder unless upgrading, etc.


            This is the portion of the script that runs exptrans.exe


            ::change disc so you can be local to the bin folder when making these file moves

            cd c:\<YOUR PHAROS SITE INSTALL PATH>\pharos\bin

            ::Run ExpTrans.exe with profile in Uniprint. I designate variables that I select with the start and end date so I can automate it like a rolodex.

            .\ExpTrans.exe -profile printsbymonth -start %var1% -end %var2% >nul


            :: allows user to specify file name

            set /P _metricsfile=Please enter a name for your file(example, My Metrics File):


            :: moves file from pharos\bin according to the export profile name you chose in uniprint

            move "E:\Program Files (x86)\Pharos\Bin\printsbymonth[%var1%,%var2%].txt" "%userprofile%\desktop\"%_metricsfile%".txt >nul

            cd %userprofile%\desktop\




              • Re: Reports - Want to see Transactions by Bank
                Katherine Baynton Ranger

                Loyd - you are right - a good improvement would be to allow the specification of the path for the output file.  However, as a workaround you can get the file to create in whichever directory you wish by running the command prompt from the desired directory and calling the exptrans tool using the full path to the ExpTrans exe in the Pharos\bin folder.  Eg, if you would like it to output the file in the c:\temp folder, have the command run from the c:\temp folder and run the full command:

                C:\Temp>"c:\program files (x86)\pharos\bin\exptrans.exe" -profile printsbymonth

              • Re: Reports - Want to see Transactions by Bank
                Philip Blake Wayfarer

                Any way to see transactions by Purse?

                • Re: Reports - Want to see Transactions by Bank
                  Jeff Geller Guide

                  Hi Paul,


                  Not sure if this is helpful for your original question but have a look at the below query and the results it produces.



                  USE pharos

                  SELECT Bank, [Transaction Type], COUNT(bank) AS Total

                  FROM rpt_transactions

                  GROUP BY bank,[Transaction Type]

                  ORDER BY bank, total DESC



                  BankTransaction TypeTotal
                  Default BankPrint162
                  Default BankComputer30
                  Default BankCopy8
                  Default BankCredit5
                  Default BankCustom2
                  Pharos Remote BankPrint19



                  Jeff Geller