Get Ahead With Query: Our Most Common Audit Request

More often than not, auditors request specific information regarding transactions within the system. Occasionally, their request(s) may require more than one report or less information than is in a current report. One of the most frequent requests I receive is for a report that shows detailed information on Journal Entry transactions sorted by post date. Additionally, I have found that auditors request to include who created the batch. I recommend setting up the outlined query ahead of time to cross one item off of your to-do list. In order to pull all requested information into one place, I find our Query feature works best.

I prefer query because we have full control over what information is included which eliminates unnecessary information making the reports longer than necessary. Each module has different query options based on the record types available. For this blog, we are going to be working within General Ledger. To create a transaction query, click Query from your menu. Click the New button from the action bar and set the Query type to Transaction and the Query format to Dynamic. By making the query dynamic, it will update if any new transactions meet our criteria each time it is opened.

As an overview of query, our Criteria tab is the equivalent of the Filters tab on a report. We are able to add conditional filters to determine which transactions will show up. As an example, we may only want to see transactions where the post date is between a specific range or transactions that come from the Accounts Payable Journal. Filters are selected from the Available fields list by expanding sections clicking the + symbol, highlighting your choice and clicking the Select button. Our Output tab is what defines what information is pulled for each transaction. To select an output, you expand a section, select the output and click Select on the bottom of the screen. We may choose to re order the output by using the blue arrows on the gray toolbar. On our sort tab, we may select one or more fields to sort by. Our results will show us transactions meeting the defined criteria with the output we defined. We are able to export our results directly to Excel or choose to Print the results.

Below I have a sequence of screen shots (click image to enlarge) which outline a transaction query that will show similar information to a Batch Detail Report, but allows us to sort based on the transaction postdate rather than the date the batch itself was posted. If you would like to see all transactions rather than just Journal Entries, you may choose not to include the Journal filter on the Criteria tab:



You may also choose to add additional output if you would like to see more information or exclude information your auditors do not require. I like to include the transaction number because it tells you the batch the transaction is coming from and on which line it is. I would also like to specifically mention the Amount choice on the Output tab. It is important that you are choosing “Amount” from the Account Distribution section and not the “Transaction Amount” from the Transactions section. This will prevent inaccurate results if you distribute transactions to multiple products.

I recommend saving this query for future use. You may always modify the query criteria as necessary if you would like to pull the same information but for different dates and/or Journals or use the Save As function to create a similar query.
If you would like more information on our Queries, be sure to check out our Query Guide.