EfficiencySpring: Administrator's Help

Configuring Reports

EfficiencySpring ships with a reporting framework that allows for creation of list reports, crosstabulation reports, and mail merge reports. You can setup these within the Configured Reports section.

When adding a report, the first step is selecting the Section it will be based on. After that, selecting the Report Type will cause several fields to show up and/or disappear.

There are several options regarding where the report will be runnable from.

The Show on Section Home Page checkbox determines whether the report should be runnable from the hope page of the section.
The Show on Section Results Page checkbox determines whether the report is runnable after a standard search is run.
The Show On Central Reporting Page checkbox determines whether the report appears within EfficiencySpring's central list of reports.
The Skip Search Page checkbox determines whether reports triggered from the central reports screen or the section home pages are executed immediately, instead of going through the search page for search criteria entry.
The Show on Record Page checkbox determines whether the report is runnable for single records from the record view page.

The Search String field is used to specify the filtering and ordering criteria of the report, using the same querystring format as standard Data Manager section searches. For example, this Search String, run for report based off of the Section Admin section, would return all of the "Data Manager" sections, sorted by section name.

f1_1083=SQL&union=AND&sortfld_21=SECTION_NAME&reversesearch=true

The search string supports capabilities not found in the Data Manager search interfaces. For example, multiple sort fields can be specified for reports, such as:

f1_1083=SQL&union=AND&sortfld_21=SECTION_NAME,SECTION_TYPE&reversesearch=true,false

In addition, parameters can be specified for date structure elements that are sensitive to the current date, such as:

f1_32=mtd&reversesearch=false&viewby=50&union=AND

The f1_32 parameter, representing a date structure element, has been set to "mtd", which is short for Month To Date. The search string, when run, will return all of the records between the 1st of the current month, and the current date. Below is a list of date sensitive search values.

Type	Search String Format
Today	today
Yesterday	yesterday
Tomorrow	tomorrow
Month to Date	mtd
Year to Date	ytd
All records within a single month n months from now.	nmonth, such as "-2month" or "2month"
All records within a single year n years from now.	nyear, such as "-2year" or "2year"
All records within a single day n days from now	nday, such as "-2day" or "2day"
All records within a date range started or ending n days from now	ndays, such as "-90days" or "90days"

In addition, the Search String may also contain a parameter named sffilter. This parameter can contain a comma-delimited list of structure element IDs. If specified, the search screen that is displayed before running the report will limit the search fields it displays to the ones specified in the sffilter parameter. An example would be:

sffilter=2325,4545&reversesearch=false&viewby=50&union=AND

The Template Server File Path field is used to specify the location of optional Microsoft Excel templates for list and crosstabulation reports. The same field is used for specifying the location of Microsoft Word mail merge templates; this setting is mandatory for Mail Merge reports. For mail merge templates, the mail merge field designators in the template should match the database field names for the section the report is based on. The Template File Path can be a fully qualified Windows path (beginning with a drive letter) or can be a partial path with a ##domain## marker used to specify the Windows path up to the root of the site, such as:

##domain##\fileupload\templates\RSS Template.xlsx

If a template is uploaded for the report using the Uploaded File Template file upload field, then it overrides the Template Server File Path setting, replacing its value with "Template has been uploaded."

List and Crosstabulation Report Formatting Options: Excel
For Excel based output, EfficiencySpring has a standard template markup it utilizes for creating Excel reports. Examples of this markup can be found at:

/fileupload/templates/RSS Template.xlsx (Simple list report template)
/fileupload/templates/RSS Feed Template.xlsx (List report with subreports template)
/fileupload/templates/Standard Crosstab Security Log Template.xlsx (Crosstab report template)

For List reports, each Excel report template should have one tab named Master, which is where the primary output will reside. A secondary tab named RowFormats is used to define the layouts for the data rows (with two alternating styles allowed), the total rows, and for reports with groupings, the subtotal rows and group header rows. Additional tabs can specify formatting for subreports, which should be named RowFormats_<section id>. Below is a table containing each formatting markup tag, and where it belongs in the template:

##report_title##	The placeholder on the Master tab for designating where the report title should be placed.
##report_criteria##	The placeholder on the Master tab for designating where the search criteria responsible for querying the data should be placed.
##rows_start##	The placeholder on the Master tab for designating where the rendering of the report's data rows begins.

##row_start1## and ##row_end1##	The placeholders in the RowFormats tab used to designate where the layout for the first alternating row begins and ends.
##row_start2## and ##row_end2##	The placeholders in the RowFormats tab used to designate where the layout for the other alternating row begins and ends.
##total_start## and ##total_end##	The placeholders in the RowFormats tab used to designate where the layout for the total row begins and ends.
##group_header_start## and ##group_header_end##	The placeholders in the RowFormats tab used to designate where the layout for the group header rows begins and ends. This layout is used when a List report is grouped by one of the fields.
##subtotal_start## and ##subtotal_end##	The placeholders in the RowFormats tab used to designate where the layout for the subtotal rows begins and ends. This layout is used when a List report is grouped by one of the fields.
##subtitle_start## and ##subtitle_end##	The placeholders in the RowFormats_<section id> tabs used to designate where the layout for the subreport headers begins and ends.
##subfooter_start## and subfooter_end##	The placeholders in the RowFormats_<section id> tab used to designate where the layout for subreport footers begins and ends.

##fieldname##	A placeholder containing a field name that will be replaced with the value for the current result row, or in the case of a total or subtotal row, the aggregated calculated value for the field. ##fieldname## placeholders are placed in the layout placeholders above.

For Crosstabulation reports, each Excel report template should have one tab named Master, and a secondary tab named Formats, with the latter used to specify the formatting for Y field names, X field names, data cells, total calculation cells, and grouping headers. Below is a table containing each formatting markup tag, and where it belows in the template:

##report_title##	The placeholder on the Master tab for designating where the report title should be placed.
##report_criteria##	The placeholder on the Master tab for designating where the search criteria responsible for querying the data should be displayed.
##crosstab_start##	The placeholder on the Master tab for designating where the rendering of the report's crosstabulation grid begins.

##grouping_header##	The placeholder in the Formats tab used to designate the cell containing the formatting information for cells used to group crosstab reports.
##y_field_name##	The placeholder in the Formats tab used to designate the cell containing the formatting information for cells in crosstabulations that display fields along the Y axis.
##x_field_name##	The placeholder in the Formats tab used to designate the cell containing the formatting information for cells in crosstabulations that display fields along the X axis.
##data##	The placeholder in the Formats tab used to designate the cell containing the formatting information for cells in the crosstabulations that display data.
##total##	The placeholder in the Formats tab used to designate the cell containing the formatting information for cells in the crosstabulations that contain X axis or Y axis totals.

List and Crosstabulation Report Formatting Options: HTML
For HTML output, The HTML Style Sheet (for HTML output) textarea is used to modify the default formatting options of list and crosstabluation reports that are rendered in HTML.

Crosstabulation Settings
If the Report Type is set to "Crosstabulation Report", then several fields will appear. The Crossab X Axis dropdown box is used to specify the structure element whose distinct values will form the X-Axis. The Crosstab X Date Type setting is used to specify whether date values in the X Axis should be broken down into full dates, month/year combinations, or simple years (this setting is relevant only when the Cross Tab X Axis setting is a Date structure element). The "Y" settings operate in the same matter as the "X" settings.

The Crosstab Z Calculation Type dropdown can be used to specify how values in the crosstabulation are represented in each crosstabulation cell. If the type is set to Sum or Value List, then the Crosstab Z Axis dropdown will appear. The Crosstab Z Axis dropdown is used to specify which structure element will have its values summed or listed for each crosstabulation cell.

For grouped "three-dimensional" crosstabulation reports, a Crosstab Grouping Element can be specified. Separate grids will be rendered for each value of the Grouping element. If a Crosstab Grouping Comparison Type is specified, then grids will also be rendered that compare each crosstabulation grid with the prior one rendered.

List Report Settings
If the Report Type is set to "List Report", you can set the Maximum # of Records returned. Specifying an upper limit can be useful for limiting the amount of memory generated reports can occupy. If left blank, there will be no maximum applied to the report.

You can also specify the columns that make up the list report, fields that are used to sort records in the report, and any subreports that should be embedded.

For each column, it is possible to specify a display order, as well as a summary calculation that will appear at the bottom of the column. If Group By This Column is specified, then a subheader will be displayed whenever the value of the column changes.

You may define multiple sort fields for the report, and specify in what order they are used to sort the records within the report. You may also specify for each sort field whether the records are sorted in ascending or descending order. This Sort Fields configuration can be used as an alternative to entering sort information into the report's Search String field.

Subreports contain report results that can be related to the results of a parent report. After selected a Subreport to display, the Child Structure Element field is used to specify the child field that maps to the key field of the section the parent report is based on. Because multiple subreports can be configured, the Display Order field is available to specify which subreport grids appear first.

Notifications

Notifications can also be configured for each report. These notifications run the report for each user configured to receive the report, and sends it to them via their user account's email address. The Start Date/Time field is used to specify the first date the notifications should start, and the time of day they should be sent. A repeat schedule can be setup where reports can be sent on any of the weekdays and/or on designated days within the month.

There are three receive types available for notifications; they can be received by everyone who has access to the report, received by a specific user, or received by a configured email address. For the "Specific Email Address" Receive Type, the security level of the user who created the report is used when generating the report to be sent.

The configured reports are stored in the Section_Report, Section_Report_Column, Section_Report_Notification, Section_Report_Sort,and Section_Report_Subreport database tables.