Manage Custom Report Definitions (Update or View)
|
Screen ID: Screen Title: Panel Number: |
UWRKQRY-01 Manage Custom Report Definitions (Update or View) 6929
|
|
Access this screen by selecting Tool #99 Manage My Custom Reports (sc: QUERYCTRL) for an Update version, or Tool #98 View/Run My Custom Reports (sc: QUERYRUN) for a view-only version.
Helpful Resources
CU*BASE Report Builder (Query) Overview
Database Search Assistant (Search for Tables (Files) and Columns (Fields)
Understanding Assigning Authority to Queries and Files
Learn how to complete Query-related tasks in CU*BASE.
Manage My Custom Reports (Tool #99)
There are two types of query users: the designer/builder, and the processor. The Manage My Custom Reports tool is designed to support the architect and builder with all the major functions of designing and managing queries. It is a partner to the View/Run My Custom Reports tool, which is similar but is restricted to only the functions necessary for running an existing query.
Manage My Custom Reports includes the majority of functions available in the classic query builder tool – Tool #100 CU*BASE Report Builder (Query) – but improves upon it with options to search within the list of queries by query description, creator of the query, date the query was created or last modified, and source tables (files) used in the query.
This tool allows you to be proactive, such as auditing your library of queries for any that will be impacted by an upcoming CU*BASE software release by using the “Table Name” feature to search for queries that use each of the impacted database tables (files). Files impacted in a software release can be found published online in the Database Changes Memo on the Release Summaries webpage, posted several weeks before the release date. Once the queries are identified, you can make a plan for any required maintenance to take place post-release.
Solve the mystery of who created a query and when by using the “Jump to name” or “Created by” features to find details of a specific query, or a list of all queries created by a specific person.
Perform advanced research into your query library when you need to identify if you already have a query that performs a certain task for reporting need; use the “Table Name” feature to search for queries that use tables specific to a certain topic.
-
For example, if you need reports on Safety Deposit Boxes you would search for table name SBOX to find any existing queries that use SBOX as source data. Review all results to see if any meet your current need. Even if only part of the project is fulfilled by an existing query, it could save time to copy the part you did find, make small adjustments as needed, and begin building the rest from that point.
When a query designer leaves your credit union, make sure you know which queries they created and audit the accessibility of those queries to get in front of any possible disruption in your regular query processing. Use the “Created by” and “Work with” features to find the queries that person created, and then to open them and review which authority setting was used for both the query and the output file. An authority setting of *CHANGE or *ALL on the query will support other users processing the query (no changes), but if the output file is found to have anything other than *ALL there will be issues with processing the query until the file’s authority is changed to *ALL and the file name changed. (Keep in mind, if a file name is changed it is likely necessary to search for any separate queries using the old file name as an input file – If any, those queries will also need maintenance to change to the new file name)
Search/Filter Criteria
|
Field Name |
Description |
|
Search for description containing |
Use this feature to search within the contents of a query’s description. It is a wildcard search which will find any results that contain the search term, regardless where it falls within the full description. |
|
Date created from |
Use this feature to search for queries created within a selected date range. Alternatively, to find very recent or very old queries quickly, click the column header of “Date Created”. |
|
Last changed from |
Use this feature to search for queries that had their most recent modification performed within a selected date range. There is not historical log of changes to queries. This date only represents when the single most recent change was made. Keep in mind that saving the query definition is what updates the “Last changed” date – even if nothing has specifically been changed. |
|
Jump to name |
Use this feature to navigate the list of queries by setting it to start with a specific letter or name. For example, if you type in the letter “B” the list will jump to the first query that starts with the letter “B”, but if you type the letters “BOX” the list will jump to the first query that starts with those three letters of BOX. |
|
Created by |
Use this feature to filter the list of queries by the person who created the query. The user ID seen in results represents the current owner of the query. Some user IDs will be system users (Ex. FILExx or QSYSOPR) which is normal. |
|
Table Name |
Use this feature to filter the list of queries by the table (file) names used by the query. It is a wildcard search which will find any results that contain the search term, regardless where it falls within the full name. |
Query Functions
|
Function |
Description |
|
Run Report |
Use this option to run the query as-is. No option to make changes will be available. Running a query executes all configured settings in the query definition and produces the query’s configured output (printed report, database table, or display results on-screen). If the query fails to run and you see: “Not allowed to use/replace query ________ in QUERYxx”
“Level for file _____ in ______ does not match query” |
|
Use this option to view a list of the tables used as data sources in this query. No option to make changes will be available. This is a helpful shortcut to viewing a query’s source tables without requiring that you open the full query definition. Use it when there is minimal description recorded for the query and you are trying to quickly determine the purpose of the query. |
|
|
Preview Report |
Use this option to preview the query’s results. No option to make changes will be available. This is a helpful way to check the result of a query before running it. What you see in the preview is what the query will produce once it is run based on pre-determined output settings (send to a printer, write to a database table, or display results on-screen) |
|
Use this option to copy an existing query. You will only be able to copy queries which have an authority setting of *ALL, or which you are the owner. Copying requires either assigning a different name or a different location. All custom queries should remain in the QUERYxx library location. Copying a query to a different name is helpful anytime you need to retain the original while either you or a colleague works with the new copy. It is also helpful with query management when you have template queries which do common functions (Ex. combining all active and closed loan records together). As new projects arise you will copy the template queries to save the work of re-building that function from scratch. |
|
|
Delete |
Use this option to delete a query you no longer need. One confirmation screen will appear before the deletion process is final. Deleting a query is permanent and cannot be reversed. This is helpful to keep your list of queries clean by deleting unnecessary copies or old and unused queries. If the query is automated, deleting the query will not also delete the automation settings. You need to cross-check with Report Automation (Tool 758) and delete any corresponding automation settings for your query. |
|
Use this option to assign a new name to an existing query. This is helpful if the query is automated, as renaming the query will disconnect it from the automation settings. If you continue with changing the name be sure to also perform maintenance in Report Automation (Tool 758) to remove the automation settings for the previous query name(s) and add new automation settings for the new query name(s) |
|
|
Work with |
Use this option to view and change an existing query’s configured settings. The Report Builder Main Menu will appear and you will be able to change any setting within any of the query’s configuration options. If the query is automated, changes to the query’s configurations will not automatically be updated in its automation settings. If you save changes to the query be sure to also perform maintenance in Report Automation (Tool 758) to refresh (re-save) the corresponding automation settings. |
Buttons
|
Button |
Description |
|
Refresh (F5) |
Use this button to refresh the filters and screen. |
|
Create New Report (F10) |
Use this button to access Tool #100 CU*BASE Report Builder (Query) |
View/Run My Custom Reports (Tool #98)
There are two types of query users: the designer/builder, and the processor/runner. The View/Run My Custom Reports tool has been specifically designed to support query processors’ ability to fulfill their regular tasks of running queries without the risk of queries being changed or deleted. It is a partner to the Manage My Custom Reports tool, which is similar but includes expanded functionality to create new queries and make changes to existing queries.
Database Administrators and Security Officers: If you are intent on controlling the risk of changes to or deletion of your custom queries, then this tool is the only query tool that a processor/runner should be able to access. Other query tools of Tool 100 CU*BASE Report Builder (Query) and Tool 99 Manage My Custom Reports do support change and edit functionality – If your staff person has access to any of these, they can still make changes to or delete queries.
What kind of queries can be processed via this tool? Any query that does not require manual changes can be processed using this tool. If a query requires manual changes during processing, it is not possible to be correctly run via this tool. Examples of manual changes include changing file names (when end-of-month file names are used), or to changing filter settings (when date ranges or varying codes are used).
If there is an existing query which requires manual changes to filters (usually a date range), it may be possible to improve the configurations of the query to support automatic calculations of the correct date range or correct set of codes. The strategies for this typically involve using custom fields.
If there is an existing query which requires manual changes to source files (usually an end-of-month file), it may be possible to have the system automatically use the most recent end-of-month file if you automate the query processing via Report Automation (Tool 758). Do not automate a query until you have also ensured no other manual changes are required for processing.