Report Writer is one of the most important features of Issuetrak. This article will help you get to know the report writer’s terms, capabilities, and why we use this feature. This feature gives you the ability to construct queries to gather data, and then create reports to structure that data. As such it provides you with a powerful feature to pull and disperse data from your Issuetrak application. The following section will provide you with a list of terms that will be helpful as we explain this feature and how it is used.
Report Writer can be broken down into three features that complement each other:
- Queries: This is the information-gathering feature of Report Writer. You select what data you want to pull from Issuetrak. This is the prerequisite of the Reports feature. If you want to quickly grab information from Issuetrak, then you don't need to go any further than this.
- Reports: This is the data organization and presentation feature of Report Writer. You select the query that you want to turn into a report, and then decide how the data is presented. Once complete, the report will run the query it is based from and then present it to you based on how you've configured it. This is the prerequisite of the Scheduled Reports feature.
- Scheduled Reports: This feature automatically runs a report and delivers it to specific recipients. You select which report you want to run, decide how often it should be delivered, and who should receive it.
It’s best to be familiar with several terms going forward:
|Table||Basic database repository for information stored in Issuetrak. For example, the organization table contains information about all of your Organizations.|
|Field||A piece of information about a specific record in a database table, such as an address or phone number for a user.|
|Dataset||A collection of data from multiple tables that allows us to provide various fields for your reports. For example, to display a user record, we data from the Users, Organizations, Departments, and Locations tables.|
|Filter Expression||A filter that limits the data you view in a query. For example, a query can be filtered so that issues submitted last week are the only viewable data.|
|Filter Statement||The combination of Filter Expressions allows for a more complex data filter. For example, a Filter Statement would allow you to build a query to see all issues assigned to John that were submitted last week.|
|Query Builder||A tool for focusing records from a Dataset into a subset of records for reporting. The tool allows you to select fields and create filters to select a subset of data from the raw Dataset. The result is known as a Report Writer Query.|
|Report Builder||A tool for formatting a Query into a report. You can use this tool to focus the query output, using groups, sorts, and select fields for presentation. Multiple reports can be created from a single query. This tool also has the capability to count and sum data as well as create summary reports.|
|Record||The basic unit of the information displayed in your query or report. Each record displays one row. A record can be an issue, a user, an asset, a task, or any other item that you need to report on with your selected fields displayed.|
This feature allows you to build your own reports. So the first thing to do is think about what you want for your final output, what type of data needs to be included, and how you want that data displayed. This will keep you on track through the report creation process.
Consider the following to determine which data and fields to include in your report:
- Dataset – Do you need data about assets, users, issues, or something else?
- Filter Expressions/Statement – Based on the dataset you selected, which records do you want to see? Are you looking for records from a specific time frame, customer, or department?
- The Query – Do you need to see all of the fields for each record, or are you just looking for certain fields? What filter criteria do you want to use?
- The Report Builder – How do you want the results formatted? Do you need to sort or group them? Do you need to see an average of time, count the total number of records, or get a sum of the data in the fields? This helps you decide how to sort, group, and total the columns in the report.
Building a Query
A Query is a tool to select the fields you want to show in the report. A query also allows you to build a filter that shows only the data you need for the report that you want to build. Queries can be the basis for multiple reports since their output is just a list of data from the Issuetrak database. Some customers use the output of their queries as reports. We recommend that you format the data to focus attention on some aspects of the information presented.
In addition, a Query also allows you to create a list of information that combines data from more than one source in Issuetrak.
- Go to Reports in the left menu > Queries in the right context menu.
- Click New at the top of the Queries list that is displayed.
- Select the Data Set you want to query.
- Drag and drop the fields you want to display in this Query into their appropriate display order within the Selected Fields list on the right. (To remove fields from this query, drag and drop them back into the Available Fields list on the left.)
- Click Next.
- Add any Filter Expressions and Statements that apply. (These settings can be based on criteria within any Available Field in your selected Data Set.)
|Field||All Available Fields in the selected Data Set will be shown in the Field list|
|Condition||The Conditions available are based on the data type of the field selected for filtering. Most conditions are self-explanatory. A few that may not be are In and Like. The In condition allows you to type a series of values separated by a semicolon. For example, Priority In Medium;High means that all records with a Priority of Medium OR High will be returned. Note—no space is used after the semicolon. The Like condition will search for the value anywhere in the field selected. For example, Priority Like I will return records containing both the Medium and High values since both values contain the letter I.|
|Field Value||This field will only appear when a condition of =, <>, >, =, <= is selected, requiring a discriminating value. Field values can be manually entered or selected using the search (magnifying glass) icon. When selecting the search icon, a dialog box containing all the possible values for the specified field will display. Values for the specified field may be selected individually, or all values may be selected by clicking the Select All button. The CONTROL and SHIFT keys may also be used as described in Step 1. Click Update when all desired values have been selected.|
|Filter Expression||This section lists all individual filters that have been created for the query. Each individual filter is assigned a sequential letter of the alphabet for identification purposes. Filter Expressions may be modified by clicking the EDIT link associated with the expression or deleted by clicking the DELETE link.|
|Filter Statement||Filter Expressions can be related to one another using the Filter Statement. By default, the Filter Statement includes each Filter Expression with the AND operator. The Filter Statement can be modified as desired, using the operators: AND, OR and NOT. Parentheses can also be added for more advanced relations|
- Click Next.
- Enter a Name for this Query and any other details that apply.
|Query Name||A descriptive name for the query so it can be easily identified.|
|Create Report||If checked, clicking Finish will bypass the Results and direct you to the Create Report screen once you click Finish.|
|Share||To make this query available to other users who can design reports, select Share. By default, the query will be shared with everyone who can design reports.|
|Share With||This field will only appear when Share is selected. To specify specific users and/or groups in which to share the query, update the Share With field appropriately.|
|Description||A detailed description of the query to assist in describing the query details.|
- Click Save & Run.
The current results for your new Query will appear when this process is complete.
Building a Report
A Report allows you to take the data from a Query and display it in many ways.
- You can add Groups and subtotals to show the number of issues submitted by Type.
- You can use the same Query and show how many issues were assigned to each agent.
These examples would all use the same data with different groupings. You can even decide not to show the details of the data, only displaying the Footer and subtotal of each grouping. Hiding the details of a report allows your audience to focus on the subtotals, instead of having to read through all of the data.
- Go to Reports in the left menu.
- Click New at the top of the Reports list displayed.
- Select the Query you want to use for this Report.
- Drag and drop the fields you want to display in this Report into their appropriate display order within the Selected Fields list on the right then click Next. (To remove fields from this Report, drag and drop them back into the Available Fields list on the left.)
- If you want specific field values grouped together in this Report, drag and drop those values into the Selected Field list on the Select Grouping screen and click Next.
- Follow the remaining prompts until you reach the Save Report screen.
- Enter a name for this Report and any other details that apply.
- Click Save & Run.
The current results for your new Report will appear when this process is complete.
The Report Writer then allows you to create a schedule that emails reports to users of Issuetrak. You cannot schedule a query, but you can schedule a report. Scheduled Reports allow you to keep staff informed of what is going on, without your administrator being involved.
The information outlined above introduced the basics of Issuetrak’s Report Writer. We will now discuss some best practices and tips for working with queries and reports created with Report Writer.
Use Filters to Limit Your Data
Reports can use a lot of system resources when calculating, so make sure to match your needs with the appropriate data filters. For example, if you only need to see the 150 issues that were submitted this week, make sure to use an expression that specifies “This Week” instead of “This Year.” Making filter statements with more than one expression narrows down your results so you only see the required values.
Use Grouping to Deal with Duplicate Data
Datasets such as Issues/Notes or Issues/Tasks use a combination of information that displays a line in the report for each note or task added to a particular issue in a one-to-many relationship. The query below shows how the Issue Number, Submitted By, and Issue Type from the issue record are duplicated.
Use grouping in the report to limit duplication where possible. Below is a report grouped first by Issue Number, then by Submitter, and then by Issue Type. This leaves a cleaner report showing information from the unique Notes for the issue.
Include More Fields than Needed
Each query can be used to create multiple reports, so include fields in the query that may appear in different reports. For example, by including Issue Type and Priority in the query, you can create two reports: one grouped by Issue Type that doesn’t show Priority, and the other group by Priority.
Use Query and Report Descriptions
The description for a query or report is displayed on the “Select Query” and “Select Report” pages in Report Writer. This information can include a statement of purpose with a list of the fields in the query or report. Having that information makes it easier to find the query or report you need, especially if you have a lot to choose from. The below example shows the grouping, totals, and sort values, as well as what fields are included.
Change Default Format and Settings
The Settings page under Reports allows you to set different formats for the field data types. To improve output on all reports, you may want to modify the defaults. For example, decimal fields can be changed to “display as currency” fields with a dollar sign automatically added. Remember that this will change the value for each field using that data type.
We also recommend the following modifications for the settings at the bottom of the page:
Increase the maximum number of records returned by a query or report to allow more results. The default of 1,000 may cause frustration if your users are trying to create a report that needs to return more than 1,000 results.
Increase the number of records printed per page to avoid blank spaces when printing your report or query. Increase the number of records displayed per page or set it to 0 for an unlimited value. This keeps you from having to click “Next Page” when viewing results within Issuetrak.
Increase the number of records displayed per page or set it to 0 for an unlimited value. This keeps you from having to click “Next Page” when viewing results within Issuetrak.
Use Formatting on Specific Reports
If you don’t want to modify the default settings, formatting changes can be made on a specific report. On Step 5 of building a report, the formatting page allows you to make modifications for each field appearing in that report.
Any report you create can be scheduled to be delivered via email. When adding a recipient to a scheduled report, there are two options when specifying recipients: User ID or email address.
By selecting User ID, the report values are limited by a combination of user permissions, Organization, and Departmental membership. Using an email address sends an unfiltered report, showing the same values as would be seen by an administrator.
Tips and Tricks
1. Displaying Date and Time
In a report, by default, fields such as “Submitted On” only show the date. To display both date and time when building a report, on the formatting step, remove the formatting value from the specific columns. After removing the formatting on the “Submitted On” field, the time value displays.
2. Creating Summary Reports
When building a report, you can create your own summary reports by using the “Suppress Report Details” option on the formatting page. This option limits the report to show only the headers and footers for each grouped section. Make sure to turn off the option to show headers when setting your group defaults and leave the footer option selected. This results in a formatted report that shows the footer totals. This example shows the count of issues and the average number of days open for each footer.
3. View in Full-Screen Mode
When viewing the results of a query or report, this option removes the Issuetrak menu and allows the data to fill the entire browser window for less scrolling. This can be useful when you need to see extra columns or rows.
4. Exporting Data
You may elect to export your results to Excel or CSV. This allows for additional manipulation of data, such as creating graphs and charts based on your results.
5. Reporting on Inactive Types and Subtypes
Issue Types and Subtypes have the ability to be marked as inactive so that they can’t be selected on issues. In order to report on these inactive objects, you must temporarily reactivate the type or subtype long enough to create a filter in your query that includes those values. Once your query is created, you can deactivate the value. When you run the query or report, the deactivated value will be included.
6. Renaming Columns
By default, each column name in a report is the actual field name. Renaming a column on a report can give the data more meaning.
The goal of Issuetrak consulting is to make sure you get the most out of your Issuetrak implementation. Because your company is unique, your account manager can discuss which consulting options will work best for you. Or you can reach out directly to the Professional Services team.