Tutorial Notes of External Data Connection

Author's MSN and Email: qinzhe_wt@hotmail.com

Overview

In an enterprise, there might be many existed softwear such as OA, ERP, BI system etc. Each softwear have own database. However, in some case, the data from those database

can not be shared and communicated. In order to figure out the isolated information issue, BC Excel Server 2008 v8.3 have provided a interface to import, integrate and operate the

external data (for BC Excel Server, the database from the other system might be considered as an external data). BC Excel Server currently supports the following external database

source: MS SQL Server, Oracle, Access, Sybase, Mysql, Infomix.

There are three conditions of employing the external data:

1) You are expected to hold the permission of administrator.

2)You are expected to get know well of the location of the external data source and visit manner

3)You are expected to understand the table and the field you need in external data source

Case study

To explain the processing, we will employ an actually exsited table “StaffDetail_Main” ( see Diagram 1.1) of  database “ ESSample” which is the sample database of Excel server as

the external data. In this case, there is an assumption that we have already known about the structure, meaning and location of the data (Field—“Date” “Name” “Age” “Nationality”

“Subject” “Salary”) in the StaffDetail_Main of the database “ESSample”.

The job we need to do in this processing is to connect the table “StaffDetail_Main” with Excel Server and import the data of the field “Name” “Age” “Nationality” “Subject” “Salary” to

our new report ( see the Diagram 1.2 ). The actual implementment is that build a new query operation in our new report and export the data from external table “StaffDetail_Main” to

our new query.

    1.1 External table StaffDetail_Main is under the database “ESSample”.

                                                                1.2  New report for quering the external data

Detail of Report   It is the new simple report that showing the information of Cesoft. The main function area for inputting the external data is in the area of “Staff Info” of the

                            template. The definition of the data is below:

                                                                                                         1.3 Single Data & Multiple Data

  Detail operation in the case:  once user choose the period (from when to when) that a saff register in the Cesoft company, then system will feedback the data into the field “Name”

                                               “Age” “Nationality” “Subject” “Salary” from external table.

Implementation

Step One: Import the External Data to BC Excel Server

1)     Open the ES Management Console, click “New” on the “External Data” section.

                     2.1 Add the new external data

2)  Marking the data source name. We used “TestingExternalData” for example. Then choose the data source type and his location. His database name in the server will be

     requested as well.

     Note: The database name must be exactly the same as the one in the database server, otherwise, system will not find the source.

                                                                          2.2  Import the external data

3)  Once you successfully connect with your external database into Excel Server, all the table and view in that database will be showed up. Add the table you want to Excel Server. In

     this example, the external table “StaffDetail_Main” has been successfully imported. (See the diagram 1.6)

                                                                               2.3  Successfully import the external table

4)  The field in the external table “StaffDetail_Main” has been showed up in the “Data Table Information” window. It is easy to find out the field we want to use in this example:

     “Date” “Name” “Age” “Nationality” “Subject””Salary”( you could ignor the other fields ). You will be allowed to modify the “Display Column Name”, it just makes the thing easier.

                                                                             2.4  Information of external data

5)  The permission setting can be set up as below

Query: desiner can query the external data

Write: designer can write (update) the external data

                                     2.5  Permission setting

Step Two: Create the new template

1)       Make a new report of template

 3.1  Add new report

2).  Define the ID and Name for the new report

                3.2 Setting new report--general information

3).  Define the write permission for each “Role”.

                                 3.3  Setting new report—write permission

Define the read permission for each “Role”.

                                 3.4  Setting new report—read permission

4)  Design the shape of the template like below.

                                      3.5  New template shape

5)  Creating a new table “InformationOfCesoft_Main” Define the single data field for the fields of “Register from” and “ To”

      3.6.1   Define single field

                                   3.6.1   The data type of single field

6)  Creating a new table “InformationOfCesoft_Detail” and defining the multiple data field for the fields of “Name” “Age” “Nationality” “Subject” “Salary”

                                  3.7.1   Define multiple field

                                3.7.2   The data type of multiple field

Note: Do not forget to tick off the check box “ Downward Expandable” if the number of the row for your data inputing is more than the one you designed in template

                                3.7.3   Setting in row’s Downward Expandable

7)  A. Define the data fetcher. It is a kind of operation of database.

                                                        3.8.1   Define data fetcher

B. Select the button “New” to build a new data fetcher expressions.

                             3.8.2  A dd a new data fetcher expression

C. In the window of defining data fetcher, “Data Source” is to operate the SQL “select from…”; “Filter conditions” is for “where…”; “Fill methods” is to define the data will be fill to

     which field from which table.

                                  3.8.3   Setting the SQL expression

D. In this case, external table StaffDeteil_Main considered as the data source. So Click Data Source, then import it to the expressions.

Note: The external table name can only be shown up in this step (see the diagr am 3.8.4 below)

                            3.8.4   A dd the table which is refered

The“Fillcondition”would be“TestingExternalData_StaffDetail_Main.Date>=thisReport.Sheet1:RegisterFrm    

           And TestingExternalData_StaffDetail_Main.Date <= thisReport.Sheet1:Too”

Meaning: Where the date in the field “From” in this report is later than the date in the table StaffDetail_Main and the date in the field “to” in this report earlier than the date in the table StaffDetail_Main

                                   3.8.5   Filling condition expression

E. Select “Fill methods” and double click the “loading Data” for each field which has been supposed to fill up.

                                 3.8.6   Filling Mothod expression

Select which data from which table will be filled up in this report.

In the case,

“ TestingExternalData_StaffDetail_Main.Name” has been chosen to fill the field “Name” in this report. Also,

“TestingExternalData_StaffDetail_Main.Age” is filled in the field “Age”

“TestingExternalData_StaffDetail_Main.nationality” is for the field “Nationality”

“TestingExternalData_StaffDetail_Main.subject” is for the field “ Subject”

“TestingExternalData_StaffDetail_Main.salary” is for the field “Salary”

                             3.8.7   Select the field that you want to input

F.  Once the expession has been down, they should be shown up in the “specification” area ( see the diagram 3.9 below)

                       3.9  The whole SQL expression in this data fetcher

G.  A simple new template has been completed. “save” it.

Testing

1)  Return to “My Workbench” window. Obviously, the new report “informationOfCesoft” is in the system. Click “new” to open a new report to fill up

                               4.1  Read to fill the new report

2)  Select the date you want to query.

3)  The final Result will be shown up as below