Dynamic Reports

The Dynamic Reports feature gives users the ability to create reports without requiring knowledge of CISPro Global's database architecture. In contrast to Static Reports, which are based on SQL queries that are generally created and then left unchanged, Dynamic Reports can be created and modified rapidly, and can even be used once and discarded.

The Dynamic Reports page displays existing dynamic reports, and allows the user three options: create a new report, edit an existing report, or delete an existing report.

Creating, modifying, and deleting Dynamic Reports requires the following permissions:

Task	Permission	Allow	View	Create	Modify	Delete
Create/Modify/Delete Dynamic Reports	reports		•	•	•	•

The first step in creating a report is selecting the table on which the report will be based. The table options are: Containers, Materials, and Users.

Default Settings If the Containers Table option is used, the report is started with 'Container Class = container' and 'Site ID = 1' as default filters.

The report name, type, and other settings for Dynamic Reports are described in the table below. Modify these settings as needed for any report that is to be saved (they are not required for one-time-use reports).

Name	Appears in the Report Name column on the Dynamic Reports page. Required for saving reports.
Description	Appears in the Report Description column on the Dynamic Reports page. Optional.
Author	The user who created the report (set automatically)
Type	Controls the appearance of the report. Can be 'Table' or 'List.'
Access	Restricts access to the report. Options are 'Private' (author only), 'Workunit,' and 'Global.'
Used For	Indicates the purpose of the report.
Enable Auto Links	Causes entities like materials and container barcode IDs to appear on-screen as links. When such a link is clicked, opens the record (e.g., container info page) it represents. See Auto Links, below.

Enable Auto Links When the "Enable Auto Links" check box is selected, certain items on the report will become links that, when clicked, open more detailed screens of information about the particular item clicked. The following table lists the auto link items and the pages they open:

Material Name	Main identity tab for the material
Container Barcode	Container Information page for the Container
Location Name	Worklist of Containers by Location
Username	User Information page

The report is built via the selection of Headings (table columns) from a picklist. The application of logical operators (and, and not, not, or) and filters determines which records are included.

In addition to headings from the Materials and Containers tables, reports can include material and container properties, subject to the same filters used for the other headings.

A report that is run without filters will return all records* from the table for which it is created. For example, a report that's based on the Containers table will include every container in its results, unless some filters are applied to limit what containers are included. Adding filters is accomplished by selecting a column name from the 'Heading' drop-down list, and then setting criteria for the selected column.

For a text heading	Select 'begins,' 'contains,' or 'exact' and enter a filter value
For any numeric heading	Select 'begins,' 'contains,' 'exact,' 'greater than' or 'less than' and enter a numeric filter value
For any date heading	Select 'after,' 'before,' or 'on,' and select a limiting date using the calendar tool provided. You can also type "today + x" or "today - x" where x is some integer, to limit the date to a particular day relative to the current date.
For any heading	For any of the above filter types, you can always choose 'not' from the Type drop down list, if an exclusion filter is easier than an inclusion filter. For any heading beginning with the second, you can choose one of the 'and,' 'and not,' and 'or' operators. See Searching for an explanation of how the operators are evaluated in a query.

* Limit the number of records The number of rows in Dynamic Report output can be limited via the reportingrows configuration variable (see Configuration). The default limit is 10,000 rows.

Tip! To add additional criteria on any heading, click its 'More Filters' button, and set the criteria according to the guidelines in the table shown above.

All headings included on the report act as filters on the report's contents, but whether and how they appear on the report is determined by the following filter settings:

Prompt	When Prompt is selected, the user is prompted for filter criteria when the report is run.
Band	Banding makes one or more of the headings into a "master" heading, set above the other headings on a gray background. Sort and Band work together, because it makes sense to sort the rows by the master banded heading. Banding gives a greater sense of order and breaks the report down visually into sections according to the banded heading.
Group	Grouping suppresses redundant information in the report's details. When a heading is grouped, if its value is repeated on a set of consecutive records, the value will only be shown on the first record of the set. The Group setting is ignored when no headings are banded.
Display	When Display is selected, the data from this heading (column) appears on the report.
Order	Change the left to right order of the headings on the final report by entering values into each of the Order edit boxes. Once you have entered your preferred order and clicked Save, the headings on the Build a Report page reorganize themselves to reflect the order you have assigned.
Sort	Selecting a Sort check box causes the rows of the report to be sorted in ascending order by the values in the column for which it is set. If more than one Sort check box is selected, the rows are sorted in nested order (values of heading 2 within each value of heading 1). Saving a row with the Sort check box selected, requires setting the row's Order field value as well (i.e., Order can't be blank).

A preview of the dynamic report is always displayed at the bottom of the report builder page. The preview displays the first 15 rows of the report. Preview and save options include:

Refresh	Refresh the preview to see the effect of changes without having to save the report.
Run Report	Preview the entire report in a new window without saving. Use the browser's File menu in the new report window to save the report output in a preferred format.
Save Changes	Saves the report in its current state, at which point it can be run from the Reports link on the home page.
Save as New	Saves the report in its current state as a new report. Note: Report Builder allows duplicate report names, so modifying an existing report and clicking Save as New without first changing the name will result in two reports with the same name.
Revert to Saved	Discards all changes made to the open report since the last save.
Clear	Has the same effect as clicking the New Report button on the Dynamic Reports selection page, but uses the same Table option as the currently viewed report.

To see the entire report while still in design mode, click Run Report. Use your browser's save options to save the report in your choice of format. Alternatively, you have the option of saving the report as an Excel spreadsheet by clicking the View in Excel link at the bottom of the actual report.

Once the report is saved, it can be run from the home page. Reports that have one or more headings with Prompt selected will prompt the user for input.

Viewing a Report's SQL Query At the bottom of the Build a Report page, the SQL query that will be used to select the report content is displayed in the Report SQL box. This query is updated each time a change is made to the report settings. The Report SQL content can be used to prepare Dynamic Reports like those described above, and have them automatically sent to others via email on some regular schedule of your choosing. See the Email Report Notifications topic for further information.

Modifying Dynamic Reports Click the Dynamic Reports link on the System Configuration page to see a list of the dynamic reports you have access to. If you have modify permission to a particular report, click the Edit Report button to open the report building screen, where you may modify the report. Click the Save Changes button to save your modifications.

Deleting Dynamic Reports To delete a report, click the Delete Report button to the right of the report name. Be sure that you really want to delete it, because you will not be prompted for confirmation of the deletion.

View Report in Excel Users can view report content in Microsoft Excel. When a report is run, a 'View in Excel' link is displayed at the end of the report. Click the link to view the report in Excel.

Changing the Decimal Separator Symbol Setting the dynrep_usecommas configuration variable to '1' enables the user to choose either periods or commas as the decimal separator symbol on report output.

Changing Column Names The names of the columns that appear on the report can be changed via the Report Column Names link on the System Configuration page.