In an enterprise, there might be many existed software such as OA, ERP, BI system etc. Each software
have own database. However, in some case, the data from those databases 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, and Informix.
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
To explain the
processing, we will employ an actually existed table “StaffDetail_Main” (
see Figure
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 Figure
The actual implementation is that build a new query operation in our new report and export the data from external
table “StaffDetail_Main” to our new query.
Figure
Figure
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:
|
|
Field |
|
Single
Data |
“Register From”
“Too” |
|
Multiple Data |
“Name”
“Age” “Nationality”
“ Subject” “Salary” |
Figure
Detail operation in the case: once user choose the period (from when to when) that a staff
register in the Cesoft company, then system will feedback the data into the field “Name” “Age”
“Nationality”
“Subject” “Salary” from external table.
Step One: Import the
External Data to BC Excel Server
1)
Open the ES Management Console;
click “New” on the “External Data” section.
Figure
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.
Figure
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 Figure
Figure
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 ignore the other fields ). You will be allowed to modify the “Display Column Name”,
it just makes the thing easier.
Figure
5)
The permission setting can be set up as below
Query: designer can query the external data
Write: designer can write (update) the external
data
Figure
Step Two: Create the new
template
1)
Make a new report of template
2). Define the ID and
Name for the new report
3). Define the write
permission for each “Role”.
Define the read permission for each “Role”.
4)
Design the shape of the template like below.
Figure
5)
Creating a new table “InformationOfCesoft_Main” Define the single
data field for the fields of “Register from” and “To”
Figure
6) Creating a new table “InformationOfCesoft_Detail” and defining the multiple data field for the fields
of “Name” “Age” “Nationality”
“Subject” “Salary”
Figure
Note: 1. Do not forget to tick off the check box “Downward Expandable” if the number of the row for
your data inputting is more than the one you designed in
template
Figure
2. The data type in this
table and the one from the external table must be exactly same.
7)
A. Define the data fetcher. It is a kind of operation of database.
Define data fetcher
B. Select the button “New” to build new data
fetcher expressions.
Add 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.
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.
Figure
The“Fillcondition”would
be“TestingExternalData_StaffDetail_Main.Date>=thisReport.Sheet1:
RegisterFrm
And TestingExternalData_StaffDetail_Main.Date <= thisReport.Sheet1:
Too”
Filling condition expression
E. Select “Fill methods” and double click the “loading Data”
for each field which has been supposed to fill up.
Filling Methods
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”
Select the field that you want
to input
F.
Once the expression has been down, they should be shown up in the
“specification” area ( see Figure
Figure
G.
A simple new template has
been completed. “Save” it.
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
Read to fill the new report
2)
Select the date you want to query.
3) The
final Result will be shown up as below
CHAPTER 1---CHAPTER 2---CHAPTER 3---CHAPTER 4---CHAPTER 5---CHAPTER 6---CHAPTER 7---CHAPTER 8---CHAPTER