MSQuery filtering with QQube and Excel | CLEARIFY

Using MSQuery to Filter Spreadsheet Data

The purpose of the QQube Excel Tool is to hide the complexities of database technology, but there are times where the amount of data in a spreadsheet can make it unwieldy and too large to use, or to e-mail to others.

One option is to use PowerPivot, as it handles millions of rows.  However, that may not be your preferred delivery.

The QQube Excel Add-In is actually a front-end for MSQuery, which contains the actual SQL Statements that QQube auto generates behind the scenes.

So we are going to invoke MSQuery to do the filtering.

In this example we are going to filter out jobs that don't have active estimates. This can be especially helpful for contractors who have many, many jobs in their QuickBooks file, but only want to see what is active.

Example of Conventional Filtering

In our first storyboard, we are filtering using the native Excel Pivot Table filter. But irregardless of the filter - the data is still stored in the spreadsheet.  This does not reduce the data set within the spreadsheet.

Example of Filtering With MSQuery

To access MSQuery, go to the Data Tab, click on Properties, Highlight the Definition Tab, and click on Edit Query.

Show the Criteria Grid. View > Criteria.

Drag and drop the field "Job Has Active Estimates" into the Criteria Field. Type 'Yes' into the Criteria Value.

Click on File > Return Data to Microsoft Office Excel.

Click on OK to return to the spreadsheet.

Notice that there is only ONE option for Job Has Active Estimates - 'Yes'.