Considerations For Reporting Against Remedy ARS Based Applications

Applications built with the Remedy® Action Request System™ (ARS) client/server workflow engine are wonderful online transaction processing (OLTP) environments. When it comes to getting meaningful information back out of ARS environments, the users and application designers get a bit befuddled on what is available to them; what’s right for their organization.

This paper takes an approach to enlighten those who wonder what options are available and to those who want more information from their ARS based applications. They also want to know what the underlying considerations on each option are.

As someone, and it couldn’t have been a salesman, once said, “…you don’t get nothing for nothing.” And it’s true!

So we’ll start with reporting and analysis options built right into the Remedy/ARS environment. Then we’ll venture outside of the ARS environment and look at some other innovative ways to report and analyze information from any Remedy/ARS application.

Remedy® User Tool Native Reporting Subsystem

Background

The out-of-the-box method for reporting from an ARS environment comes with the installation of the Remedy User Tool, typically run from a client side workstation.

This “native reporting” capability is built off the results of a User Tool search capabilities.

With an ARS form open in “Search Mode”, there are three methods of searching records (tickets) in ARS applications.
• Search By Example
• Advanced Search
• Combination of both Search By Example and Advanced Search

When any one of the search methods is applied, a resulting returned set of records (zero to many) presented in the User Tool in a “split screen” form called the “Matching / Modify” panes.

With returned records in view, any or all of the returned records can be made available to the native reporting subsystem, by highlighting them in the matching pane. The native reporting system is then evoked to perform report design or to apply a set of previously saved formatting specifications.

The native reporting specifications do not contain a rich set of capabilities. However, simple calculations, sorting, grouping and summarization can be performed. Stock reporting, with headers, detail and footers can be achieved.

All fields on or hidden on the searched form the user has access to are available to used in native report presentation specifications. Once the specifications have been made, the presentation report can be executed and previewed, printed, saved to ASCII text file, or exported into comma separated value (CSV). Using CSV allow other presentation tools outside the User Tool, such as MS-Excel, to be used for final presentation. There is also a proprietary object structural export format, called “ARX”. This format is used for portable transporting data between ARS systems and is typically not used for any presentation.

The report presentation specifications can be saved for later use. The storage format has a file extension of AR Report (ARR) and, by default, is stored in the same location as Remedy User Tool macros, which have extensions of ARQ. These ARR report definitions are ASCII text files with a proprietary translated specifications. They can be viewed with an editor but it is not recommended to alter them outside the User Tool native reporting subsystem.

The ARR files, as like the ARQ macro files can alternatively be placed on a file server and shared between multiple ARS users. The location of reports and macros are controlled by User Tool option setting “Search Path”.

As like old MS-DOS path environment variable, you can extend the places the User Tool looks for Remedy macros and reports by listing them in the Search Path separated by semi-colon (;). This option setting can be found under Tools ? Options from the User Tool menubar. Since version 5 of User Tool, both drive letter and Microsoft Universal Naming Convention (UNC) formats are supported. UNC format is the recommended method.

Example Search Path showing local and a shared file server using UNC format:

C:\Program Files\AR System\HOME\ARCmds;\\MyServer\PublicShare\ARCmds

The native reporting subsystem is highly documented in the Remedy User Tool’s Help subsystem. You can open the help document by selecting Help ? Contents and Index on the menubar. Then select the “Reports” chapter. The Help contains sections with tutorials including instructions on how to search, how to run reports, how to design specifications, and all the report features.

Advantages

• Price; Built right into the Remedy User Tool; no other software nor licensing needed to use it.
• Even novices can quickly start generating reports. With little training, end users are able to accomplish reporting. Users can be self-trained from tutorials in the Help subsystem of the User Tool.
• Report execution (including the search portion) can be simplified by recording them a Remedy macro. End users can simply pick the report macros from a list in the macro playback subsystem. The resulting report can be presented on screen, printed, or saved to disk.
• Central design and availability of reports can be accomplished. If using a shared storage (ie. “file server” environments) with Remedy macros and native report definitions, reports can be centrally designed and deployed without touching the users client workstations after pointing users search path to the shared environment.

Disadvantages

• Performance. The searching capability in a typical designed ARS application is usually satisfactory. However, when passing records to the reporting subsystem there is a significantly noticeable delay in processing even as few as several hundred records. Though the reporting system is almost entirely client side processing, more CPU power and/or more memory doesn’t increase performance.
• Limited report design features and functions, as mentioned earlier.
• Access to report objects are limited to the data the user has access from the searched form, only. Data can’t be linked or joined with associated data. In complex and well normalized ARS applications this becomes a problem because the fields on the form are just id fields for other forms/tables. To compensate for this, data has to be de-normalized by the application designer which causes application growth of the size of the ARS application as stored on the ARS Server.
• No graphic capability; ASCII text representation only. Charts, graphs, images, logos, background formatting such as “grey bar” are not possible.
• No cut/paste from “report preview”. This is annoying when you just want to grab a portion of reported data for other presentation or quick communication, like to add to an email.

You are forced to report it to disk, open it with an ASCII text editor, then do cut/paste. Annoying!

• No search resulting previewed report. No jump-to-page positioning on previewed report. You can only scroll through it.
• Reports can’t be scheduled. The native reporting subsystem is demand reporting only. Reports can only be invoked by user running the User Tool and performing the steps needed to open a form, search for records, go into report subsystem, select the report formatting definitions, and execute the report.

Remedy® Flashboards™

Background

Remedy/Flashboards was a companion product (pre ARS version 6) now integrated in version 6, or greater, which allows a Remedy developer using the Remedy Administrator Tool, to design real time and historical charts, graphs, gauges, diagrams forms called “Flashboards”. Flashboards can be viewed standalone, or added to existing Remedy forms to enhance the usability and functionality of ARS designed applications.

With a licensed version 6 of ARS Server, you are allowed to design up to three free Flashboards (a Flashboard is considered any one chart, graph, gauge or diagram). When over three 3, additional licensing is required. The system will not run the 4th Flashboard without a valid license pack.

The Flashboard design definitions are performed in the Remedy Administrator Tool. They follow an extended feature and function set but are very much in line with designing Remedy ARS applications forms and workflow.

Therefore, the skill set of your existing application Remedy Administrator(s) is the target audience to build Remedy/Flashboards for ARS applications. Training for Flashboards is available where you get your Remedy Administrator training classes.

Advantages

• Integration with the ARS Framework. Both Flashboard definitions and resulting aggregated data collected are stored in the ARS datastore.
• No issues with data types or availability of data. Flashboards are inside the ARS product bubble.
• Flashboard design skills are an extension of the Remedy developer/Administrator skill sets. Skilled Remedy Administrators pick up Flashboard design quickly.
• Flashboards can present real time information out of the ARS datastore. All other reporting methods can only obtain, near real time.
• Flashboards can present long term historical information. Other reporting methods may have to extract and store subsets of data to accomplish the same resulting in more systems and disk storage needs, in turn, creating more complexity. With Flashboards, it all exists in the ARS datastore.

Disadvantages

• Cost of Server components in the way of additional Flashboard based licenses and license maintenance adds up quickly. Licenses are where Remedy makes their money; Contact your Remedy sales represetative.
• Additional ARS server capacity required. Including size larger requirements of the ARS datastore because Flashboard definitions and aggregations are stored within and additional processing needs Flashboard server processes increase as you design and activate more Flashboards.
• Flashboards are not an end user reporting environment. They require Flashboard developer skills. Learning curve for non-skilled in Remedy/ARS development is significant.
• Flashboard design is application development; This can take time, especially if the organization doesn’t have the skill sets and must seek/contract from outside.
• Intent of Flashboards is real time and historical trending analysis. The environment is not good for stock, list and form (like invoices, mailing labels, etc.) reporting.
• Poor Flashboard design can have significant impact ARS online transaction processing (OLTP) applications, which ARS environments are typically OLTP.

Remedy® User Tool Native Reporting Enhanced by Crystal Reports®

Background

The Remedy User Tool comes bundled with a run time version of the Crystal Reports Viewer and a Remedy supplied open database connectivity (ODBC) driver.

There are special hooks in the User Tool’s native reporting subsystem, to use report definitions created from a Crystal Reports Designer tool for presentation portion of the native reporting.

With a separately purchased Crystal Reports Designer tool license(s), all the rich report design feature sets of Crystal Reports is available. You can then create the presentation portion, including graphs, charts, pivot tables, pivot charts, imbedded images, colors, fonts, and so on enable you to tie it into the User Tool native reporting subsystem by designing the presentation specification with Crystal Reports.

This implies having the skill sets to create those Crystal Report definitions.

How it works is very much an extension of the native reporting subsystem. Instead of using the ARR report definition creator, the Crystal Reports (RPT) definitions developed using the Crystal Reports Designer Tool using the supplied Remedy ODBC are placed in the same folder as the ARR reports.

Report execution follows the same sequence in Searching to obtain a Matching / Modify list, then highlighting desired entries to be reported, then invoking the reporting subsystem. Next, with the Crystal RPT definitions in place, instead of using ARR definitions, an RPT definition is selected for presentation. The result launches the Crystal Report Viewer, processes the records against the definitions, and presents them in the viewer.

The resulting presentation allows all the functionality of the Crystal Reports Viewer, including zooming, searching text on the report, printing options, printer selection, saving to other formats such as MS-Word and Excel, and more.

The report designer must use a copy of the Crystal Report Designer Tool and the Remedy ODBC to contact the ARS datastore. In designing the report, the Remedy ODBC used the credentials of the designer; it prompts them for their Remedy login ID and they are stored in the RPT definitions so that the end user doesn’t get prompted.

Under the Remedy ODBC, there is a restriction (at run time, not at design time) that you can only add one “form” to the report as its datasource. It does not allow any form (table) linking; it is as in the User Tool, you only have access to one datasource object. If you attempt to link forms, Crystal Designer will let you. However, at run time, it will fail with an ODBC run time error.

The good new is, you can create Crystal sub-reports on your report using the Remedy ODBC. This effectively gives you a pseudo one level of linking and solves a few normalized data issues. However, you can’t have sub-reports inside sub-reports, by design by the Crystal Designer. So again, you only have a pseudo one level. But that’s one better than zero with the ARR definitions designer.

IMPORTANT: At design time using the Crystal Reports Designer, the report developer is typically testing their definitions by running their report. This uses the Remedy ODBC to pass it’s SQL criteria to return a set of records (a dynaset) and uses it’s local record selection definitions to filter down to the final dynaset before applying the presentation definitions.

When the report definitions are placed and made available to the User Tool native reporting subsystem, the SQL definitions and record selection definitions are ignored.
If you recall the earlier explanation of the native reporting subsystem; it gets it’s record set (dynaset) from the result of Searching a ARS form. This passing of the dynaset is followed here too. The dynaset is handed to the Crystal Report Viewer and the report definitions for finishing the presentation. Sorting and grouping definitions are applied, then the presentation is completed.

Finally, be aware the version of the Crystal Report Viewer bundled in the Remedy User Tool. It has been historically one version less than the current version of Crystal Reports upon Remedy User Tool version release. In creating your own Crystal Report definitions, you probably will have to “Save As” a version which matches the bundled viewer. As a result, some of the some of the latest added Crystal Report features may not be available to you. It takes a pretty advanced developer before this is a make/break issue but it should be known.

Advantages

• Presentation includes all Crystal Reports built-in functionalities, graphs, charts, lists, including pivot tables, pivot charts, imbedded images, colors, fonts, conditional formatting, drill-down reports.
• Central Design and Administration still can be accomplished. If using a shared storage (ie. “file server” environments) with Remedy macros and native report definitions, reports can be centrally designed and deployed without touching the users client workstations after pointing users search path to the shared environment.
• Crystal Sub-reports create a pseudo one level of linking data. This can’t be done with the ARR native report designer.
• Reports can be scheduled, if additional purchase of Seagate Crystal Report product, “Crystal Enterprise” (or third party enterprise scheduler product). Expect additional costs for hardware (servers) to run the Enterprise environment. If installed on your ARS platform, it could affect it’s performance. Also, reports must have proper record selection criteria designed into them because they won’t be invoked through the User Tool.
• Useful alternate Crystal Report output storage formats available, such as HTM, PDF, DOC, RTF and XLS.
• Reports can be text searched, and viewing size can be zoomed in or out.
• Print reports to any printer.

Disadvantages

• Additional Software Costs. At least one copy of Crystal Reports Designer must be purchased.
• Additional Skill sets Required. Someone will need to learn how (or contracted) to design reports using the Crystal Reports Designer.
• Still Performance Issues. Remedy ODBC (at version 5) is slow. Using sub-reports slows report generation further.
• Crystal Reports available to the User Tool native reporting subsystem can’t be scheduled under the User Tool. They can only be invoked by user running the User Tool and performing the steps needed to open a form, search for records, go into report subsystem, select the report formatting definitions, and execute the report.

Using Crystal Reports® Directly Against a Remedy/ARS Datastore

Background

If you have Crystal Reports Designer, or one of it’s derivatives such as Crystal Enterprise Report Designer, you have the option to develop reports outside of the ARS environment by creating your own native access path to your ARS datastore.

Typically, this is with ODBC or MDAC drivers for your ARS database type, such as Oracle, MS-SQL/Server, Sybase, and Informix.

This implies you’ll need some sort of database level authentication (ID and password), which is required in the ODBC or MDAC configuration. You’ll configure your data access in the Crystal Designer Tool and it will become part of your RPT report definitions.

With this method, you are outside the Remedy User Tool environment. You won’t be able to register the RPT report definitions with the User Tool. If you try, they fail at run time because the User Tool is expecting to shove it’s dynaset with the RPT definitions to the Crystal Report View. The result is it gets confused and pukes (returns an error).

So running reports has to come outside of Remedy User environment. You’ll either have to buy copies of Crystal Reports with the “complile to .EXE” capability, letting users then run the EXE (each will need a copy of the ODBC configuration and a database level ID and password), or buy into a Seagate Crystal Enterprise environment or other enterprise scheduler product.

From a designers standpoint, you can design reports like an other SQL reporting environment. You can link/join data to your hearts content. You can slam the datastore and return data as efficiently or in-efficiently with the speed datastore can return the dynasets over the network.

However, this is an Remedy ARS datastore; A “Remedy-ized” database. There are things you need to know if you’re considering reporting directly against the database.

Forms do not translate as Tables in the ARS database; they end up as table “Views”. The actual underlying tables do exist but are cryptic; a convention starting with “T”, “H”, “C”, or “B” followed by a number. There’s even more; certain “Remedy” field types cause a decomposition further into “H” tables (history/diary fields), “C” tables (long character fields), and “B” tables (attachment/blob fields). Therefore, forms are found in the database as Views and some data types are found else ware outside the view. If you need to report against some of the decomposed structure, you’ll have to extra work to determine where they are in the tables.

Going to the table level has draw backs. The number portion of the table name is generated and when a Remedy developer using Remedy Administrator Tool. So, T446 exists because of the order which it was created. This becomes a problem at certain times. Certain developer activities cause the entire table to be dropped and a re-created as a new one with a next available number. This also can happen when upgrading to a new version of ARS engine. This also will happen if you move your ARS application to a different server. So, reports will break, requiring the new table location be set by the report developer.

Menu Selection fields: Are an example of a decomposed field on a form. What is stored on the form is a zero relative number of the menu selection value. The label for the value is decomposed into one of those “T” tables. You always have the option to use a Crystal Reports formula to provide the label. This will change only if the ARS application changes the values on the menu field.

Example Using Crystal Reports Formula Syntax Translating a ARS Menu Field Type.

[“Draft”, “Reported”, “Assigned”, “In Progress”, “Resolved”, “Closed”, “Cancelled”, “Archive”, “Delete”][{INCIDENT.STATUS}+1];

Example Using Crystal Basic Syntax Translating a ARS Menu Field Type..

Value = Choose([INCIDENT.STATUS]+1,”Draft”, ”Reported”, “Assigned”, “In Progress”, “Resolved”, “Closed”, “Cancelled”, “Archive”, “Delete”)

Date, Date/Time Fields: Remedy date storage format is an integer value representing the number of seconds since (or before, a negative integer) an epoch date of January 1, 1970, at 00:00 UTC. Yes, Universal Coordinated Time (UTC). You’ll have to do the math and make the time zone and seasonal adjustments (aka. Daylight Savings) to make it dates meaningful.

The good news is products do exist such as Cogniza’s “UFL Timezone Functions” (www.cogniza.com/products) which eliminate this issue by simply adding some user function libraries (UFL) available to the Crystal Reports Designer.

Example Crystal Syntax Remedy Date Conversion Formula Using Cogniza UFL Timezone Functions:

EpochToLocalDate(“INCIDENT.SUBMIT_DATE”,”US Central Timezone”,”True”)

Advantages

• Performance is as good as your native database driver can provide. This is typically very good performance in reading thousands and even millions of rows of data in reasonable times.

In reality, reports performance screams in comparison to using the Remedy ODBC driver.
• Presentation includes all Crystal Reports built-in functionalities, graphs, charts, lists, including pivot tables, pivot charts, imbedded images, colors, fonts, conditional formatting, drill-down reports.
• Linking/Joining data can be performed, without restriction.
• Reports can be scheduled, if additional purchase of Seagate Crystal Report product, “Crystal Enterprise”. This also typically requires additional hardware (servers) to run the Enterprise environment. If installed on your ARS platform, it could affect it’s performance. Also, reports must have proper record selection critieria designed into them because they won’t be invoked through the User Tool.

Purchasing a third party enterprise scheduler product is another option.

• Useful alternate Crystal Report output storage formats available, such as HTM, PDF, DOC, RTF and XLS.
• Reports can be text searched, and viewing size can be zoomed in or out.
• Print reports to any printer.

Disadvantages

• Removes demand report capability; choosing native access to the ARS datastore means the reports can’t be made available under Remedy User Tool.

However, one alternative is to require person to have execution time version of Crystal Reports or access to a Crystal Enterprise environment (access to the enterprise scheduler product of choice) to demand schedule a report, ie. able to run a desired report right now.

Still another alternative is to use both methods, the Remedy ODBC with Remedy macro stored on a central file server for demand reporting available through the Remedy User Tool, and a native database driver and Crystal Enterprise (or third party enterprise scheduler) for scheduled reporting and analysis.

• Decomposed Remedy Type Fields for History Fields, Long Character Fields, Blob (Attachment) fields, and menu selection fields creates complexity and administration coordination issues between ARS application development and ARS reporting functions/organizations.

• A method for converting date field storage format to presentation will have to be addressed. If dates and times are not depicted accurately, the reporting environment won’t have creditability.

• Slightly more Database Administrator (DBA) activities. Database level id’s and passwords will have to be established and maintained. It is highly recommended database level access used for reporting should be “read-only” to the tables and view objects in the ARS datastore.

Using Microsoft™ Access® Directly Against a Remedy/ARS Datastore

Background

The external data link capabilities built into Microsoft Access makes it also a consideration for extracting and reporting from a Remedy ARS datastore.
Just as using a Crystal Reports with a database level ID and password configured into native driver ODBC or MDAC configuration, MS-Access can be configured through it’s external “link table” to use ODBC and/or MDAC to register tables and views as table objects in the MS-Access database.

Then as in any MS-Access application, the MS-Access query and report designer tools can be used to create demand and scheduled reports.

Even though MS-Access is deemed a personal productivity database, it’s report definition features and expression functions are pretty rich allowing professional looking reports, charts, graphs, lists, pivot tables and pivot charts. Developers skilled in MS-Access report design can design reports against a Remedy datastore.

However, the considerations and caveats follow the same issues as noted in the “Using Crystal Reports Directly Against a Remedy Datastore” section earlier. To summarize…

• Data can be linked/joined.
• Forms are seen as database Views.
• Table decomposition for History/Diary Fields, Long Character, and Blobs must be addressed.
• A Date and Time conversion method will have to be addressed. This issue can be easily eliminated with Cogniza’s “VB Timezone Functions” (www.cogniza.com/products).

• Menu selection fields can either be accomplished by tracing down the “T” tables for their values or using an MS-Access expression language such as the following example…

Choose([INCIDENT.STATUS]+1,”Draft”, ”Reported”, “Assigned”, “In Progress”, “Resolved”, “Closed”, “Cancelled”, “Archive”, “Delete”)

MS-Access environment lends itself easily to demand type of reporting, but not to scheduled generation of reporting.

However, scheduled report generation can be accomplished by combining MS-Windows Task Scheduler to run MS-Access reports and queries against your ARS based datastore.

Keep in mind, MS-Task Scheduler has limited functionality and maturity in comparison to enterprise scheduling products. The setup can be accomplished but the administrative burden in setup and maintenance is higher than enterprise scheduler products. MS-Task Scheduler is bundled in the cost of your Microsoft Windows operating system so to most organizations, it appears to be free.

Advantages

• Performance is as good as your native database driver can provide which is still much better than the provided Remedy ODBC driver.
• Linking/Joining data can be performed, without restriction.
• All rich features and functionalities in Crystal Reports presentation are available.
• Reports can be scheduled, if willing to support the administrative burden of MS Task Scheduler or purchase of an enterprise scheduler product.
• MS-Access querying and reporting skills are prevalent in the business community. They can be easily taught; many companies have internal MS-Access training programs. Even the most basic MS-Access querying and reporting training could allow users to generate professional looking reports, charts, graphs from a ARS datastore.

Disadvantages

• You again loose the demand report capability under the Remedy User Tool; Choosing native database access to the ARS datastore means those reports can’t be made available under Remedy User Tool. The users will have to get used to running demand reports from the designed MS-Access application.
• Decomposed Remedy Type Fields for History Fields, Long Character Fields, Blob (Attachment) fields, and menu selection fields creates complexity and administration coordination issues between ARS application development and ARS reporting functions/organizations.
• A method for converting date field storage format to presentation will have to be addressed. If dates and times are not depicted accurately, the reporting environment won’t have creditability.
• Slightly more Database Administrator (DBA) activities. Database level id’s and passwords will have to be established and maintained.

IMPORTANT: It is highly recommended database level access used for reporting should be “read-only” to the tables and view objects in the ARS datastore. This is especially important in an MS-Access environment so that the ARS database can not be written to externally. Writing on an ARS datastore outside the ARS server environment can corrupt the ARS database.

Conclusion

“Choose your evil”! Or should it be said more positively as, “Choose your angel”.

You can see there are many options and many considerations. Hopefully this paper helped enlighten a pathway to getting at your additional value from your ARS based applications because only you know your organization’s desires, direction, skill sets, budgets, restrictions, tolerance for risk, innovations, and goals.

Maybe you’ve found both short term ideas which can start applied today along with options to move to a long term ARS reporting strategy.

We hope this information has helped you extend your Remedy/ARS investment in your organization.

6 thoughts on “Considerations For Reporting Against Remedy ARS Based Applications

  1. fconill

    awesome. took many years of experience to understand all of the implications discussed in this posting.

  2. mangaj316

    I was searching the web hoping to find information on my “odd problem”.
    I came across this web site that really did a great job of explaining the ARS reporting structure.
    I’m hoping behind that article is a person who might not the answer to my question:

    Does anyone know how to let Remedy User Clients run the existing embedded Crystal Reports within Remedy, BUT not allow them to use the ARS ODBC on their own to access the database directly with report writers and Excel? I know you can block ODBC access but this Remedy config also blocks the ability to run the embedded Crystal Reports. Our problem is that smart users are using the ARS ODBC to make connections to the production database. Since the ARS ODBC doesn’t need a user to have a database ID/PW, they are in just using their ARS LOGIN.

Leave a Reply

Your email address will not be published. Required fields are marked *