You are here:: Hướng dẫn A-Tools Tạo công thức BS_SQL bằng “SQL Builder”
 
 

Tạo công thức BS_SQL bằng “SQL Builder”

Từ menu "A-Tools" chọn "SQL Builder"
Với Excel 2003 vào menu "A-Tools"->"Truy vấn dữ liệu" chọn "SQL Builder"

(1) DBKEY - Khóa kết nối: chọn CSDL hoặc DBKEY (mã kết nối với CSDL bên ngoài). Để kết nối với CSDL bên ngoài, người dùng phải tạo DBKEY, cách tạo sẽ hướng dẫn ở trang sau.
(2) Danh sách các tên vùng hoặc tên bảng (table, query) dữ liệu trong CSDL chọn ở (1). Người dùng hãy nhấp đúp chuột hoặc kéo tên vùng dữ liệu từ (2) sang (3) để làm nguồn dữ liệu.
(3) Nối các khóa quan hệ giữa các bảng (table) nếu có (trong hình vẽ là "MA_VLSPHH"). Chọn các trường dữ liệu để hiển thị và làm điều kiện.
(4) + Output: nếu chọn, trường dữ liệu đó sẽ hiển thị
+ Expression: tên trường dữ liệu hoặc biểu thức tính
+ Aggregate: chọn tên hàm thống kê: Sum; Count; Min; Max;...
+ Alias: đặt tên cho trường hoặc biểu thức ở "Expression"
+ Sort Type: chọn kiểu sắp xếp tăng (Ascending) hay giảm (Descending) dần
+ Sort Order: đặt ưu tiên trường sắp xếp
+ Grouping: chọn các trường dữ liệu được nhóm theo. Mục này được sử dụng khi người dùng chỉ định hàm thống kê ở "Aggregate". Tất cả các trường nếu không có chỉ định hàm thống kê thì ần được chọn "Grouping".
Criteria: mục đặt điều kiện lọc. Người dùng có thể dùng chuột để chọn dữ liệu trong một ô của bảng tính làm điều kiện động, thực chất là sử dụng hàm RANGE(..).
(5) Sau khi thực hiện 4 mục trên, A-Tools sẽ tự động tạo câu lệnh T-SQL cho hàm BS_SQL.

Sau khi thiết lập xong, người dùng chọn nút "Tiếp tục" sẽ xuất hiện màn hình dưới đây

 

+ Tên bảng kết quả: A-Tools sẽ tự động tạo tên cho vùng kết quả.
+ Tự động tạo tên các cột: A-Tools tự đặt tên các vùng theo mỗi cột trong vùng kết quả. Những tên được tạo sẽ được sử dụng trong các công thức của Excel.
+ Chèn dòng: nếu được chọn, bảng kết quả sẽ chèn các dòng dữ liệu sau con trỏ. Nếu không, dữ liệu dẽ bị ghi đè lên.
+ Địa chỉ đặt tham số SQL: ô chứa câu lệnh SQL. Cần thiết sử dụng khi chuỗi lệnh SQL dài (số ký tự > 128 ký tự).
+ Địa chỉ ô/vùng đặt công thức: ô đầu tiên trong bảng tính chứa giá trị của bảng kết quả.
Sau khi hoàn tất, chọn nút "Thực hiện" để A-Tools chạy hàm BS_SQL trong bảng tính.
Công thức có hàm BS_SQL được tạo ra bởi "SQL Builder" như sau:
=BS_SQL("SELECT DMHH.TEN, Sum(KHO.SLG) AS SLG
FROM DMHH INNER JOIN KHO ON KHO.MA_VLSPHH = DMHH.MA_VLSPHH
GROUP BY DMHH.TEN, KHO.LOAI_PHIEU
HAVING KHO.LOAI_PHIEU = 'N'" , "INSERT=YES;AutoNames(THHH);")

Hướng dẫn sử dụng hàm BS_SQL
Hàm BS_SQL trong A-Tools là một hàm đa năng và rất đặc biệt. Kết quả trả về của hàm là một bảng dữ liệu được lọc theo các điều kiện phức tạp, dữ liệu được móc nối với nhau mà nguồn dữ liệu có thể từ nhiều loại CSDL khác. Chỉ cần một hàm BS_SQL, người dùng có thể tạo ra các loại báo cáo động.
Hàm BS_SQL có cấu trúc:
BS_SQL(SQL [,OPTIONS])
+ SQL: Là một chuỗi khai báo câu lệnh truy vấn SQL. SQL là một ngôn ngữ truy vấn CSDL như trong Access, Foxpro, dBASE, SQLServer, Oracle...
Câu lệnh SQL trong hàm BS_SQL được phối hợp thêm các hàm: RECNO, CELL, RANGE, DATA, GetOnce, FORMULA, FieldIf, VTC,...Nhờ phối hợp các hàm này trong khai báo SQL, hàm BS_SQL có thể trả về kết quả rất linh động.
+ OPTIONS: Là tham số tuỳ chọn (có thể không có), tham số này là một chuỗi, cho phép khai báo thêm các tham số cho hàm, các tham số được ngăn cách nhau bởi dấu chấm phảy (;)
Các tham số sử dụng trong OPTIONS bao gồm: DBKEY, HR, NAME, AUTONAMES, INSERT, SERVERSOURCE.
Có thể lập trình các macro trong VBA để chạy trong các sự kiện của hàm BS_SQL, khai báo chạy các macro thực hiện trong tham số OPTIONS. Các tham số được khai báo như sau:
+ Cho phép chạy một macro trước khi cập nhật dữ liệu vào bảng tính
OnBeforeUpdate = YourMacro
+ Cho phép chạy một macro sau khi dữ liệu được cập nhật
OnAfterUpdate = YourMacro
+ Cho phép chạy một macro khi nhấp đúp chuột vào vùng dữ liệu (có hàm BS_SQL)
OnDblClick = YourMacro
+ Cho phép chạy một macro khi con trỏ ô di chuyển
OnSelectionChange = YourMacro
+ Cho phép chạy một macro (hàm) để thay đổi giá trị trong quá trình nhận dữ liệu từ kết quả truy vấn SQL.
OnGetValue = YourMacro
Ví dụ: lấy toàn bộ dữ liệu sổ kho vào bảng tính Excel, sổ kho nằm trong tệp Access "Examble.mdb" đã được kết nối với mã DBKEY là MDB.

=BS_SQL("SELECT * FROM KHO" , "DBKEY=MDB")
Tham số SERVERSOURCE dùng để lấy dữ liệu từ máy chủ.
Ví dụ:
=BS_SQL("SELECT * FROM KHO" , "DBKEY=MDB; SERVERSOURCE=YES")

Công cụ "SQL Builder" giúp người dùng tạo công thức cho hàm BS_SQL một cách dễ dàng, thông qua công cụ này người dùng có thể tự khai thác về cách sử dụng hàm BS_SQL.
A-Tools cung cấp hàm BS_SQL để tạo báo cáo động, sử dụng ngôn ngữ truy vấn T-SQL và thêm nhiều thuộc tính mà chỉ có trong A-Tools do đó bạn sẽ tạo được báo cáo động với độ phức tạp cao mà không phải lập trình. Bạn cần đọc kỹ các tài liệu của A-Tools về hàm BS_SQL để khai thác tốt A-Tools.
Hướng dẫn sử dụng chi tiết cùng các ví dụ của hàm BS_SQL được viết trong 2 tệp Excel gửi trong bộ cài đặt là:
C:\A-Tools\DATA_DEMO\Examble.xls - Hướng đãn về cách khai báo SQL, tham số OPTIONS trong hàm BS_SQL.
C:\A-Tools\HELP & DEMOS\Bai 2 - Cac ham ho tro nhom ham Database Functions.xls
Hướng dẫn sử dụng các hàm hỗ trợ trong khai báo SQL của hàm BS_SQL: RECNO, CELL, RANGE, DATA, GetOnce, FORMULA, FieldIf, VTC.

Đọc thêm bài viết hướng dẫn sử dụng hàm BS_SQL tại đây:
C:\A-Tools\Help\BS_SQL.htm

Xem hướng dẫn bằng video tại đây:
http://www.bluesofts.net/Products/AddIns/A-Tools/Help/DEMO/Index.htm

Tạo DBKEY - Kết nối dữ liệu bên ngoài
A-Tools cho phép kết nối với các CSDL ngoài: Access, Foxpro, MS SQL, MySQL,...Để kết nối vào Excel, người dùng phải tạo DBKEY.
Từ menu "A-Tools" chọn "SQL Builder"
Với Excel 2003 vào menu "A-Tools"->"Truy vấn dữ liệu" chọn "DBKEY - Thiết lập các kết nối với CSDL bên ngoài"


+ DBKEY: mã của kết nối. Tên DBKEY được sử dụng trong các hàm của A-Tools như BS_SQL, BS_TABLE,...
+ Thêm: tạo thêm DBKEY (thêm kết nối)
+ Sửa: sửa lại các thong số thiết lập DBKEY
+ Gỡ bỏ: gỡ bỏ DBKEY khỏi A-Tools. Nếu DBKEY bị gỡ bỏ, các công thức sử dụng tới DBKEY này đều bị lỗi.
Ví dụ:
Truy vấn dữ liệu sổ kho, với loại chứng từ là nhập ‘N'
1) Sổ kho nằm ở tại tệp đang mở
=BS_SQL("SELECT * FROM KHO WHERE LOAIPHIEU='N' ")
2) Sổ kho nằm ở tệp CSDL Access "Examble.mdb".
Ta đã tạo DBKEY với tên MDB kết nối tới "Examble.mdb"
=BS_SQL("SELECT * FROM KHO WHERE LOAIPHIEU='N' " , "DBKEY=MDB")

 

Truy vấn, nhận dữ liệu từ máy chủ bằng hàm BS_SQL()
Khi máy chủ đang mở. Từ máy khách dùng hàm BS_SQL với việc gán thuộc tính SERVERSOURCE=YES để nhận dữ liệu từ máy chủ.
Giả sử máy chủ đang chạy và đang mở một workbook có vùng dữ liệu tên là "KHO". Cách viết hàm như sau:
=BS_SQL("SELECT * FROM KHO" , "SERVERSOURCE=YES")
Nếu máy chủ quản lý sổ "KHO" trong CSDL Access và được kết nối bởi mã DBKEY có tên "MDB"
=BS_SQL("SELECT * FROM KHO" , "DBKEY=MDB; SERVERSOURCE=YES")
Với hai ví dụ trên, thay vì lấy dữ liệu trong máy khách, người dùng có thể lấy dữ liệu từ máy chủ. Việc viết hàm BS_SQL() như cách thức cũ chỉ cần thêm khai báo trong tham số OPTIONS là SERVERSOURCE=YES