The Fields tab is where the actual report is built. This tab allows you to define which fields from your Data Source that will be included in your report and any grouping, sorting, and totaling that needs to be performed. There are a multitude of options that are available to users from this tab. Listed below is a brief description of the properties available on the Fields tab.
|Field Selection||Select fields from the drop-down menu or use the Quick Add button|
|Description||Sets the title to be used for the column.|
|Sort||Sorts in ascending order - numerics from 0 to 10 numerically, strings from A to Z alphabetically, and datetimes oldest to newest chronologically.|
|VG||Visually Groups your data based on the selected Field.|
|A||Arithmetic – Performs basic calculations (+, -, *, %) and concatenation of text.|
|Function||Displays functions available based on the type of data being accessed.|
|Format||Displays the formats available to be displayed based on the data type.|
|Other buttons||,,,,||Enables you to delete, insert, or move fields as well as providing advanced options for the selected field.|
|Records||Sets the number of records to display. Note: Adding a form to the report will set this to 1. This is to facilitate previewing the form. Setting this higher will duplicate the form for as many records as you specify.|
|Add Sub-total||Adds a sub-total to the report under each numeric column|
|Add Side-total||Adds a side-total to the report beside each numeric row|
|Quick Add||Lets you quickly add multiple fields to the report at one time|
|Add Pivot||Displays the pivot controls. Learn more about pivots.|
|Continue to Summary||Continues to the next tab in the report design process, the Summary tab.|
Next, we will explain a bit more in-depth about each of the controls listed above.
The Field dropdown menu is a list of the available fields in the table(s)/view(s) that are selected on the Data Sources tab. When working with multiple tables/views, the fields are categorized according to which Data Source they come from. The entries in the list that are in Bold are the table/datasource names. If the ShowDatabaseTypes setting is enabled, you could also see the data type next to the field name.
Automatically inputs the name of the associated Field that you selected. You have the option to change the description and it will appear as the new title when displayed. You may define column groups here instead of on the Advanced Field Options by placing the name of the column group and the at symbol (@) after your field name, for example "Name@Group". You can decide how wide the control is with the DescriptionWidth property.
The following table explains the Sort, Group, and VG Check Box features.
|Sort check box||Check this box if you want the table/view to be sorted in ascending order by the field selected in the Field dropdown menu to the left. You can also sort in descending order in the advanced properties.|
|VG check box||When checked, this option will cause the field that it is checked for to be separated into subheadings. This can better allow report viewers to easily see related data. Visual group behavior is controlled by the style tab.|
|Arithmetic||Arithmetic. Performs basic calculations (+, -, x, %) and concatenation of text.|
The , , and buttons apply to the row that they are displayed in and allow you the flexibility to re-organize your data without creating a whole new report.
Below is a table that describes the function buttons of the Fields tab.
|Delete button||Click this button to delete the row the button is on.|
|Insert Row button (above)||Click this button to insert a row above the row the button is on.|
|Insert Row button (below)||Click this button to insert a row below the row the button is on.|
|Move||Allows user to move a row up or down in the list|
|Advanced Properties||View advanced properties for that row.|
Functions allow you to perform calculations on the data and can be chosen from the dropdown menu based on the data type of the selected Field. Here is an article on SQL Functions
Note: When calculating a field containing an expression, you must use a function aggregate if you want to use a calculated field in a chart. Click here for more details.
Text (String) Functions Available
Date/Time Functions Available
Numeric Functions Available
|. . .||No function used|
|Average||Average the values in a column|
|Count||Counts the number of values in a column|
|Count Distinct||Counts the number of distinct values in a column|
|Maximum||Takes Maximum value of a field|
|Minimum||Takes Minimum value of a field|
|Sum||Sums the values in a column|
|Sum Distinct||Sums the rows with distinct values in a column.|
|Group||Groups field values together for aggregating|
|Group(Day)||23; day of month|
|Group(Day of Week)||Sun or Mon|
|Group(Year & Month)||2012 -07|
|Group(Year & MN)||2012 - Jul|
|Group(Week)||Jul 01 – Jul 07 (Sunday to Saturday of Week)|
|Group(Year & Quarter)||2012–Q3|
|Days Old||342 (Number of Days from Today’s Date)|
|Sum(Days Old)||782 (Sums the Number of Days from Todays Date)|
The Format dropdown menu is a list of formats for the entries of the field selected in the Field dropdown menu directly to the left to appear in. Depending on the Field data type and the function, you will have differing options for formatting. The main two formats used will be available for all numbers and dates.
Numeric Formats Available
|. . .||Displays the number as it is stored in the database||500|
|0,000||Displays as a whole number||500|
|0,000.00||Displays with two decimal places||$500.00|
|$0.00||Displays as currency with two decimal places||$500.00|
|$/100||Displays the Number / 100 In currency format.||$5.00|
|0.0||Displays with one decimal place||500.0|
|0.00||Displays with two decimal places||500.00|
|0.000||Displays with three decimal places||500.000|
|0%||Displays the number as a percentage||50000%|
|% of Group (with rounding)||Same as % of Group but rounds to closest tenth (ex. 1.11547% would be 1.1%).||100.0% (only one selected value, 500)|
|% of Group||Calculates the percentage of the row value to the entire group of values.||100%|
|Gauge||Shows value in a Linear Gauge instead of a numerical value. This value is fixed between 0 and 1, similar to the percentage formatter.|
|Gauge (variable)||Shows values in a Linear Gauge which changes range based on subsequent values. The formatted field supplies the value to be gauged, the next two numerical fields establish (respectively) the lower and upper boundaries of the gauge. This gauge only operates in a range based on significant digits, so 0 to 100, 10 to 10,000, 100 to 10,000, etc.|
|Dash Gauge||Shows values in a Radial Gauge style with a range based on the higher number of significant digits - so a value of 1,043 will produce a range from 0-10,000, a value of 10,430 will produce a range of 0 to 100,000, etc.|
Date/Time Formats Available
|. . .||Displays the date as it exists in the database||7/4/2012 12:00:00 AM|
|Short Date||Displays date using the mm/dd/yyyy format||7/4/2012|
|Long Date||Displays the day of the week, month, numeric day, and the year||Wednesday, July 04, 2012|
|Short Time||Displays time as hh:mm AM/PM||12:00 AM|
|Long Time||Displays time as hh:mm:ss AM/PM||12:00:00 AM|
|Full(short)||Displays the Long Date format, followed by the Short Time format||Wednesday, July 04, 2012 4:34 PM|
|Full(long)||Displays the Long Date format, followed by the Long Time format||Wednesday, July 04, 2012 4:34:52 PM|
|D&T (short)||Displays the Short Date format, followed by the Short Time format||7/4/2012 4:34 PM|
|D&T (long)||Displays the Short Date format, followed by the Long Time format||7/4/2012 4:34:52 PM|
Quick Add lets you add multiple fields to the report at one time by selecting from a list.
To access the Advanced Field Settings, click on the Gear icon on the selected Field.
For more information: http://wiki.izenda.us/FAQ/advanced-field-settings
Add Pivot lets you generate a pivot attached to the right side of the tabular report. This pivot is always on the right side of the fields tab – nothing is further to the right of the pivot. You may add one and only one pivot on the fields tab, meaning that you may have one and only one pivot per report.
The first thing you must do is select the pivot columns. Values in this field will be used to generate the main columns in your pivot. You can thing of this as rotating an entire field 90 degrees, from vertical to horizontal.
Left: Unpivoted tabular data by country by year. Right: Data by country, pivoted by year
You must then select at least one column to calculate cell values. These are the values which will be displayed inside of the pivot. You may select more than one column to calculate cell values – each of these will be an independent sub-column generated for each value of your pivot column header.
Multiple columns being displayed in a pivot per year (per pivot column value)
While you may only have one pivot, you can graphically break this pivot up by using visual groups. This will also impact the calculation of sub and side totals.
A pivot being divided by visual grouping
Here are some additional resources that admins can use to increase readability and more precisely define what data users are allowed to view.
Last edited by ericpfeifer-izenda, 2016-07-13 18:01:39