How To Videos: Lucity Queries with Microsoft SQL Server and Excel


What follows is not a blog, but some suggestions on using Microsoft SQL Server "Views" to query your Lucity data using Excel.   This information is intended to assist Lucity software users, and not for any nefarious purposes.

I recommend watching the videos in Full Screen view and with HD resolution.  They're not as blurry as they look on this page!!  Each of these about two minutes long, but the original actions only took 50 seconds each.  (After recording them, I decided to slow them down to make them more watchable.)

1. How to create a SQL Server "View".  

The video shows how to create a new View from the core Work Order table.  (WKORDER - see the data dictionary here.)  The video first shows the simple method of creating a view with all fields, then shows the more effective method of including only needed fields, and re-labeling them with their on-screen names.

Music: "A View to a Kill" - Duran Duran


2. How to Connect to the View with Excel

With a view created, we use Excel's ability to connect to external data sources to directly query the Lucity database.  Notice that we're using a Read-Only user - an extra layer of security that allows the views to be shared without concerns about data changes.  Also, your server and database names will be different.

Music: "Mothership Connection" - Parliament




3. Using the Connection for Excel Queries

The video starts by showing the View, which includes only the Work Order, Category, Project Number, and Total Amount.  (Plus a filter to only get Complete Work Orders with status 999.)  

Once the connection is defined, we create a simple table query (here filtering on one Project Number and doing a simple Sum) and then a Pivot Table.  Read on after the video for a Pro tip!

Music: "Think" - Aretha Franklin  (because of your freeee-dom! from reporting tools)

Pro Tip - if you're creating these spreadsheets and want to share them with people, you should set the spreadsheet to automatically refresh when it opens - and to NOT save data when it's closed... to keep the Excel file tiny.

This text is from Microsoft's support site: https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-1524175f-777a-48fc-8fc7-c8514b984440:
  • Click a cell in the external data range. 
  • On the Data tab, in the Queries & Connections group, click the arrow under Refresh All, and then click Connection Properties.
Mouse pointing to the Connection properties command on the ribbon
  • In the Connection Properties dialog box, on the Usage tab, under Refresh control, select the Refresh data when opening the file check box.
  • If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.