By Thomas Schonthaler, Raffa Technology
Even though Quick Query (QQ) has been available since SL 2011, I find that it is under utilized by my clients, especially the ones who began using SL before 2011. This will be the first in a series of blogs with the goal of helping SL users take advantage of this powerful and flexible tool. Let’s start with the basics!
Quick Query is located on the navigation pane. When you select it (depending on your rights), Administration and the various module groups (e.g. Financial) are displayed. For this example, I am expanding Financial, which makes General Ledger (GL) available. Within the GL section, I am selecting Journal Transactions. The default view is then displayed. Clicking on any of the headings will cause the data to be displayed in ascending order. Clicking a second time changed the order to descending. To change the order of the columns, simply click and drag.
The default filter for Journal Transactions is Journal type. Typing “AP” will restrict QQ to transactions from the Accounts Payable module. To change the selection, type over it. To change the column, click on the “Journal Type” dropdown. Selecting Period Entered will allow you to restrict the data shown to a specific period using the format YYYYMM. This is the way dates are stored in the database, contrary to what you see displayed in most of the SL screens and reports.
Often you will want more than one filter. Clicking on the circle to the right of the Journal Type dropdown () will open the interface to Add Filters. Clicking it a second time will close it. Click “Add Filter” and what SQL users call the “Where clause” is now available. Your first choice is to accept “Where” or use the dropdown to change to “Where Not”. I am accepting the default of “Where”. I am also accepting the next default of “Journal Type”. Had I clicked on it, I would have had all the same options as shown in Figure 1. Clicking on “contains” reveals the options of:
- begins with;
- is not;
- does not contain;
- is between and includes;
- is greater than and includes;
- is less than and includes; and
- is empty.
I am selecting “is” for our example. Clicking on “enter value” and typing “AP” completes the first “Where clause”. To start a second filter, I now click on the down arrow of “Add Filter”. My options are now:
- And Not;
- Or Not; and
- And Either.
I am selecting “And”. Next click the dropdowns and change: “Batch Number” to “Transaction type”; “contains” to “is” and
“Enter a value” of “CK”. As a third filter I am adding “And” “Account” “is” “1030”. We have just created a simple check register for checking account 1030.
Working with Columns
Right clicking on any of the column headings reveals three options: Choose Columns; Rename Column; and Remove Column. In continuing to refine our check register, we note that the QQ has a default column of “Period Entered” but does not include “Period Posted”. No problem! Right click on “Period Entered” and select “Choose Columns” and scroll down until you see “Period to Post”. Click on “Period to Post”, then on the “Add>>” button, and finally on the “Move Up” button until it is in the location that you desire. For our example, I put it next to “Period Entered”.
Next, I right click on “Reference Number” and select “Rename Column” then edit it so it is “Check#”. Click “OK” and the column should display the edited version. There are several columns that do not add any real value to this QQ as a Check Register so next I’m going to right click and select “Remove Column” for Journal Type, Batch Number, Transaction Type, Account Description, Original Company ID, Subaccount Description, and Period Entered. (If some of these have meaning for your data set, do not delete.)
Saving a QQ
At this point, we should save this, especially as this is one we’ll want to use at least once a month. Click the Save icon or type Ctrl-S and give the Query a View Name and Description (Description is what you will see when reusing it in the future). I gave mine a View Name of “Ck Reg 1030,” accepted the default of “All Users” and a description of “Check Register for 1030”. Click OK to finish saving. Going forward there are two ways to open your view. If you select the QQ “Journal Transactions” and click on the dropdown icon, you’ll see it (and any other ones that have been created). In addition, it will be displayed in the QQ menu as shown in Figure 4. A filtered QQ can be deleted by opening it and clicking the “Delete” icon or typing Ctrl-D.
Exporting a QQ
All QQs can be exported to Excel by clicking the Excel icon () on the menu bar. As Figure 5 shows, there are four options: Copy to Table (one time, static data), Connection (data can be refreshed in Excel), Query Table (data can be refreshed in Excel) and Pivot Table (automatically builds a pivot table).
Note Figure 5 is the check register we built. Having added the column Period to Post, it is now available as a filter. Advanced features and Administration will be addressed in future blogs.
For more information about Microsoft Dynamics SL Customization Manager contact Thomas Schonthaler at firstname.lastname@example.org.