Tuesday, August 19, 2008

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

15 comments:

Ahmed Ibrahim said...

A greate effort that will make Documentum reporting very easy

Many thanks

ukdavo said...

Nice one. It's great to see more people making Documentum related software available for all.

How does this compare with Documentum Reporting Services (I've not used this either)? How does SQL Server Reporting Services deal with repeating attributes?

Regards

Mosallem said...

I haven't used Documentum Reporting Services either, but i know that it uses the Crystal reports engine.
Regarding the repeating attribute, currently the value at index 0 is displayed but i plan to change this to display all the values in the attribute

JB said...

You did a great job.
The download link is broken.
Would U send me the DqlDataExtension.zip?

Regards.

Mosallem said...

Thanks Jb,
the link is working fine, but you can send me your email and i will send the extension to you

Zuleika said...

Interesting to know.

Jeff said...

Mohamed,

Great effort on the driver. I seem to be having issues although when previewing the report in the designer. It is telling me that it cannot connect to the datasource, and that it is unable to connect to the docbase, please check your credentials. Any help would be appreciated.

Cheers,
Jeff

Mosallem said...

Jeff,
If you followed the steps correctly every thing should be working, may be there's something wrong with your DFC installation

Jeff said...

The weirdest part is that it will let me run the DQL when building a query for the report, but fails to return the dataset on the report :(

vuglll said...

Will this extension work with MS SQL Server Reporting Services 2008 R2 ?

Mosallem said...

For more information about this Extension please contact www.EnterpriseFabric.com

Rami Jum'ah said...

Dears, Where i can find this extention to download, the current link is broken, Kindly, Advice.
My Email is : mrrami.76@gmail.com

MAHMOUD said...

You did a great job.
The download link is broken.
Would U send me the DqlDataExtension.zip?

Regards.

Foni Kumar said...

Great job,but link is broken.can you kindly email the extension to debashismekap@gmail.com

Foni Kumar said...

Great job,but link is broken.can you kindly email the extension to debashismekap@gmail.com