Report Overview
From Informer 4 Wiki
The Report Overview page details specifics about your report. This is the page you will use to make global changes to the report. Consider this page as a report blueprint, detailing what users will see in report results provided they haven’t created a custom view. When you first create a report, the options in this page will be empty except for those values you provided in the New Report Dialog. This is now your opportunity to define how this report will select and display by default. Also note this page contains a report sample. This sample display retrieves 5 sample records which do not necessarily qualify your selection criteria and displays the fields you’ve chosen.
Contents |
Select Filter
From the report overview page, click ‘none’ beside Select Filter to enter the Selection Criteria Palette and Canvas editor. The selection criteria palette and canvas editor is your opportunity to define the select statement required to return the records you want displayed in report results.
The criteria palette is a drag and drop editor for visually creating selection criteria statements, regardless of complexity, to execute against mapped datasources. The type of condition statements available on the palette depend on the type of database chose for the report datasource.
To add a criteria block to the canvas, simply click the criteria type you need to add, or click-drag the criteria type to the desired spot in the canvas. Note you can drag criteria blocks within the canvas as well. To remove a criteria block from the canvas, click the x in the upper right hand corner of the criteria block. Use the Clear button in the Edit Criteria menu bar to clear all statements from the criteria palette. Use the Or button in the Edit Criteria menu bar to create a new set of a logical Or block.
Criteria Types
The following two conditions are available regardless of datasource type:
- Simple Condition : The most basic of condition types. A simple condition provides the ability to compare two values. For example: Person with Last Name begins with R, Company with Billing Address State exactly matches NC, Product with Cost is at least {?Prompt User}. Simple condition editors are context sensitive to the data type of the property selected in the following ways:
- The limiter option is only active when the property selected is defined as multivalued. If active, you can choose Any, Every, No, and When.
- The condition values are type-specific. Textual conditions are offered when querying text-based fields, numeric conditions for numeric fields, and date conditions for date fields. For example, alphanumeric properties are provided conditions such as: exactly matches, does not match, like, unlike, contains, etc. Numeric and Monetary values are provided conditions such as: equals, does not equal, is at most, is at least, is more than, etc. Date values are provided conditions such as: on, on or after, on or before, not on, last seven days, etc. Simple conditions also allow for three types of input for Value to evaluate against the elected property:
- Literal. This is a literal value entered at design time of the report. If selected, this portion of the select statement will not be displayed to the executing user. You can enter free text in the Literal textbox, as well as any valid runtime keywords. For a list of supported runtime keywords and syntax, please review the Runtime Keywords Appendix.
- Prompt. The user will be prompted to enter a value at runtime for this portion of the select statement. You can enter a custom prompt in the Prompt textbox, or leave empty to prompt with the description of the selected property. Selecting the Require Value checkbox ensure the user will not be able to run the report without providing a value for this portion of the select statement.
- Property. Selecting this value compares two properties for evaluating the condition.
- Compound Condition : A compound condition assembles one or more sub-conditions into a logical and (“All”), or (“At least one of”), and nor (“None”) expression.
Person with Last Name begins with R and At Least One of (Person Company Zip begins with 27 –or- City exactly matches New York).
This statement uses a compound condition to return: Everyone from the Person table with the last name R who also has at least one of the following conditions: an associated Company Zip Code beginning with 27, or a city name exactly matching New York. Compound Conditions can themselves contain any number of nested compound conditions.
If you are executing your select statement against a SQL-based datasource, you have access to the following additional condition types:
- SQL Where Clause : Free text entry for syntactically correct SQL statements.
If you are executing your select statement against a Multivalue-based datasource, you have access to the following additional condition types:
- Select / Returning : Allows you to execute queries off linked mappings and return keys of appropriate type. Informer will not allow you to execute queries off mappings which do not contain a link back to the mapping required to logically continue the flow of the criteria statement. If you need to execute a query against a table not included in the dropdown displayed within your select/returning condition block, you need to create a link. For Example:
Person with Zip Code exactly matches 21001 and All of the following from Orders returning Person – Order Invoice Amount greater than 500.
This select/returning block requires that a link from the Orders mapping to the Person mapping exists, but not a mapping from Person to Orders. The first dropdown in the select/returning block (All, At least one/none of the following from X) contains all mappings which include a link to the mapping required to logically continue the statement flow, in this case, Person. The second dropdown (returning X) contains all links *from the mapping selected in the first select* which point back to the parent file.
- TCL /ECL Block : Free text entry for syntactically correct TCL/ECL statements. If you select the Use Active Select List checkbox, your statement is expected to use the active select when executing. If you leave the box unchecked, your statement executes independently.
- GET.LIST : Executes a GET.LIST command for the specified List Name.
- Key List : Retrieves Keys specified in the comma-delimited list provided.
When you complete designing your select statement, click Save and Close to return to the Report Overview page. An English-like representation of the statement you created now appears next to the Select Filter property of your report.
Columns
From the report overview page, click ‘none’ - or the value displayed if one exists - beside Columns to enter the Edit Columns page. This page allows you to add fields to your report for display in results, including both fields from your databases as well as calculated fields evaluated on the fly as the report executes.
To add a field from your database, click the Add Fields button in the Edit Columns menu bar. This will launch the field chooser, which displays link explorer on the left, and a fields listing on the right. To add fields to your report, either double click the field row in the list, or ctrl-click and drag to select multiple fields at a time. Your field will display in place where you dropped it, and provide sample data. Exactly like the report sample on the report overview page, this sample listing does not reflect the selection criteria of your report.
Adding a new column or clicking the header of an existing column opens the Column Display Editor. The display editor contains different options for displaying the results of a particular column based on the data type defined for the field. The editor allows you to provide specific formatting for the column in question including custom CSS, alignment, and hidden or show in body.
Hiding a column will remove it from the list view, but allow for your users to still sort or group on the value. Show in Row Body will suppress the values of your field into a collapsible row beneath the standard row listing. Take special notice of the Alias value. This is a syntactically correct alias of your field name used for the calculated columns feature of the column editor.
Calculated Columns
To add a calculated column, click Add Calculations in the Edit Columns menu bar. This pops the Add Calculation dialog allowing you to create a new calculated column for your report which are evaluated when the report is run. There are two types of calculated columns you can create:
A Template Field must be a syntactically correct JavaServer Pages Standard Tag Library (JSTL) expression. Reference for JSTL is available here: http://java.sun.com/products/jsp/jstl/. Template fields are most useful for simple string substitutions, such as concatenating two fields together, for example:
${lastName}, ${firstName}
or producing a dynamic chunk of HTML, for example:
<img src='${imgUrl}'>
A Script Field must be syntactically correct JavaScript. Reference for JavaScript is available here: http://www.w3schools.com/JS. The entire syntax of the language is available (references to DOM objects and methods are not allowed). Script fields are useful for creating logical expressions, such as formulas (e.g. “price * 1.07”) or conditional HTML. An example of a script formula is:
price * 1.07
Aggregate Functions
To add an aggregate function column, click Add Aggregate in the Edit Columns menu bar. This opens the Add an Aggregate Function dialog allowing you to add the aggregate values of Count, Minimum, Maximum, Average, and Total based on any numeric, monetary, and date (Max and Min only) fields you’ve chosen for display in the report. Note that adding aggregate values will cause your report to group by fields in your report.
The Clear button in the Edit Columns menu bar will remove all columns from display. The Refresh Sample button will reevaluate the sample records selected.
Once you’ve added the columns you would like to display in the default view of your report, click Save and Close in the Edit Columns menu bar to return to the Report Overview page.
Sorting
From the report overview page, click ‘none’ - or the value displayed if one exists - beside Sorts to enter the Edit Sorts page. Here you can provide default sorting views for your report. You can sort ascending or descending by any fields you’ve chosen in the Edit Columns page, including those you’ve hidden or are showing in the row body.
Grouping
From the report overview page, click ‘none’ - or the value displayed if one exists - beside Groups to enter the Edit Groups page. Here you can provide default grouped view for your report. You can group ascending or descending by any fields you’ve chosen in the Edit Columns page, including those you’ve hidden or are showing in the row body.
In addition to applying groups, you can add aggregate calculations for any numeric, monetary, or date columns you’ve added to the report. The aggregate values you choose will display per group and as grand totals at the bottom of your report.
Normalization
From the report overview page, click ‘none’ – or the value displayed if one exists – beside Normalize to enter the Edit Normalizations page. Assigning normalizations to a report will provide a single row of data for each instance of a multivalue, and repeat single valued associations. For example, if you have a report which in one row shows a PERSON id and a multivalue list of INVOICES 5 items long, choosing to normalize those two as a set will repeat the PERSON id 5 times, while providing one row each for individual INVOICE items.
The Edit Normalization page forces you to choose from existing sets of associated multivalues as defined by your database. If you have a logical multivalue association set available in the report, but the associations are not defined on the database, you can choose to define a custom set using any of the fields in the report.
General Information
From the report overview page, click the value beside Title, Description, Datasource, Mapping, PDF Template, or Tags to enter the Edit General Information page for your report. This page allows you to edit any values you entered while filling out the New Report dialog.
It is important to be as descriptive as possible in both the description and the tags, as this will help you and others quickly find and understand the purpose of the report on the report home page. Click Save and Close to apply your changes and return to the report overview page.
