How to I create a report with ApplicationXtender data?

What data is available for reporting?


The index tables and associated lookups, batch list table, and audit table have all been defined to both the ODS and Cognos in the Docmgmt_Wkflow flow package.


The naming convention for the index tables views is: a prefix of DM, underscore, the application identifier, underscore, and then INDEXES:



The Index Table views use the same social security number masking as other Cognos applications. Unless you have been given permission, asterisks will display instead of the social security number when the data is displayed on a report.


Index views will contain the application number (APPLICATION_ID), the application identifier (APPLICATION) and the description of the application (APPLICATION_DESC). It will also contain all the index fields defined to the application, a unique document identifier (DOCID), and a number of objects field (NUMOBJECTS) which essentially equals the number of pages in the document.





It would be important for you to remember that if a document is deleted the associated information is also deleted from the index table and therefore it is no longer available for reporting.


The batch list view contains data from the batches in all the applications.





The audit table keeps data for only five years. The data captured by the audit table is application specific. If you view the contents you will see data for system changes like adding users or groups. Our implementation standard is to have the audit trail turned on for all delete activities. Here's an idea of events that are captured:





Security for the document management view is the same as that for all Cognos:


  • Normal View users can only access the views in Cognos.
  • Power users can link to the views in the ODS as well as access the views in Cognos.


Security for the index tables is consistent with application packages:


  • Finance users can access DM_B_F* views.
  • HR users can access DM_B_H* views.
  • Student users can access DM_B_S* views.
  • And so on.


There is no special security for the Xtender batch list view.


Security to the audit view (DM_Audit) is granted by application owners to a select few individuals.


Queries from the index tables will not impose any document-level security that exists in Xtender. Users are using the Cognos tool, and they are only querying a copy of index data, not the actual documents themselves.


Standard Reports


Some standard certified reports have been developed to support document management. These are located in Public Folders>Document Management Reports>Standard Certified Reports.





There are a set of standard certified reports that were developed specifically for people who need to manage their areas. These include:




Creating New Audit Reports


For this example we will create an audit report of documents added in B-S-ID and we want to know who indexed them.


If you want to write a report that contains data from the audit report view as well as data from an index table view such as DM_B_S_ID_INDEXES Indexes you will want to add a filter to make sure the query only selects audit records for the application you are joining to so it runs faster:






Select fields that have data from both DM_AUDIT and DM_B_S_ID_INDEXES.





The relationship between these two views has been created behind the scenes. If you view the generated sql for the query, you will see behind the scenes the outer join definition has been created for you.





 Here's an example of the report output (notice SSN is all asterisks because SSN masking has been imposed):