Publishing the Crosstab Report
Copyright © 2019 EarthSoft, Inc • Modified: 18 Feb 2019
Any standard EQuIS report that produces a tabular output can have an associated Crosstab Report published to the database. Crosstab Reports cannot be published to the database if the associated parent report .dll has not been published to the database or if the associated report is not a function or stored procedure. After configuring the Column Header(s), Row Headers(s), Tabbed Data, and Options, publish the Crosstab Report.
Note: The Creator permission on the Reports Object Type (in addition to access permissions) is required to be able to open the Crosstab Publisher Wizard and to be able to publish Crosstab reports. The user who publishes a crosstab report will automatically be granted Owner permission on that report.
1.Check to make sure the Crosstab Report is configured as expected. Click Go on the toolbar to run the report.
The Crosstab Report will be displayed in grid format.
2.Click Publish, and the Publish Report window will display.
3.Enter the appropriate information as shown below.
•Name: Title the report. This will be the name of the report as seen in the Professional report list.
Note: The Name field defaults to unique text based on the underlying report name. If the text is altered so that it is the same as an existing published crosstab report, the application checks whether the user has at least Editor permission on the already published crosstab.
If the user has Edit permission on the existing published crosstab report, a prompt asks whether they want to update the existing crosstab report.
If not, the user is notified they do not have permission to overwrite the existing report and to use a different name.
•Applies to: Determines from what EQuIS facilities this report may be run.
oAll Facilities: Enables this report to be run from any facility within the current database.
oOnly [Database Name]: Makes the report available only for the current, named facility.
•Parameters: Decides how report parameters will be chosen.
•Prompt for saved User Report (Default): Asks you to select a saved User Report of the same type on which the crosstab was built.
•Show all Input Parameters: Allows you to reenter the report parameters (from original report used to create crosstab). This option does not require the User Report to be saved first.
Note: When running a published Action Level Exceedance Crosstab, take care that the same Action Levels that were used in the published crosstab configuration are used in either the chosen User Report or selected parameters.
•Description: Enter an optional description of the report. This description will appear in the tooltip that displays when hovering the cursor over this published report in the Reports list.
•Publish Crosstab as Grid Report: Check the box to choose grid output for use in Enterprise. Leave unchecked for Excel output.
The Crosstab Grid is a report type introduced in EQuIS 6 and is used to denote a published crosstab that exports to a grid in Professional, instead of straight to Excel. When this box is checked, the ST_REPORT.REPORT_TYPE will be xtabgrid and selecting this report to run will show the data as a grid in Professional (instead of exporting to Excel like a normal published crosstab report). The published crosstab is a grid report, and it can be used in widgets that require grid reports (e.g. Traffic Light, Traffic Light Map, Time Series Chart, etc.). This allows you to define your own crosstab logic (particularly useful with aggregates), and use that output as a grid report elsewhere in EQuIS.
4.Click Finish to publish the Crosstab report.
1.Reconnect to the database or restart EQuIS Professional.
2.Click on Reports.
3.Locate the report by name (same as that assigned when published).
4.Open the report.
5.Select the appropriate parameters or saved User Report.
6.Choose an output type. Available options follow.
•Some users encounter OutOfMemory exceptions when trying to export large Crosstab reports to Excel. A new option is now available to reduce memory usage (and increase speed) when exporting.
•When using Excel templates to export a Crosstab report, you can now use the selected ColumnHeaders as template codes.
1.In the source database, open ST_REPORT and note the REPORT_ID of the published crosstab report in question.
2.Open the Blobber Form in the source database and select ST_REPORT from the Blob Table drop-down, and enter the REPORT_ID field corresponding to the published crosstab in the Key Value field. Select Download and save as an .xml file.
3.In the target database, run the base report, e.g. Analytical Results II, making sure to select expected fields such as Location Group, Analyte group or Action Levels to create the Available Fields for the crosstab configuration.
4.Open the Crosstab Configuration screen and open the saved .xml file from number two above (if the old .xml file does not exist, it may be necessary to recreate the crosstab from scratch). Run the crosstab and export to Excel in Professional to ensure it is correct. It may be necessary to reselect or upload an Excel template if one is included.
5.Once the crosstab is configured and the configuration is saved again, publish to the target database using the Crosstab Publisher Wizard.
6.To allow Excel templates to show in Enterprise, open the Docs & Photos Form in Professional. Select the template on the left, and select the database or facility to upload it to on the right. Select Upload.
7.Open DT_FILE and note the FILE_ID of the new file. Check the FACILITY_ID to make sure it is correct. A null value will make it available to all facilities in the database.
8.Open ST_REPORT and enter the FILE_ID from number seven above in the XLS column of the new published crosstab row.