Queries

ARC Queries

A query is a request for transactional data presented immediately and simply, without much formatting, and is run using real-time data in ARC.  Queries are useful when more information about a financial transaction or data set is needed. They are best used for researching smaller data sets and viewing the same data set on a regular basis.  Queries can be quickly run on a recurring daily basis by adding to the Favorites link, or scheduled to run at pre-specified times.

Multiple pre-existing queries are available for users to access.   When searching for existing queries, the Basic Search offered in the Query Viewer is the easiest and most frequently used.   A list of pre-existing queries can also be accessed by entering the ARC Portal and choosing Pre-Defined Queries from the Transaction Quick Links

The Advanced Search can be used when additional search methods for desired fields (e.g. equals, does not equal, contains etc.) must be specified to broaden or narrow the query.

During the fiscal year, users may need to run a query that has not yet been created. In this instance, request that a new query be built by submitting a change request in Service Now

Please note that you will only be able to search for queries for which you have been granted access .

How to Run a Query

Queries are run through Query Viewer.

Queries Viewer enables you to: 

• Search for a query

• Preview a query in the active browser window

• Run a query and display results in a new browser window

• Print a query

• Schedule a query 

• Add a query to Favorites

To access the Query Viewer log into the ARC Portal then select Go To Arc.  From the Main Menu choose  Reporting Tools.  On the right under Query click on Query Viewer.

If you already know the query name, enter it and hit Search.  To pull up the list of available queries leave the field blank, click Search and scroll through the pages.  The first 300 Queries will appear.  Please note, all queries names begin with CU_ followed by initials for the owning module.

Alternatively, to narrow the search, click Advanced Search.  In the drill-down box next to the field Description click on Contains and enter a word you know to be in the title, such as "Vouchers", "Project" or "Cash".  Hit Search and a more specific list of Queries will appear.

A query name may bring up more than one selection.   Once you choose the query you wish to run, you may select the Run function from the following options:

• Run the query to HTML (query results are displayed in a new web browser)

• Run the query to Excel (query results are displayed in an Excel document)

• Run the query to XML (query results are displayed in an XML document)

• Schedule the query to be run within a specific date range

• Add the query to your list of Favorites 

 

Once you have made a run selection, enter the department.  To find a department number click on the magnifying icon and choose from the list.  Then click on View Results.

Download results by clicking on Excel, saving to CSV or to XML.

 

How to Run a Scheduled Query

Query Scheduler submits a query request to PeopleSoft Query, an engine that runs queries and sends data back to the user.   This is a good option for queries that have extremely large result sets.  Since it runs in “background”, the report will not time out or truncate results.

Navigate to the ARC Portal at my.columbia.edu.  Choose the Advanced ARC Reporting Link then select Reporting Tools>Query>Schedule Query.  Please note:  User must have the proper access to see Schedule Query on the Query list.  To schedule the Query, first create a Run Control. Run Control is a file that consists of a query with parameters. The Schedule Query screen allows you to reuse an existing Run Control or create a new one.  Search for an existing Run Control by Run Control ID, Description or Query Name.  To run the Query, select Run in the Schedule Query screen.   You may run the query immediately or set a future date or time for the run.  Optionally, schedule the query to run on a recurring basis by using the Recurrence drop down.   For more  information, please refer to Job Aid: Running a Scheduled Query.