"SQL for Beginners" How Juris Users Can Easily Extract Data from the Database

Kathy Baker, Consultant, Juris Professional Services, LexisNexis: 

SQL (Structured Query Language) is a database management language for relational databases.  SQL uses a variety of statements and clauses to get information out of databases; such as:

  • SELECT statements to select the fields of data you want to extract
  • WHERE clauses to filter data
  • ORDER BY clauses to sort data
  • GROUP BY clauses to group data together
  • Using the HAVING clause the user can filter groups of data
  • Aggregate functions (AVG, COUNT, MIN, MAX, SUM) allow the user to perform a calculation on a set of values
  • JOIN statements can be used to return information from multiple tables in the same query.

Our Software Engineers use SQL when writing custom Juris Suite reports.  SQL statements are combined into queries that extract specific fields and records based on certain selection criteria provided by the client.  The data can also be grouped and sorted using the SQL statements within the queries.  The output of those SQL queries is then designed in a report layout and when printed provides valuable statistical and financial data to the firm.  Our Developers also use SQL to code utilities that can alter and/or correct the data in the Juris database. 

However, you don’t have to be a Software Engineer or a Developer to be able to use SQL to extract information from the Juris database!   The View Tables function in Juris allows you to quickly perform SQL queries on the Juris data.  View Tables is only available to users with the “Grant System Administrator Authority” Menu Permission granted.  The Juris Professional Services team can create SQL queries for you, or you can use one of the many SQL queries provided on the Juris Support Center web site.   Search the web site for “Juris Queries”.  

The View Tables function can be found in the Setup and Manage module in Juris.   Simply copy the SQL query starting with the SELECT statement, paste it into the View Tables query window, and then click on the Exclamation icon to execute the query.  The results are displayed in the grid below the window and can be copied to Microsoft Excel for further manipulating.  Below is an example of a simple SQL Query that will select all users/timekeepers from the Employee table (the table in Juris that stores the information about each employee whether they are a user and/or a timekeeper) and displays every field in the table:

View Tables queries can be used to find:

  • “All open matters with time or expense entry disallowed”
  • “All client/matters that Require Task or Activity codes”
  • “All matters assigned to a specific Fee Schedule”
  • “Who modified, changed, or deleted a Fee Schedule OR Who changed a Fee Schedule on a matter”
  • “Who deleted a prebill”
  • “What time entries were posted AFTER a prebill was selected”
  • “Find all billed time entries that were adjusted using Today’s Date for a timekeeper”

 The list goes on and on.   There are over 100 helpful SQL queries available on the Juris Support Center web site:  

http://www.lexisnexis.com/business-of-law/support/juris