8/12/2015

SQL Server ODBC Driver Connection to Apache Open Office Calc Pivot Table Example

In order for Apache Open Office Spreadsheet to connect to a SQL Server database, the first step is to install the driver for SQL Server:

https://msdn.microsoft.com/en-us/data/aa937724.aspx

Download... 

http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774




Download...Unzip...



WINDOWS
-------
1. Read the license.txt file. Downloading and using the Microsoft JDBC Driver 4.0 for SQL Server signifies acceptance of this license.

2. Download the sqljdbc__enu.exe to a temporary directory.

3. Run sqljdbc__enu.exe

4. Enter an install directory when prompted; it is recommended that you unpack
   this zip file in %ProgramFiles% with the default directory:

    Microsoft JDBC DRIVER 4.0 for SQL Server

5. After the package unpacks, open the JDBC Help System by opening
   %InstallationDirectory%\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_\\
   help\default.htm. This will display the help system in your Web browser.


From Open Office Spreadsheet, goto File --> New --> Database --> from the "Connect to an existing Database", select that, and you will now see the ODBC option, which wasn't there before:



Next.. select ODBC (pre-existing or create a new one)...

Next... enter UserId, check the box for requires password... next..



Create a name to register the new database...save file to server... it opens...



Next, created a view for the data...



Create a Pivot Table...


Select the Data Source registered in OpenOffice...


 Select database... and view or table...



 Well, at this point, the View I selected caused the app to crash, and it had to recover the files when re-opened.  So instead, for proof of concept, i chose a simple table with 3 rows, a Dimension table.


Here you can see the Pivot Table with option to select fields...



And some more options...




And here's the simple Pivot Table in action...




Well, it's working.  From there, you have the option to refresh or "Edit Layout".

And filter selection pop up windows...





And there you have it.  Connecting to a SQL Server database from Apache Open Office using ODBC driver.

Thanks for reading!





No comments:

Post a Comment

Bloom Consulting Since Year 2000