Using Service Manager Queries

Post date: Jun 27, 2011 4:1:38 PM

SMCP has a feature called 'SM Query', which is a kind of streamlined custom report creator.  It doesn't have nearly all the features of a full blown report writer, but it allows users to get quick lists of selected information out of their SMCP data, and it has a couple of integrated features a report writer can't offer.  These lists can appear on the screen, with some limited formatting options, OR the function can be used to create a 'spreadsheet' file (actually, a comma-delimited file) that can be downloaded to the local computer.

To use the SM Query function, you need to build an SQL 'query'.  (SQL, or 'Structured Query Language' is a pretty universal format for getting information in different ways out of relational databases like the one SMCP uses.)  You may have people in your office who know SQL, and they can create their own queries, but we may also be able to help.  Queries are structured 'questions' you might ask out of your company's data, like 'Give me a list of all the open invoices over 5,000 dollars', or 'Print a list of all my stock inventory items', or 'Show me all the orders in my system with the phrase 'fire door' in the items'.

To use the function, enter a query title in the 'Title' box.  You can choose from some limited formatting options (changing the font size, turning borders on and off, choosing rows to print with alternating background colors, or choosing a file instead of on-screen results.)  And finally, in the large box at the bottom, you can enter your query.  As a convenience, we are saving queries people have built and making the list of them available for users to try.  There is a link at the top of the 'SM Query'' page to 'List pre-built queries'.  We will continue to update this list as people contribute or request them.

The Query function cannot do subtotaling, or multiple groupings in its listings, like a typical custom report writer.  But what it CAN do is create actual dynamic links to other information (something report writers CAN'T do).  So, for example, if you want a list of invoices with a particular item on them, the query can be designed to show the invoice number as a hyperlink - when you click that link, it can display the actual invoice.  Or it can have a hyperlink to the 'View Order Information' function - right on the query list.  You'll find samples in the 'Pre-built queries' and help from people here at AIRO Tech.

It also knows 'who' you are - so a query can be designed, for example, to show sales figures - but the same query, run by a different user, can be designed to show that person's sales figures only, without changing the query for each user.

Finally, for people who want to experiment and build their own queries, there is a link at the top of the page to 'Display Data Definitions', which gives an up-to-date chart of all the tables and fields currently in the SMCP database.  As always, feel free to contact us for help and suggestions.