Documentum Reporting Gateway

Introduction

The Documentum Reporting Gateway is a that tool enables access to Documentum data
through an ODBC interface. The Documentum Reporting Gateway works with report writers like
Crystal Reports and Business Objects. It also works with other Microsoft applications that
access ODBC Data Sources like MS Access, MS Excel, MS Query etc. These applications allow the
user to directly query the Documentum tables by just linking to the ODBC Data.

This article describes installing the Reporting Gateway and provides some simple examples of using
it to access and modify data using Microsoft applications.

I found it was fairly easy to get the Documentum Reporting Gateway installed. Reporting Gateway was equally easy to
use. Reports are limited only by one’s experience with and the limitations of the reporting applications the Documentum
data is being fed into. However, as the Documentum Reporting Gateway is purely a reporting tool, you cannot use it to modify, add,
or delete data in the docbase you are connecting to.

Installing the Reporting Gateway

In order to utilize Documentum Reporting Gateway you will need to have a reporting software application installed. The following are supported:

• Crystal Reports Version 6.0 and 7.0
• Microsoft Access Version 7.0
• Microsoft Excel Version 7.0 (with Microsoft Query installed)
• Microsoft Query Version 7.0
• Business Objects 4.1.5 and 5.0
• ODBC test application (included with Open Database Connectivity (ODBC) Software Development Kit (SDK))

Another requirement is that one of the following cases is met:

• All core ODBC components are installed on the machine in which you are installing Reporting Gateway.
• No core ODBC components are installed on the machine in which you are installing Reporting Gateway.

By core components I believe Documentum is referring to the ODBC Core components that are provided by
Microsoft. These include things such as the ODBC Administrator and the ODBC Driver Manager. These components are also referred to as the Microsoft Data Access Components.

I have never encountered the case where I wasn’t compliant with either of these two cases, so I can’t offer any insight on what to do if you are not.

The actual installation is straightforward and relatively simple as far as installations of Documentum products go. The only parts of the installation requiring
any input from you are where you need to enter your license key and where you create the Data Source for the docbase you are reporting from.
If you get stuck with the latter, feel free to post a question to www.dmdeveloper.com.

Part of the Installation involves defining the datasource for the Reporting Gateway. You can create additional datasources at any time after the ReportingGateway is installed.

The steps to do this are as follows:

• Settings->Control Panel->ODBC Data Sources
  The following dialog appears:

  

  
  Figure 1 (Click to Enlarge)

  As in the above Figure, you want to choose “Documentum Driver”

• After selecting the Documentum Driver, the dialog in figure 2.0 appears:

  

  
  Figure 2 (Click to Enlarge)

  Enter a name for the Datasource,a description of the Datasource, and the docbase you want to connect the ODBC datasource to.

As mentioned earlier, with the Documentum Reporting Gateway, you can use a number of different report building applications
as well as MS Access and MS Excel to access data stored in a Documentum Docbase and produce reports. This article will only describe using
Reporting Gateway with MS Access and MS Excel.

Creating Reports Using MS Excel

• In order to create reports with MS Excel you need to have Microsoft Query installed. Launch MS Excel and
  verify Microsoft Query is installed by choosing Data->Get External
  Data->Create New Query.
• If Microsoft Query is installed, Microsoft Query now launches.
• The Choose Data Source dialog appears.
• Select the data source that corresponds to the Documentum ODBC Data Source you created at install. If you cannot recall what you named the data source, just go to
  Settings->Control Panel->ODBC Data Sources and find it.

  Note: In the Choose Data Source dialog you can check/uncheck “Query Wizard to create/edit queries”. I typically use the wizard.

  You will be prompted for a username/password to connect MS Excel to the docbase defined as the ODBC data source. Enter it.

  

  
  Figure 3 (Click to Enlarge)

• Next you see “Query Wizard – Choose Columns” dialog where you are presented with a list of Document Object Sources (tables, views, etc) to build your report query from.
  You can reduce what you see here by clicking options and checking/unchecking the appropriate selections. I have found that if only want to query against my custom types,
  I select check Tables from the Table Options dialog. This cuts down on some of the noise.

  

  
  Figure 4 (Click to Enlarge)

• At this point you can select the object you want to query or expand the object and select only the columns of that object you are interested in.
• Apply Filters to the data and sorting options as needed and Execute your query.

  
  Figure 5 (Click to Enlarge)

   

  

  
  Figure 6 (Click to Enlarge)

• The Documentum data will be loaded into Excel spreadsheet.

  

  
  Figure 7 (Click to Enlarge)

Using MS Access with Documentum Reporting Gateway

To import data from a Docbase type or registered table into an Access
database:

• Create a new database or open an existing database in Microsoft Access.
• From the <datatabsename>:Database dialog, choose New
• The New Table dialog box appears.
• Choose Import Table.

  

  
  Figure 8 (Click to Enlarge)

• The Import dialog box appears.

  

  
  Figure 9 (Click to Enlarge)

• Choose ODBC Databases from the “Files Of Type” drop-down list at the bottom of the Import dialog.
• In the Select DataSource dialog box, choose a Documentum data source and
  click OK.
  The login dialog box appears.

  

  
  Figure 10 (Click to Enlarge)

• Type in a Docbase username and password to connect to the Docbase.
• Select a type or registered table and click OK.

  

  
  Figure 11 (Click to Enlarge)

  The object type/registered table you selected is imported and appears in <datatabsename>:Database dialog.
  Select it to view the data object type/registered table data.

Link to a Docbase type Using Microsoft Access

Using Microsoft Acess and the Documentum Reporting Gateway, you can link to a Docbase Object type or registered table.
Linking data enables you to read data in the Docbase without importing. The
type or registered table still resides in the Docbase.

To link to a Docbase type or registered table:

• Create a new database or open an existing database in Microsoft Access.
• From the <datatabsename>:Database dialog, choose New
• The New Table dialog box appears.
• Click Link Table. The Link dialog box appears

  

  
  Figure 12 (Click to Enlarge)

• Choose ODBC Databases from the “Files Of Type” drop-down list at the bottom of the Link Table dialog.
• In the Select DataSource dialog box, choose a Documentum data source and
  click OK.
  The login dialog box appears.

  

  
  Figure 13 (Click to Enlarge)

• Type in a Docbase username and password to connect to the Docbase.
  The Import Objects dialog box appears, displaying the types and registered
  tables in the Docbase.
• Select a type or registered table and click OK.

  

  
  Figure 14 (Click to Enlarge)

• The Select Unique record identifier dialog box appears, displaying the
  attributes of the selected type or registered table.

  

  
  Figure 15 (Click to Enlarge)

• Select the attribute that uniquely identifies the Docbase object. For Documentum Objects select r_object_id.
• For registered tables, select the attribute that uniquely identifies the row in
  the registered table (the attribute used as the primary key).

Creating Microsoft Access Reports and Displaying Docbase Data with Access Forms

You can use Microsoft Access forms to easily view data in a Docbase table and
create Microsoft Access reports for the Docbase objects. In order to do this your Access
database must contain linked or imported Docbase type or registered tables. I am
only going to describe building Reports. If you need more information on creating
Access Reports, see Documentum ÆReporting Gateway Installation and Release Notes

To create a report using Reporting Gateway:

• Create a new database or open an existing database in Microsoft Access.
• Choose the Reports property page from the left side of <datatabsename>:Database dialog.
• Select “Create reports by using Wizard”. The Report Wizard dialog box appears.

  

  
  Figure 16 (Click to Enlarge)

• In the Report Wizard dialog, choose a Documentum Object/Documentum Registered Table to generate the report from.
  from Tables/Queries pulldown. If you haven’t linked to or imported from a Documentum Object type or registered table yet, you will need to do so before
  you can create a report.

  

  
  Figure 17 (Click to Enlarge)

• Choose the attributes from the Available Fields list.
• Proceed through the Report Wizard to sort, format, print, etc. your report

  

  
  Figure 18 (Click to Enlarge)

Reporting Gateway Limitations and Things to Know

• You cannot choose multiple Docbase tables for a single report because the
  Reporting Gateway does not allow joining of types.
• In MS Access, The Documentum system types are not listed by default. To be able to view the
  Documentum System Objects:
  1. Create a new database or open an existing database.
  2. Choose Tools->Options. The Options dialog box appears.
  3. Select the View property page.
  4. Check the System objects checkbox then click OK.
• Repeating Attributes: If you are familiar with DQL you know that in order to use a repeating attribute in a query
  condition you use the keyword ‘ANY’ (i.e. where any r_version_label = ‘1.3’). ANY is not supported
  in MS Access query. However, querying with a the repeating attribute condition (where r_version_label = ‘1.3’)
  returns all records that have it as one of the repeating attribute values.
• Querying Non-Current Object Versions: I was unable to query/load older versions of an object using the Documentum Reporting Gateway. I don’t know
  this can be done. I believe you can only get information on the current versions of Documentum Objects.
• Dates: In both MS Excel and MS Access, I was seeing my dates returned as 10/16/1901 11:21:02 AM which obviously isn’t correct.
  To correct this I needed to correct my Date settings for my machine. I was able to this this my going to
  Settings->Control Panel->Regional Settings and changing the value in the Date tab to represent the year as four characters
  (i.e. MM/DD/YYYY)