A picture is worth a thousand words! (Part I)

Hi.

Let’s see - you have Sales, you have Profit & Loss and, most important, you have a boss who does not really care about how you choose to keep the company data in your CUBRID database – all he wants to see is Figures and Charts...

...So what's an easy way to get out the boring data as nice and easy to understand images?

Well, it’s not really that complicated - in the end, it’s all about mixing Excel with CUBRID!

For simplicity, let’s assume we have only 2 Sales tables in our database – Sales_People and Sales_Figures:

First step – let’s be smart – let’s create a VIEW (we only need to perform data query, no updates here):

CREATE VIEW Sales_Data AS

SELECT a.name, b.sales_date, b.amount

FROM sales_people a, sales_figures b

WHERE a.id = b.sales_people_id

ORDER BY b.sales_date AS

Now, how do we link CUBRID with Excel?

Simple as that, we have 2 solutions:

• Use a hard-coded but more powerful VBA macro approach
• Use the standard out-of-the-box Excel support for OLE DB and/or ODBC

The key thing for both approaches is to know that CUBRID provides both OLE DB and ODBC drivers out-of-the-box.

Let’s take a look first at the option of writing some VBA code by ourselves…

First we need to get the CUBRID data into an Excel worksheet. We will start by creating a new Excel VBA project/macro:

Next, we need to add a reference to the CUBRID OLE DB Type library:

Finally, here is some basic VBA code that does extract the Sales data from the CUBRID database and populates the worksheet:

Sub cubrid()

  Set conn = CreateObject("ADODB.Connection")

  Set rs = CreateObject("ADODB.Recordset")

  strConn = "Provider=CUBRID.OLEDBProvider;Location=localhost;Data Source=demodb;User Id=public;Port=33000"

 

  conn.Open strConn

 

  If conn.State <> 1 Then

    MsgBox "Sorry. No CUBRID today!"

    Exit Sub

  End If

  

  Set rs = conn.Execute("select name, sum(amount) AS amount from sales_data group by name")

 

  With Worksheets(1).Cells

    .ClearContents

    .CopyFromRecordset rs

  End With

  conn.Close

End Sub

And here is the result (of course, after adding also the chart we want, but this is an easy step):

Oh, one last piece of the puzzle – how to refresh data easily…? Well, nothing special in there – all you need to do is to add a customized button to the Excel ribbon, a button your can press anytime to have the data refreshed.

How exactly do you do that? I will let you figure out this one for yourself – here is the only tip you will need)

The 2nd part of the post will be published soon - keep an eye on the blog!

See you next time – Bye-bye! :)

P.S. Do you know who first said that “a picture is worth a thousand words”…?