(401) 515-5115 moc.sbalnrubkcalb@ofni

In this post I will explain how I went about using a Google Docs spread sheet as a Crystal Reports 2008 data source.

Hello Again Everyone,
My friend over at data-ink.com recently got me interested in Crystal Reports and Dashboard. So I have been playing around with it to learn it better.

Of course to learn something new you need to have a starting point and for this I needed a basic datasource to play around with. I have a few MySQL DBs, and these worked great at first, but quickly I found I wanted to try some things which I did not have an applicable DB available for without create a new one with fake data. My initial thought was to just set up some local CSV files to play with. But then I thought, why not have some real fun, and see if I could use Google Docs as a data source.

My initial research almost led me to believe this was not going to be possible, then I stumbling across this little gem, gdocjdbc. With a little playing I was able to leverage this to use my Google Docs spreadsheets as Crystal Reports datasources. I’ll walk through the basic steps here for you.

First you’ll of course need to install Crystal Reports 2008 if you do not already have it. If your new to Crystal Reports, SAP offers a 30 day free trial.

Now, you need to download the Google Docs JDBC driver, and unpack it to a temporary location. This will have a gdocjdbc-0_01.jar file and a “lib” folder with the following JAR files:

  • gdata-client-1.0.jar
  • gdata-core-1.0.jar
  • gdata-docs-3.0.jar
  • gdata-media-1.0.jar
  • gdata-spreadsheet-3.0.jar
  • google-collect-1.0-rc1.jar
  • hsqldb.jar
  • junit.jar

Move all these JARs and the gdocjdbc-0_01.jar to this folder:
C:\Program Files\Business Objects\Common\4.0\java\lib
(Note: If you are running a 64-bit version of windows, this will be C:\Program Files (x86)\Business Objects\Common\4.0\java\lib)

Now open the C:\Program Files\Business Objects\Common\4.0\java\CRConfig.xml file with any plain text editor. Find the XML node. This is a semicolon delimited list of classpaths. Add the following calsspaths to this value:

C:\Program Files\Business Objects\Common\4.0\java/lib/junit.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/hsqldb.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/gdata-client-1.0.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/gdata-core-1.0.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/gdata-docs-3.0.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/gdata-media-1.0.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/gdata-spreadsheet-3.0.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/gdocjdbc-0_01.jar;C:\Program Files\Business Objects\Common\4.0\java/lib/google-collect-1.0-rc1.jar;

I’ll be honest, you might only need to add “C:\Program Files\Business Objects\Common\4.0\java/lib/gdocjdbc-0_01.jar” to this list. I am not sure if you need them all, but I figured better safe then sorry, so just added them all.

You are now ready to start Crystal Reports 2008.

Once you have Crystal Reports 2008 started create a new report (“File” > “New” > “Blank Report…”). When it prompts you to select a datasource, double click “JDBC (JNDI)” under “Create New Connection”. In the prompt, select “JDBC Connection” and provide the follow values:

  • Connection URL: jdbc:gdocjdbc
  • Database Classname: org.gdocjdbc.jdbcDriver

Feel free to give it any connection name you like.

Crystal Reports Screen Prompt #1 for Google Docs JDBC connection

Screen 1 of setting up a Crystal Reports Google Docs JDBC connection

Click “Next” and you will be prompted to supply your Google Account information.

Crystal Reports Screen Prompt #2 for Google Docs JDBC connection

Screen 2 of setting up a Crystal Reports Google Docs JDBC connection

Click “Finish” and you should now be connected. Under the connection you should be able to find a table for each tab in each spreadsheet in your Google Docs account.

This seems to have some trouble joining two tables together at times. Creating a report based on one table seems to function fine, but when two or more tables are involved it seems it is hit or miss at times. If I figure out how to resolve this I will update this post, or maybe my friends at Cleartelligence will be able to answer this mystery.