Versions Compared

Old Version 5

changes.mady.by.user Michael Standal

Saved on

compared with

New Version Current

changes.mady.by.user Emma Fullilove (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

ACM ships with pre-made reports covering many different data topics. Custom reports can be created that are specific to your organizational organization’s needs. This page will show how to create a new custom report and where to locate save it for users to access.

Note
titleCaution on Creating Reports

Custom reports interact with the ACM database directly, and misconfigured reports can modify or delete contents from the ACM database which may cause issues. See Reports Tab on how to configure the reporting account to a read-only SQL user/account.

Instructions

  1. Create a new file with the .XML extension.
  2. Paste in the sample File Format shown on this page.
  3. Enter a Name and Description for the report. Assign a unique name. This is the display name that will appear in report listings. The description is supporting text to explain what the report provides.
  4. Enter the SQL for the report:

    1. SQL Query method. 

      Info

      Example: <!-- SELECT * FROM TblObjects ORDER BY ObjectId -->


    2. Stored Procedure method.

      Info

      Example: <!–EXECUTE dbo.asi_spProcedureName '@Parameter' -->


  5. Create one or more parameters as needed. If no parameters are needed, leave the parameters node empty. See Parameters section for details and examples.
  6. Save the file to the File Location shown below. The Report Service will include the new report the next time the listing is loaded or refreshed. There is no need to restart the Report Service.

...

Info
Example: C:\ProgramData\AutoSol\Communication Manager\7\Reports

Anchor
FileFormat
FileFormat
File Format

Reports are XML files with the following schema:

...

Parameters consist of the following elements:

  • Type (Determines the data type and user entry method .such as checkbox or drop-down listing and its choices)
  • Name (The label for the parameter and the token used in the SQL statement to resolve the value when executing.
  • Description (Helpful description shown to guide user.)
  • Default (Default value for parameter. Can be Custom Default for Date/DateTime types only.)
  • Min/Max (If applicable, include the minimum and maximum range the value can be.)


Info
titleNo Parameters Example

<parameters>
</parameters>


Info
titleOne or More Parameters Example

<parameters>
<parameter type="Boolean" name="OnlyEnabled" description="Enabled Devices Only" default="true" />

<parameter type="Integer" name="Missing" description="Missing Record Count" default="0" min="-1" max="100"/>

<parameter type="Boolean" name="IncludeStation" description="Include Station Meters" default="false" />

<parameter type="Device" name="DeviceName" description="Device" default="0" />

</parameters>

Supported Parameter Types

TypeDescription

DateTime

Date

A value representing a

custom date and time modifier to start as

datetime. Modifiers can be used to set a default. Uses the default attribute of the parameter node.

See Supported Custom Defaults below.

Date

A value representing a Date. The UI will display a Date Picker control.
BooleanA value representing a choice of True or False. The UI will display a toggle or checkbox control.
IntegerA value representing a generic number such as a number of days or hours. The UI will display a number entry control.
DeviceA value representing a device object number. The UI will display a listing of protocol devices to choose from.
ObjectA value representing an object number. The UI will display a listing of objects to choose from.
MeterA value representing a meter object number. The UI will display a listing of meters to choose from.
ArchiveA value representing
a
an archive object number. The UI will display a listing of archives to choose from.
PublisherA value representing a publisher object number. The UI will display a listing of publishers to choose from.
FilterA value representing
a
an archive filter object number. The UI will display a listing of archive filters to choose from.

Supported Custom Defaults

TypeDescription
Current

The current date and time as of executing the SQL.

TopHourThe current top of hour as of executing the SQL. Example: Current Time=12
.
:30PM, TopHour=12
.
:00PM
StartDayThe current date as of executing the SQL.
EndDayThe current date plus 1 day as of executing the SQL.
YesterdayThe current date minus 1 day as of executing the SQL.
PriorHourThe last top hour minus 1 hour from current date and time as of executing the SQL. Example: Current Time=12
.
:30PM, PriorHour=11
.
:00AM
NextHourThe next top of hour plus 1 hour from current date and time as of executing the SQL. Example: Current Time=12
.
:30PM, NextHour=1
.
:00PM
StartMonthThe
current date's month starting day as
date of the first day of the current month as of executing the SQL.

Examples


Info
titleArchive Hourly Summary (Stored Procedure Method)

<?xml version="1.0" encoding="utf-8" ?>
<report>
<name>Archive Hourly Summary Report</name>
<description>Returns the Archive Hourly Summary. Use the Missing Record Count parameter to control the minimum number of records a device must be missing to be included in the report.</description>
<sql>
<!-- EXECUTE asi_spHourlyArchiveStatus '@onlyEnabled', '@iMissing', @includeStation -->
</sql>
<parameters>
<parameter type="Boolean" name="onlyEnabled" description="Enabled Devices Only" default="true" />
<parameter type="Integer" name="iMissing" description="Missing Record Count" default="0" min="-1" max="100"/>
<parameter type="Boolean" name="includeStation" description="Include Station Meters" default="false" />
</parameters>
</report>

Note: Stored Procedure Parameters are preceded by the "@" character. The matches the name of the corresponding parameter.

...

Info
titleGC Write Request Audit Last N Days (SQL Method)

<?xml version="1.0" encoding="utf-8" ?>
<report>
<name>GC Write Request Audit Last N Days</name>
<description>This report displays all audited GC write requests from the specified date.</description>
<sql>
<!-- SELECT tblGcWritePendingAudit.DeviceName As [Device], tblObjects.ObjectName As [Meter], tblGcWritePendingAudit.GcWriteTime As [WriteTime], tblGcWriteResultLookup.Description As [Result]
FROM tblGcWritePendingAudit, tblObjects, tblGcWriteResultLookup
WHERE tblGcWritePendingAudit.MeterId = tblObjects.ObjectId
AND tblGcWritePendingAudit.GcWriteResultId = tblGcWriteResultLookup.Id
AND tblGcWritePendingAudit.GcWriteTime > getdate() - @Age
ORDER BY tblGcWritePendingAudit.GcWriteTime -->
</sql>
<parameters>
<parameter type="Integer" name="Age" description="Age (in days)" default="30" min="0" max="730"/>
</parameters>
</report>

Note: SQL Parameters are preceded by the "@" character. The matches the name of the corresponding parameter.


Info
titleMissing Daily Archive Records (Stored Procedure Method)

<?xml version="1.0" encoding="utf-8" ?>
<report>
<name>Missing Daily Archive Records</name>
<description>Identifies missing daily records, using the Archive Management process.</description>
<sql>
<!-- EXECUTE asi_spMissingDailyRecords '@startDate', '@endDate', '@enabled' -->
</sql>
<parameters>
<parameter type="Date" name="startDate" description="Start Date" default="StartMonth" />
<parameter type="Date" name="endDate" description="End Date" default="StartDay" />
<parameter type="Boolean" name="enabled" description="Enabled Devices Only" default="true" />
</parameters>
</report>

Note: Both parameters Start Date and End Date make use of custom defaults.

Image Added

...