iMaint Help - Creating and Editing Report Layouts

Before You Begin Advanced Report Writing

If you are planning to create new reports using views that don't already exist in the iMaint database you will need to have the following knowledge. iMaint Online Customers will face database restrictions with advanced report writing. Some of the following will not apply to iMaint Online Customer.

The .NET 4.6 Framework will need to be installed on each computer that intends to access the Reports module.
You will need to have a strong working knowledge of Microsoft SQL database and the process of creating views. This document assumes that you are using Microsoft SQL Server 2005 or later. We highly suggest taking advantage of the Professional Services training classes offered by DPSI.
You will also have to have a good working knowledge of the iMaint table structure in order to locate which of the tables contain the fields of information you wish to use in your report. You may also wish to enlist the aid of your Database Administrator for the appropriate access to the iMaint databases.
Make sure you also have ReportAdmin security rights within iMaint. Any new view you create in SQL will also need to have permissions set accordingly.
If you copy an imrpt_view, it will need to be named cmrpt_unique_view_name. It does not matter what you call the view, as long as you have the cmrpt in front of the name. Never use an original imrpt view with any custom report. View names must be 30 characters or less. If upgrading from 2.37 SP1 view names larger than 30 characters will no longer be read by iMaint. If you run across this issue, try making a copy of your custom report containing the longer view name, and during the copy process shorten the CMRPT_view_name to 30 characters or less.

Note: DPSI does not support reports that reference views created or copied by anyone outside of the DPSI Development Team. You must have a database view created or copied, and named with the correct naming convention, before you can begin creating a report for use in iMaint. Also, don't forget to give the new custom view permissions within iMaint Security. Because the iMaint tables and views can change within application enhancements you should not use the DPSI supplied view to create custom reports. Copy the DPSI views and rename them using the prefix cmrpt. We recommend that you create a copy of the original iMaint report before you start to make any changes. When you copy a report, iMaint will automatically make a copy of the database view used within the original report. Always remember to create copies of the original reports and save them under a different name before making modifications. Use the prefix CMRPT when naming your newly created view or a copy of the iMaint view.

iMaint Online Customers and Advanced Report Writing

Prior to the iMaint 4.0 release, iMaint Online customers had to install the Standalone Report Client to have access to copy and edit the DPSI pre-defined reports. With the release of iMaint 4.0, iMaint Online customers will now have access to copy and edit the existing iMaint reports without having to install a separate report client. This can be accessed from the iMaint Navigation Tree with proper security rights.

iMaint Online customers can go into the layout of a report and edit it, save your new report and give it to customer support to publish into your live Site. However, you will face restrictions. One is the ability to create your own views or edit the views within SQL. For example, a field you want to add to a report does not exist within the data source/view the report is currently using. To accomplish your goal, it will require going into Microsoft SQL Server and editing the view, or possibly creating a new view to use as a subreport. iMaint Online customers who wish to develop their own custom reports from scratch, create their own views, or edit an existing report view will need to have a local copy of Microsoft SQL Server installed. DPSI will be happy to give you a backup copy of your live database, which you may restore into your local copy of SQL. This will allow you to enter SQL to display and design report views. Another restriction you may face is testing the report. You will be able to create your own views in SQL, and create custom reports but you will not be able to run and test those reports within iMaint until Customer Support publishes the report into your live Site. The data source list is restricted to the Site you are logged into and Support will need to script any new or altered views into the appropriate database

What is a View?

When creating a new report there are two objectives you have to consider. The first objective is to use the appropriate ”view” containing all the necessary fields of data. The second objective is the actual design and look of the report. Let’s start with views. Every report must be related to at least one, Microsoft SQL Server database view. In ActiveReports, these are called the data source. Views are the building blocks used to create reports. They hold all the data fields you wish to display on your report and can consist of fields from a single or multiple iMaint tables. You may browse through the existing iMaint views by looking in your iMaint Database in SQL Server Enterprise Manager. The Report Designer uses these views to retrieve information from the iMaint database. (The sorts and groups within your report layout will override any already set within the view.)

The two most important things to know about iMaint views are as follows:

Always use a copy of iMaint System views.
Give iMaint Users Permissions to the copy of the view.

DPSI provides a report to assist users who are trying to create new or edit existing reports. Run the report called 2701 iMaint IMRPT and IMRG Views. This one report lists all iMaint Report Views, each field used, the data type and the field size. It is approximately 187 pages long and lists all existing views by Site and in alphabetical order.

For example, if you are looking for a view that contains data about open Work Orders that are past due, this report will help you decide which view gives you the Work Order Status, Work Order ID and Past Due Dates.

Using the iMaint Report Views list is just one way to find the correct data source. You may also know of an existing iMaint report that already contains data you need. By copying an existing iMaint report, the system will automatically make a copy of the correct view. Once you make a copy of any iMaint report, you may open and edit the layout to make changes.

We do not recommend customers use the existing views from within the iMaint Databases, always make a copy first. Usually, DPSI recommends that a customer locate a similar report, copy it and make changes to the copy. Again, with this process the system automatically creates a copy of the view, and ensures you are not using a system view.

However, when creating your own report from scratch, you will need to ensure that you are using a copy if the of the iMaint system view. iMaint system views start with IMRPT. When you copy any iMaint report, the system will name the copy with the prefix of CMRPT. The copy of the view you need for your report should also be named with the prefix of CMRPT. If you open SQL Manager and copy a view, use the CMRPT as the Prefix. CM stands for Custom and is how iMaint will know this is a custom report you wish to use within ActiveReports.

If you copy a view manually, you will do this through SQL Database Manager, so we suggest that you have some background with working inside the database structures. After you have created a copy of the view you need, use the option within iMaint ActiveReports, under Tools, called Refresh Views to force iMaint to look out at the database and pick up any changes. If you try to attach your view to the report, and it does not appear in the list as a choice for your data source, try the option to Refresh Views. Sometimes, closing out of iMaint all together can help too.

For example, iMaint report 1602 Schedule List by Schedule ID, uses the view imrpt_schedule_list. If you were to copy this report, through ActiveReports, iMaint will automatically add a CM, for Custom, to the view name, CMRPT_schedule_list and the report ID, CM 1602. When you save the .rpx report file, the custom view is saved within the file. Now, you are then able to make changes directly to the custom report layout. But when creating a report from scratch, you will need to have the custom view already copied and named correctly within the database. After you select the option in ActiveReports to Create a New Report, the view (data source) is selected under the View ribbon.

Also, view names must be 30 characters or less. If upgrading from 2.37 SP1, view names larger than 30 characters will no longer be read by iMaint. If you run across this issue, try making a copy of your custom report containing the longer view name, and during the copy process shorten the CMRPT_view_name to 30 characters or less.

Now that you have made a copy of the view you need, you must also give permissions for iMaint users to run the report. If you do not set permissions, you will receive a message when you try to run the report. Make sure you have used the Refresh Views tool, then open the iMaint Admin and navigate to Security. Under permissions, select to apply permissions by view. Find the custom view and check the Select box beside the groups you wish to have permissions to this report. When an iMaint report is copied, the new custom view and any subreport views are granted permissions under the Public group in security.

Note: Another reason to never use a system view is to prevent trouble with a report during a future upgrade. The DPSI development team may makes changes or enhancements to the views, to which you are also responsible for adding to your custom views. And if you don't have the CMRPT before your view name, when the database is upgraded, your view will not be copied over to the new database for use with ActiveReports. Again, DPSI does not support reports or views altered by anyone other than the DPSI development team.

When saving a report file, use the extension .rpx and make sure to save it to a location that allows iMaint the appropriate access. The report layout, views and subreport views are all packaged within the .rpx file extension.

Copying Original Reports Before Making Changes

As seen above you will not be allowed to edit the data or layout of an existing iMaint Report. You must make a copy of the report and rename it before any changes are made. Find the existing report you wish to copy and right click on the record to display a pop up menu. On the menu, select the option to Copy the report and the Copy Report window will display. If you don’t change the name the system will automatically name it CMRPT in place of IMRPT. Where the CMRPT represents Custom Reports and IM is representative of iMaint Reports. The report ID will also contain a CM prefix.

Field	Description
ID	The system will default a new ID but you may change this ID before adding it to the iMaint database.
Record Limit	The number of records the report can display or print each time the print/preview option is taken.
Report Options	You have the ability to select to print this report with work orders or purchase orders. Check the appropriate selection if you desire this report to print along with work orders, purchase orders or both listings.
Category	Select the category to file the new report under. All reports must belong to at least one category.
Description	Enter a short description of your new report.
Master View	The database view used to build the report. This cannot be more than 30 characters.
Comments	Use up to 255 characters to describe the attributes of your new report.
If subreports are used
Sub Report Name	The name of the original sub report used.
Copy	Check the copy box on any of the sub reports you wish to use with the new report.
SubReport New Name	Rename the copy of the sub reports you wish to use within your report.
SubReport View Name	The original name of the view used to build the report.
SubReport New View Name	Rename the copy of the view to be used when editing or changing your new report.

iMaint will automatically make a copy of the database view, with the prefix CMRPT and when you save the .rpx file, will keep the relationship between your custom report and the new custom view. Just remember, the view name can not be longer than 30 characters. You may find the need to shorten your view name during the copy process.

Since all other fields on the Data window are open, you may define your report as you wish using the Description and Comments fields. Set the Record Limit to an appropriate number. When you have made all the changes to this screen simply click OK to receive a confirmation message the report has been copied.

Now, you will find the copy under the Custom Reports Category.

The following are examples to note the difference between original iMaint reports and copied reports.

imrpt_asset_bar_code.rpx is renamed to cmrpt_asset_bar_code.rpx
imrpt_asset_bar_code, the view within the database is renamed to cmrpt_asset_bar_code
1902 is renamed to CM-1902

Navigate to the Custom Reports Category and select the copied report. Right click on the report to display the pop up menu options. Selecting Edit the report will now allow you to select Layout. Under the Layout option you will be able to add or change fields, and modify how fields are displayed.

Note: As you get started, if you need further information on creating reports using the ActiveReports Designer contact the DPSI Support team at 1-800-458-0431 or via E-mail at imaintsupport@dpsi.com. You may also contact your DPSI Representative for ActiveReports training opportunities.