Documentum DQL Query Custom Data Extension For SQL Server Reporting Services

When it comes to Reporting SQL Reporting Services is my favorite choice.

SQL Reporting Services has a plenty of data source types but unfortunately Documentum is not one of them, whenever we have to develop some reports based on the data in Documentum we first develop a web service that contains the necessary DQL queries and returns the result in a Data Set, then we create an XML data source that consumes this web service and use it in our reports.

Fortunately SQL Reporting Services allows you to extend its data access capabilities by developing a custom data extension (CDE)that retrieves data from your custom data source.

I developed a Custom Data Extension for Documentum that allows you to specify a Documentum Repository as a data source and provide your DQL query that will populate your report, I used the Reporting Services SDK Sample (FsiDataExtension) and this Article as starting points

You can download the extension from here

To install the extension:

you have to install the extension both on the report designer machine and the report server machine:

• first install the DqlDataExtension assembly into the GAC (by simply dropping the file in the "C:\windows\assembly" folder)
  
• on the report designer machine open the file "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\RSReportDesigner.config", locate the <Data> element, Add the following line after the last <Extension> element in the <Data> section.

<Extension Name="DQL"
Type="DqlDataExtension.DqlConnection,DqlDataExtension,
Version=1.0.0.0, Culture=neutral,
PublicKeyToken=4fc4d3cd73258ff6"/>

• on the report server machine open the file "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config", locate the <Data> element and register the custom extension just like you did with the Report Designer configuration file.

To test the extension:

• Create a new SQL Reporting Services project
  
• Add a new shared data source, select the type of the data source to be "Documentum DQL Query", set the connection string to be "docbase=YOUR_DOCBASE_NAME;username=USER_NAME; password=PASSWORD;"
  
• On the credentials tab set the credentials to No Credentials
  
• Create a new report by selecting the shared data source you just created, in the Query string enter your DQL query, you can include parameters in the query string the same way you do in SQL queries, for example you can write the following
  

Select * from dm_document where  owner_name=@Owner 

• select the report layout, the columns to display and finish the wizard
  
• You can work normally with the report as you do with any SQL Reporting Services report
  
• When you deploy the report to the report server, you have to remove the username and password from the connection string and move them to be stored securely on the report server