Transflo DMS Management Reports API

Before using the TRANSFLO® Management Reports API the following steps must be taken to set up the environment:

1. Upgrade your TRANSFLO® Server to TRANSFLO® 2019 or newer.

2. Get DBO access to the TRANSFLO® Database from you SQL Server DBA.

This section describes the basic steps for programming with the TRANSFLO® Client Interface API.

1. Log into the TRANSFLO® Database using Microsoft® SQL Server Management Studio.

2. Create a stored procedure with the SQL in your report.

3. (Optional) Create a stored procedure with the SQL in your chart.

4. Run usp_add_report stored procedure to create the report entry under Custom Reports in the Management Reports interface.

The following table lists the parameters for the usp_add_report stored procedure.

Name	Description	Data Type	Default
@report_name	The name of the report as it appears in the Management Reports menu under the Custom Reports heading. This is also the name of the report that displays at the top of the report on the Report and Charts tabs.	varchar(100)
@report_description	The description that appears below the name of the report on the Report and Charts tabs.	varchar(255)
@report_hint	The hover text that appears when you hold the mouse cursor over the report name in the Management Reports menu.	varchar(255)
@criteria	A comma separated list of at least three items described Table 3.2. This list must be in the order shown in Table 3.2.	varchar(1000)
@report_sql	Any valid SQL select statement that returns at least 3 rows from the TRANSFLO® Database. The results from this query will display on the Report tab. It is highly recommended that this parameter be in the form of a stored procedure.	varchar(3000)
@chart_sql	(Optional) Any valid SQL select statement that returns at least 3 rows from the TRANSFLO® Database. The results from this query will display on the Charts tab. The chart type is a pie chart by default unless “LineChart” is in the criteria parameter. It is highly recommended that this parameter be in the form of a stored procedure.	varchar(3000)	Null
@enabled_ind	(Optional) Value of 1 enables the report whereas a value of 0 disables the report.	bit	1

Criteria Parameter

The following table lists the possible values for the criteria parameter of the usp_add_report stored procedure.

At least three of these values must be used and they must be in the order shown in Table 3.2.

Value	Description
StartDate	The From Date control appears on the Criteria tab. It defines the earliest date for the report criteria.
EndDate	The To Date control appears on the Criteria tab. It defines the latest date for the report criteria.
DocClass	The Select Doc Classes control appears on the Criteria tab. This control allows the user to limit the search results to a sub-set of the available Document Classes.
VerticalHeader	Header text is rotated 90˚ Clockwise. This allows more column to be visible without horizontal scrolling.
Hourly	Shows the Report Breakdown drop down on the Criteria tab. The drop down contains two values: “Hourly” and “Daily”. If the user selects “Hourly” from the drop down, hourly statistics are displayed on the report. If the user selects “Daily” from the drop down, daily statistics are displayed on the report.
User	The Select users control appears on the Criteria tab. This control allows the user to limit the search results to a sub-set of the available Users.
IndexUsers	The Select users control appears on the Criteria tab. This control allows the user to limit the search results to a sub-set of the available Capture Users (Indexers).
ShowChart	Enables the Charts tab for the report.
LineChart	The default chart type in the Select Chart type drop down on the Charts tab is “LineChart”. If this value is not set the default chart type in the Select Chart type drop down is “PieChart”.
ShowExpandAll	Shows the Expand All link on the Report tab. This link shows all rows when the report is only showing totals.
ShowTotalsOnly	Shows the Totals Only link on the Report tab. This link shows total rows only when the report is showing all rows.
ShowDetailsOnly	Shows the Details Only link on the Report tab. This link shows all rows in the report except totals rows.
ShowPrinterFriendly	Shows the Printer Friendly (PDF)… link on the Report tab. This link shows all rows of the report as a PDF file.

Example

1. Execute the following SQL against the TRANSFLO® Database using Microsoft® SQL Server Management Studio.

   Create a stored procedure with the report SQL. This will be used as the @report_sql parameter in the call to execute usp_add_report.

   create procedure usp_report_test (

   @customer_id int,

   @doc_class varchar(1000),

   @user_list varchar(1000),

   @from_date datetime,

   @to_date datetime

   )

   as

   select * from tf_config

   Go test your stored procedure:

   exec usp_report_test 2, 'df', '*', '1/1/2012', '2/1/2012'

   Create the report by running the usp_add_report stored procedure.

   exec usp_add_report 'Test Report Vertical', 'Test description',

   'Test Custom Report', 'StartDate,EndDate,DocClass,VerticalHeader, User', 'usp_report_test(CustomerID, InDocClass, InUsers,

   FromDate, ToDate)'

2. Test the report by logging into Management reports. The report appears in the list of reports under the Custom Reports heading in the menu.

   

3. Enter your search criteria on the Criteria tab.

4. Click the Submit button.

5. Verify the report looks as expected on the Reports tab.

   

When executing the usp_add_report stored procedure, use the information on the Messages tab to identify any syntax errors.

Example 1

Stored procedure sent to the @report_sql parameter is missing parameters.

exec usp_add_report 'Test Report Error 1', 'Test description',

'Test Custom Report', 'StartDate,EndDate,DocClass,VerticalHeader, User',

'usp_paul_report_test'

1. Error message generated:

   

2. Correct the syntax and re-run the exec statement.

Example 2

Values in the criteria parameter are in the wrong order.

exec usp_add_report 'Test Report Error 2', 'Description', 'Test Custom Report',

'usp_paul_report_test(CustomerID, InDocClass, InUsers, FromDate, ToDate)','StartDate,EndDate,DocClass,VerticalHeader,User'

1. Error message generated:

   

2. Run the following to find out what is expected for the first parameter:

   exec usp_add_report

3. The error message generated indicates that @report_name is the first expected parameter.

   

4. Run the exec statement with only the first parameter:

   exec usp_add_report 'Test Report Error 2',

5. The error message generated indicates that @report_description is the next expected parameter.

   

6. Repeat this process until all parameters are supplied.

7. Once the syntax is correct and the report is created, you will get a message similar to the following: