Documentation
Follow us on:
Graphs and Instruments
Reports can be configured to return results in a variety of different ways. Graphs and Instruments are often used to show the data in a more graphical format. This manual will take you through some examples and explain how to create reports that display them as various types of graphs and instruments.
Creating a Graph Report
Any report can be modified to display as a graph, but there are some changes that must be made. Let's take a look at an example. This is a report called "Open Work Orders by Type." We're going to configure this report to show us a pie chart of the different types of work orders. At the moment, the report is set up to return all of the open work orders. When we run it in an ordinary format, like HTML, we can see that it shows all of the open work orders and some information about them, including "WO Type." This image shows a portion of the output. By looking at this, we can see that a lot of the records have the value "PM" for that field, some have "CORRECTIVE," and just a few say "SAFETY."
The first thing we need to do is group the data by the WO Type field, which means that all records that have the same value in that field will be combined into one line. To add grouping, first click on the Sort&Group tab. Click the folder icon next to the table name (WORK).
Now that the records are grouped by WO Type, when we run the report again, it will look like this:
Obviously, this looks very different. There are only three rows here; one for each value of the WO Type field. These will become the three slices of the pie.
The next thing to do is to give the system a way to determine how large each slice should be. To do this, we'll need to apply a function to one of the fields. Functions include:
- SUM() - Will add the values in that group together (if numeric)
- AVG() - Will calculate an average of the grouped values (if numeric)
- MIN() - Will return the lowest value in alpha-numeric order
- MAX() - Will return the highest value in alpha-numeric order
- COUNT() - Will return the number of records that are grouped in that row
We're going to want to put a COUNT() function on the WO No (work order number) field. This will tell us how many work orders have been combined into one row and therefore, how many are marked PM, how many are marked CORRECTIVE, and how many are marked SAFETY. First, go to the Columns tab, where you will see the fields that are displayed on the report. On the right side, you'll see that each field has two icons next to it; a pencil (Edit Filed Properties) and a trashcan (Remove Field). Click on the pencil for WO No to open the properties window. Here, you can select the "Display Function." Select COUNT() from the dropdown menu and click Save.
Let's run the query again to demonstrate this.
Here, you can see that the left column says "COUNT(WO No)," instead of "WO No," indicating that it's displaying the count. It shows that there are 59 PM's, 3 SAFETY's, and 18 CORRECTIVE's.
Now that these adjustments have been made, we can set up the Graph Options. Click the arrow on the right side of the Run button and, from the resulting menu, hover over Graph and click Graph Options.
You will see the following screen:
Here, you can adjust the various settings of the graph output.
- Caption: This text will be displayed above the graph, when you run the query. By default, it will be populated with the name of the report, but can be changed.
- Sub-Caption: You can enter more details here, which will be displayed just under the Caption.
- Type: This is where you can select which kind of graph you want: Column, Pie, Doughnut, Area, Bar, and Line. Some are available in a 3D version. Let's pick Pie (3D) for this report.
- Draw Type: You can change the speed at which the data will be rendered. You can choose from Low Speed and Broadband.
- X Axis Field: This is where you choose the field by which we grouped the report: WO Type.
- X Axis Name: This will be filled in with the description of the field you chose for the X Axis Field.
- Y Axis Field: This is where you choose the field where we put the function. Choose WO No.
- Y Axis Name: This will be filled in with the description of the field you chose for the Y Axis Field.
- Image Width: Enter a numeric value between 400 and 1024 to set the width of the report.
- Image Height: Enter a numeric value between 350 and 768 to set the height of the report.
- Display Totals / %: If this is turned on, each slice of the pie will be labeled with the total percentage. Otherwise, the percentage will come up when you hover over the area with the mouse.
For this example, there are three important properties we need to set: Type, X Axis Field, and Y Axis Field. Choose WO Type for the X Axis Field, choose WO No for Y Axis Field, and choose Pie (3D) for the Type. When you're finished, click the Save button. Now, let's run the report as a graph. Click the arrow on the right side of the Run button and click Graph. Now, the report will run in the graph output.
Creating an Instrument Report
Another output option is Instrument. Instruments are similar to graphs but are designed to measure one particular value against a range of numbers that you define. To demonstrate this, we'll adapt this query to work with the Instrument output. The first thing we need to do is remove the grouping we added earlier, because we can only show one value in this format. We won't have 3 pie slices, we'll have one value measured on a scale. Go to the Sort&Group tab and click the trashcan icon next to WO Type to remove the grouping.
The Instrument Properties screen will come up. This will let you modify the way the instrument will display and what data is should use.
The properties you can adjust include:
- Type: This is where you can choose which style of instrument you would like. There are 3 types:
- Angular Gauge: A "gas gauge" style meter where a needle points to the value on a semicircle.
- Vertical LED: A bar represents the value and extends to the proper point on a vertical scale.
- Bulb: A sphere that increases in size based on the value.
- Unit: The text here will appear after the numeric value. It's used to describe what unit of measurement you're using; "%" "Dollars," "units," "WO's," etc.
- Value Field: This is the field that's being measured on the scale.
- Color Ranges: This determines what number ranges the scale will include and the colors will represent those ranges. We'll discuss this further in a moment.
- Image Width: Just like in the Graph Options screen, you can set the width of the window.
- Image Height: You can also set the height of the window
At the moment, the color ranges are set to 0-32, 33-65, and 66-100. That means, if the value is between 0 and 32, it will be in green, if it's between 33 and 65, it will be yellow, etc.
To alter these, double click on one of the colored rows. A window will come up that allows you to modify the color range:
- Name: You can set a label for the range.
- Min: Specify the lower bound of the range.
- Max: Specify the upper bound of the range.
- Color: Use the tools below to adjust the color for that range.
Click the Done button when you are ready.
From the Instrument Options screen, you can click the "+" button to add another color range or select a range and click the "-" button to remove it.
Now, let's set up our instrument. On the Instrument Options screen, choose Angular Gauge as the type. Since this report is going to show us the number of open work orders, we can change the Unit field from "%," to "WO's." Next, we'll set the Value Field to "WO (N)," which is the WO No field that has the COUNT() function on it. This will tell the report to use that field for the instrument. Lastly, we'll increase the size of the instrument, just to make it look better. Change Image Width to 400 and Image Height to 250.
Now click Save. The query is ready to run. Click the small arrow on the right side of the Run button again and select Instrument.
You can add more filters to narrow down the data that will be used, or experiment with different types of graphs and instruments.