You can add some useful functionality by integrating custom reports into your IDI platform. Grid reports are the most popular type of reports, as they allow the user to take advantage of the built-in functionally to Sort, Filter, Find and Export data from the system into CSV (Excel), HTM or XML formats. A printed version of a grid report can be easily created by simply exporting the data into the HTM format.
SQL queries, embedded in an XML file, are used to create custom reports. There are four basics steps to creating a custom report:
Create the SQL queries required to extract the desired data from the database. See below.
Use the Query Analyzer/SQL Server Management Studio to verify that the SQL queries extract the expected data from the database without error.
Create an XML file that defines the layout/format of the desired report. Copy the SQL queries into the appropriate location in the XML file. See below.
Load the resulting XML file into the IDI platform and add the report to the desired folder.
Note
When you create custom reports, you take ownership for tracking them, inventorying them and testing them to ensure they are not lost or altered during upgrades and other service enhancements.
IDI reserves the right to change, modify or remove any table, stored procedure, function, field, Attribute Type, and Attribute Definition as a part of their normal upgrade process. This may affect your unique scripting and will require you to modify or update your procedures and functions as necessary.
The following describes each step in creating custom reports in more detail.
Note
For assistance in creating custom reports, contact your account manager.
Custom SQL Query Guidelines
The first step in creating a custom report is defining the SQL query for the data you want to include in the report. Once you know what data you need, use the Query Analyzer to write and test the SQL query required to extract the desired data from the IDI database.
Sample Query
/* Accounts Per Invoice Format */
SELECT
ReportGroup.ReportGroupID AS [Report GROUP ID]
, ReportGroup.Name AS [Invoice Format]
, Count(Customer.ReportGroupID) AS [Total Accounts]
, ReportGroup.Description
, ReportGroup.NOTE1 AS Message
FROM
ReportGroup
LEFT JOIN Customer ON Customer.ReportGroupID = ReportGroup.ReportGroupID
GROUP BY
ReportGroup.ReportGroupID
, ReportGroup.Name
, ReportGroup.Description
, ReportGroup.Note1
Sample Query Results
Report Group ID | Invoice Format | Total Accounts | Description | Message |
10001 | <none> | 0 | NULL | DTV NOCONTRACT TEXTING CPNI |
10002 | Std No Detail | 19254 | NULL | DTV NOCONTRACT TEXTING CPNI |
10003 | Std Include Call Detail | 7641 | NULL | DTV NOCONTRACT TEXTING CPNI |
10004 | Std Include Call Detail w Break | 59 | NULL | DTV NOCONTRACT TEXTING CPNI |
10005 | Primary Alpha sort No Detail | 43 | NULL | DTV NOCONTRACT TEXTING CPNI |
10006 | Primary Alpha sort Detail | 0 | NULL | DTV NOCONTRACT TEXTING CPNI |
Creating an XML File for a Custom Report
Create an XML file that defines the desired layout/format of the custom report.
Copy the XML file for an existing IDI core report.
Rename the copy to an applicable report name.
Modify the XML file to output the report data in the format you want for this report.
Report title – report name that appears in the Report Explorer window.
Set the 1st 3 characters of the report's title to the 1st 3 characters of your companies name to uniquely identify them.
uid – this is the unique name added in the Core DB
type – identifies the type of report:
REL = data GRID report that can be exported
RPX = printable version of report
source – identifies active report file. Only used if type = RPX.
params – list of parameters the user specifies when requesting the report.
If a report does not have parameters, this params tag should be excluded
Insert the verified SQL queries defined above into the proper location within the XML file to produce the desired results.