Custom Reports with Excel Power Query

August 15, 2018 by Beth Forte

Power Query is a new option for your repertoire of custom reporting tools.  You can now use Excel and Power Query to create custom reports for data stored in the CoreRelate database.  By using the built-in Power Query functions (Excel 2013 and above) or the Power Query add-in (for Excel 2010 and lower), you can connect to your CoreRelate database and retrieve specific information for use in custom reports.

For example, you may need a report that shows the dates for a specific group of activities for all matters where the activity type has been scheduled, along with the organizer and recipients.  This data is currently available in the Docket Enterprise Schedule Viewer, of course.  However, the twist might be that you need the activity types to be the columns for this report, not the rows.  A similar example is the ability to display matter data custom fields as columns in a report instead of rows.

Enter Power Query.

Without in-depth knowledge of the CoreRelate database table structure, all you need to know is which of the many built-in views has the data you want. (One or multiple views may be needed.)  Once the data is retrieved into Power Query/Excel, it can further be modified, merged, and pivoted to create the report that meets your needs.

Excel Power Query Editor

The Excel workbook containing the query can be saved to your firm’s document management system or file share for use on a continuing basis.  When the file is opened, the query can be updated to pull the most up-to-date information.

To protect your database from accidental data updates, we recommend that you use a read-only SQL Server user account to run your report queries.

The above examples are simple queries that you can create on your own.  However, BEC Legal’s Project Managers and Developers are available to assist with the creation of more complex queries or custom reports.

Beth ForteBeth Forte
Project Manager and Application Specialist

Published: August 15, 2018