An Excel-based DQL Query Tool

One of the items on my personal to do list was to create a simple DQL query tool using Excel.  To me, it seemed like a natural fit:  Excel was adept at displaying tabular data, it had a robust scripting language to implement the logic (VBA), and VBA was dot-Net-compliant so it could load the DFC PIA directly.  The only problem is that I waited too long to create this tool and the DFC PIA is no longer distributed or supported by EMC.

Instead of using the DFC PIA, I discovered Obba — a framework for integrating Java with spreadsheets, both Excel and Open Office.  It is a little “heavier” solution than I had hoped to create but useful and interesting nonetheless. I had envisioned the DQL Query Tool as more of an Excel Add-in or something as portable.  Obba launches a small, local, Java-based server to handle the integration instead of handling the DFC calls internally as the PIA would have.

You can download the Excel DQL Query Tool here  if you are interested.  I found that VBA/Obba programming takes a little getting used to in the way it handles variables, objects, and methods, but after a few examples it makes sense.

For details regarding installing and running Obba, see the Obba website (www.obba.info). For details regarding configuring and running the Excel query tool, see the README file included in the download.

Query Screen

Query Screen

Results Screen

Results Screen

Next, I think I will pursue creating a tool using the DFS RESTful services.  For this interface I will likely use Excel-REST.  Hopefully that integration will prove to be a little “lighter weight” than this one.

Advertisements

About Scott
I have been implementing Documentum solutions since 1997. In 2005, I published a book about developing Documentum solutions for the Documentum Desktop Client (ISBN 0595339689). In 2010, I began this blog as a record of interesting and (hopefully) helpful bits of information related to Documentum, and as a creative outlet.

14 Responses to An Excel-based DQL Query Tool

  1. Marcel says:

    Your link to Obba has a single “b”. Please correct.

    Like

  2. Ilya says:

    Good idea! Could you help me with your tool? When I pushing any button on Query sheet it returns error like “…bla-bla can’t execute macros Obba.xla! bla-bla…”. For example, for “Logon” button it look like “… Obba.xla!DctmInit …”.
    May be you placed you custom macros for Documentum in Obba.xla application? But how we can try your tool? Or am I confusing you?

    Like

    • Scott says:

      Make sure you have Excel configured to allow macros, and do the Obba install before you open the spreadsheet.

      Like

      • Jesus says:

        Hello,
        Thanks for sharing your knowledge, please help with your tool I’ve followed all the steps but I get the following error: Can not run the macro ” Can Obba.xla DctmInit the macro is not available in this book! . .

        Please support , I have my excel configured to allow all macros , first open the DCTM -query- tool.xlsx file and then run the RunExcelQueryTool.bat .
        Diagnosis by running the file – Obba for Excel.xls everything works fine. Report attached:

        Test Result Status

        Client 1 OK OK

        Client 2 OK OK

        Client 3 OK OK

        Server 1 OK OK

        Server 2 true LOCAL

        Server 3 4.2.2 Version

        Server 4 1.8.0_101 Version

        Java Test 1 1 OK

        Java Test 2 2 OK

        Thank you

        Like

  3. Pingback: Links to All of My Source Code | dm_misc: Miscellaneous Documentum Information

  4. shodan says:

    Hello,
    The link for this DQL tool is dead !
    Could you post a new one ?

    We have a bunch of documented servers here and the web interface is painfully slow to navigate. I’m hoping to make an excel user interface to get right to the goods in one click for our users.
    And I have to do this without needing admin rights on my PC since IT wants huge $$$$$$$$$$$$ for the slightest request !

    Like

    • Scott says:

      I just checked and the link is live for me: https://app.box.com/s/epz1mds965atolaffbpe. This page gives a warning that no preview is available for the documentum.zip file, but if you click the Download button below the warning the file downloads just fine. Let me know if you still have trouble and I can email it directly to you.
      Note that you must download and install Obba before the Excel DQL tool will work.

      Like

  5. Jesus says:

    Hello,
    Thanks for sharing your knowledge, please help with your tool I’ve followed all the steps but I get the following error: Can not run the macro ” Can Obba.xla DctmInit the macro is not available in this book! . .

    Please support , I have my excel configured to allow all macros , first open the DCTM -query- tool.xlsx file and then run the RunExcelQueryTool.bat .
    Diagnosis by running the file – Obba for Excel.xls everything works fine. Report attached:

    Test Result Status

    Client 1 OK OK

    Client 2 OK OK

    Client 3 OK OK

    Server 1 OK OK

    Server 2 true LOCAL

    Server 3 4.2.2 Version

    Server 4 1.8.0_101 Version

    Java Test 1 1 OK

    Java Test 2 2 OK

    Like

  6. Jesus says:

    Hello Mr. Scott ,
    Thank you very much for all your lessons left until then I ignored Documentum until this year.
    I am now using the tool documentun and need your support.
    Please contact me at my email :
    escorpio_287@hotmail.com ,

    The consultation is the DQL query tool based on Excel.
    My question eds the next.

    1. I have to install the DFC on my desktop PC to connect to remote server Content . ????
    2. Where the obba set up to connect to a remote server

    Like

    • Scott says:

      Let me take a closer look at this. I recently set up a new laptop without Obba or this Excel DQL Query Tool, so it will be a good test to see if I can follow my own instructions. Stay tuned…

      To quickly answer you questions: 1) Yes, the DFC must be installed locally. You might be able to get around this with some clever path variables, but I haven’t tried. 2) the ObbaClient.properties file contains a pointer to the DFC.config file which indicates the location of the DCTM server.

      Like

    • Scott says:

      I am sad to report that I no longer have the source code for the Documentum Excel Query Tool. The Excel spreadsheet contained in the download archive on my blog does not contain the Documentum-specific code to interface Obba with the Content Server. I don’t know how this code was omitted from the archive since it was VBA code attached to the Obba macro sheet in the spreadsheet. Nonetheless, I no longer have it. It wasn’t too difficult to create and you have the framework in the spreadsheet if someone wants to take this task on. Again, I apologize for my sloppy configuration management.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: