Excel .XLSX Output
Queries may be output in Excel .XLSX format and assigned a sheet name and folder in the IBM i Integrated File System (IFS). When output as .XLSX, the query can translate report break summaries into Excel formulas; protect leading zeros in numeric values like account numbers, inventory codes, and postal codes; and format the detail rows as an Excel table. Excel tables enable users to sort and filter the data and are easy to transfer to Microsoft Power BI.
Extended Record Selection
Record selections in queries may reference values stored in a Db2 on i table (file). The table may be referenced in multiple queries. At run time, the query will select records based on the table’s current contents. You may also write a query so it will prompt users at run time for the name of the table.
Environment Variables
Query record selections, new fields, sorts, and other instructions may reference values stored as environment variables (EVars). With EVars, you can store values that are not in a database table and pass them to a query. The EVar feature is particularly helpful when you want to pass values (e.g., dates) to a query you’ve specified as the query to run next.
EVars can be created and updated from a simple or conditional new field calculation in a query, through the WRKIQEVAR command in the IBM i 5250 interface, and from IQ Client™. A query can process an EVar before the query, as the query reads each record, or after processing the query. You may store an EVar in any IBM i library, including QTEMP.
New Field *NOW Setting
A numeric new field calculation formula may include the system variable *NOW.
New Field *LIB Resolution
A new field calculation formula may include *LIB to include the query’s library name in the resulting value.
Email Address Validation
Queries that deliver output via email run through an email address syntax validity checking program (the CHKEMADDR command) at run time. If the program finds an address with an invalid syntax, it continues processing, delivers the query output to the valid email addresses, and adds a message to the query’s job log about the syntax errors found. You may also use this command to test an email address for structural validity.
Email Distribution Lists
Queries may use multiple email distribution lists. You have two files in the QIQE library to store email addresses you want to include (even when they don't pass the syntax checking process) or exclude. The exclude list is helpful when you're unsure which queries or distribution lists include a specific email address you no longer want to use.
Report Distribution
With the report distribution ("bursting") feature, you can add instructions to the highest report break in a query so that when the query runs, each break in the output is split into a separate file that the software can email and FTP to different addresses and destinations. Use this feature to distribute print, text, HTML, Adobe PDF, and Excel XLSX files.
FTP Logging and Security
Query job logs include FTP logging. You may use Field Substitution to set your To File and Remote System values. Passwords may be 50 characters maximum, and you can enter port, secure connection, and data protection settings to better secure file transfers.
.CSV Delimiter and Escape Character Handling
Assign your own character and numeric delimiters and escape characters to a query’s .CSV output file to overcome challenges associated with parsing values containing characters like commas and apostrophes.
|
