Work Smarter, Not Harder: Getting the Most Out of Standard Inquiries in Sage X3
One of the great features of Sage X3 is that it comes with various methods to fetch the very specific reporting information you need.
There are three types of tools you can use to create reports:
1. Requester Tool
This includes the Standard Requester, Graphical Requester and SQL Requester. All of the Requesters function by using the temporary extraction of data in a table and running a query on the table.
2. Crystal Reports
Can be used to create simple reports such as displaying lists of records from tables to complex or specialized reports that are customer or vendor facing.
Each module of Sage X3 has its own set of inquiries that allow you search and filter predefined data collections using a variety of prebuilt parameters.
In this blog post, we will show you how to use inquiries and how to get the most out of them.
One of the advantages of using inquiries over Crystal reports is that they allow you to jump to the particular transactions by way of right clicking on the Options menu of the line that interests you.
You can navigate to sub-menus for inquiries for each module of the application. Purchasing will have its own dedicated submodule, and so will Sales and so on and so forth.
Here is an example of the inquiries found in the menu for Sage X3 v12:
Please note that new inquiries are added when new functionality is released. For example, new Inquiries were released with the Shipments submodule in purchasing that came out with v7.
Most inquiries are arranged in pairs:
In the example above, found in Purchasing > Inquiries > Orders, the ‘List of orders’ will point to the document header information while the ‘Order lines’ inquiry will display line item data. Both will allow you to drill down to the detail by clicking the Options menu (dots) for each line presented.
Unless it is a graphical inquiry, like ‘Orders by period’ shown below, which prompts you for the parameters or filters prior to displaying the graphical representation, each inquiry will show in the same manner.
You will first be asked to enter the selection parameters in the header and then the lines will be displayed after clicking the Search button in the top right corner.
Please note that the default dates shown in the Start date and End date fields are managed in the code of the particular inquiry.
Below is an example of the results returned after changing the date range:
Adding the company as the filter and clicking the Search button again returns a fresh set of results:
One of the features that often is overlooked is the ability to use other available search criteria. Since these criteria are not as common as the ones in the header, they are placed in another section called ‘Criteria’ in the top right corner of your screen in v12:
Being able to filter by statistical group is one of these criteria. It is extremely useful to reduce the number of values displayed in the lines. You can easily imagine a situation where you would only want to see a subcategory of your item master and only show those purchase order lines. By simply selecting it in the list, you can now concentrate on those SKUs and see who you purchased them from for a given time period.
As an example, we can select our IT product from our product list:
After clicking OK, we are now displaying only those items from the database.
Note that these criteria can be saved and memorized using the ‘Memo’ option in the top right corner of the criteria window.
One of the other capabilities that is not used often is the ability to add/remove columns based on configurations.
Before modifying the order or adding/removing columns, you will need to know which inquiry code to change. To determine the inquiry code, hover over the inquiry link on your menu. The function code will appear. The last three letters of the function code will be the inquiry code. In our example, the function code for ‘Order lines’ is CONSCPP. Therefore, the inquiry code is CPP.
The inquiries can be modified here:
You can create as many versions of the inquiry screen as desired. All inquiries come with two standard screen versions—an ALL with most/all fields turned on, and a STD with a few already pre-selected.
Please note that the ALL screen code typically required ADMIN access and might not be available to all users.
You can decide how many lines to display when you click the Search button. After that number of lines is reached, you will need to click the Next button to display the next number of lines defined.
You can also define how many columns will be frozen when you scroll to the right. In the screenshot above, only the ‘Order’ number will remain static.
You can decide to remove a column by clearing out the numbers in the Order column. If a column hasn’t been selected yet (there is no number in the Order column, it appears after all numbered columns), all you need to do for it to be available next time you open the inquiry is to add a number in the Order column at the right place.
Finally, if you are a superuser of the system and you need to understand which screen code is used by default on an inquiry, you will need to see it in the development section:
You can create your own version of the screen and default it for all users. If no screen is selected, the system will display ALL fields defined at the screen level.
In our case the STD version is defaulted:
Please note that you can change the screen code provided that you have the ADMIN access code. You can do this at the Criteria level on the Inquiry.
The default code is STD when you open the CPP inquiry for ‘Order lines’. You can change the default screen code to ALL to display all fields.
Please note that you can also choose the Sort order.
If you need additional columns, feel free to reach out to Net at Work and we will be happy to help you make adjustments to Inquiries.