Để tạo báo cáo “Drilldown và Add-in A-Tools” truy xuất nguồn dữ liệu theo Phương pháp lọc từng cấp (Tổng -> chi tiết) các bạn cần chú ý các mục sau:
- Tìm các điều kiện tạo ra chỉ tiêu tổng?
- Lọc theo điều kiện của chỉ tiêu tổng
- Điều kiện để tìm kiếm
Phương pháp tạo sự kiện - chuyển động (Kêt hợp các hàm)
Để tạo sự chuyển động trong báo cáo chúng ta dùng phương pháp tạo sự kiện hay viết lệnh sự kiện để có thể chuyển động từ sheet này sang sheet khác hoặc khi chọn chuột sẽ về địa chỉ mong muốn.
Cấu trúc hàm như sau: BS_SQL(SQL, [OPTIONS])
Các sự kiện phải khai báo ở tham OPTIONS của hàm BS_SQL.
Được dùng các sự kiện dưới:
1. OnBeforeUpdate
2. OnAfterUpdate
3. OnBeforeExec
4. OnGetValue
5. OnDblClick (phải khai báo trực tiếp trong hàm)
6. OnSelectionChange (phải khai báo trực tiếp trong hàm)
(Các sự kiện từ 1->4 có thể khai báo trực tiếp trong hàm hoặc khai báo trên ô tham chiếu).
Cách khai báo gọi sự kiện
Tên sự kiện=Tên hàm có sẵn hoặc hàm viết trong VBA
Các hàm có thể sử dụng: SET(), GOTO(), DATA(), UPDATE()
1. Hàm GOTO() cho phép mở tới địa chỉ ô, có thể kết hợp điều kiện tìm kiếm
Cấu trúc: GOTO(Range, [ActiveColumn],[Criteria])
Giải thích các tham số:
+ Range: là địa chỉ ô/vùng cần mở, có thể là một vùng để tìm kiếm bởi điều kiện trong [Criteria]
Ví dụ: 'Doanh Thu'!D4
Ý nghĩa là mở sheet Doanh Thu và con trỏ đặt tại ô D4. Tên sheet có dấu cách hawocj có ký tự đặc biệt phải để trong hai dấu nháy đơn ('Tên sheet')
+ ActiveColumn: vị trí cột trong Range sẽ được chọn nếu điều kiện tìm trong [Criteria] thỏa mãn. Nếu bỏ qua hệ thống hiểu là 1. Chỉ cần dùng nếu khai báo điều kiện tìm [Criteria]
+ Criteria: là các điều kiện tìm kiếm. Cấu trúc mô tả điều kiện như sau:
Vị trí côt1=Giá trị tìm1,Vị trí côt2=Giá trị tìm2,...,Vị trí côt n=Giá trị tìm n
- Điều kiện là kết hợp theo toán tử AND - và
- Vị trí cột: là vị trí trong vùng Range (của tham số đầu tiên)
- Giá trị tìm: có thể lấy từ địa chỉ ô trong sheet, hàm tự tạo VBA, hàm DATA() để trả về giá trị trong bảng dữ liệu có hàm BS_SQL().
2. UPDATE() cho phép cập nhật công thức ở các vùng chỉ định, có thể nhiều vùng
Cấu trúc: UPDATE(Range1, [Range2],…,[Range n] )
+ Range 1,..range n là các địa chỉ ô có công thức cần cập nhật lại.
Hàm UPDATE() thường dùng với sự kiện OnBeforeExec(). Nếu vùng dữ liệu cho công thức đang chạy tạo bởi hàm BS_SQL hay công thức khác. Cần cập nhật trước khi lấy dữ liệu.
3. Hàm SET() cho phép gán giá trị vào các ô tại sheet hiện thời hoặc sheet khác, cho phép gán nhiều ô khác nhau.
Cấu trúc: SET(Set1, [Set2],...,[FuncName])
+ Set1,2,n là các cặp gán giá trị: địa chỉ ô=Giá trị
Ví dụ: 'Doanh Thu'!D4='KH001'
Ý nghĩa gán chuỗi KH001 vào ô D4 tại sheet Doanh Thu. Giá trị văn bản đặt trong hai dấu nháy đơn (không dùng nháy kép).
Gán giá trị trống thì là:
'Doanh Thu'!D4=""
+ FuncName: là các hàm được dùng kết hợp trong hàm SET là: GOTO, UPDATE các hàm được ngăn cách bởi dấu phảy (,)
4. Hàm DATA() lấy dữ liệu trong vùng có hàm BS_SQL theo tọa độ dòng, cột
Cấu trúc: DATA(row, col, [DataType])
+ row: là dòng chọn hoặc tọa độ dòng trong mảng
+ col: là cột chọn hoặc tọa độ cột trong mảng
(Tọa độ row, col tính từ 1 khi dùng trong tham số OPTIONs, nếu dùng trong SELECT của SQL thì tính từ 0)
+ DataType: không nhập A-Tools tự xác định kiểu. T là văn bản, D là ngày, N là số.
Cách dùng từng sự kiện:
1. OnSelectionChange=FunctionName (tên hàm):
Sự kiện này chạy khi dùng chuột hay chọn ô trong khu vực có hàm BS_SQL
Ví dụ 1: chọn vào một ô trong báo cáo thì gán giá trị vào ô C3,C4 và cập nhật 2 vùng tại các ô E10, H10.
Nếu nhấp đúp chuột trên báo cáo thì hàm gán KHO!A1=DATA(row,1) đồng thời tìm theđiều kiện và mở ô trong sheet KHO. Ứng dụng hàm SET, UPDATE, GOTO()
=BS_SQL(
"Lệnh SQL",
"OnSelectionChange=SET(C3=DATA(row,2), C4='KH001', UPDATE(E10,H10))"
);
Giải thích:
+ 'KH001' là chuỗi - Không được dùng "KH001"
+ DATA(row,1) sẽ trả về giá trị tại cột số 1, dòng hiện thời thuộc dòng bạn chọn trong báo cáo có hàm BS_SQL
+DATA(row,2) sẽ trả về giá trị tại cột số 2, dòng hiện thời...
2. 2. OnDblClick=FunctionName (tên hàm)
Sự kiện này chạy khi nhấp đúp chuột vào một ô trong khu vực có hàm BS_SQL
Ví dụ 2:
=BS_SQL(
"Lệnh SQL",
"OnDblClick=GOTO(KHO, 4, 1=DATA(row,2), 4=C4);"
)
Giải thích ví dụ trên: khi nhấp đúp chuột vào một ô trong báo cáo thì hàm GOTO sẽ chạy, do khai báo sự kiện OnDblClick.
+ Hàm GOTO sẽ chọn con trỏ vào dòng tìm được, vị trí cột 4 trong bảng KHO.
+ Điều kiện tìm là: tìm từ dòng đầu tiên đến cuối cùng trong bảng KHO, nếu
- Cột số 1 trong bảng KHO bằng giá trị "XK0000010" (giả thiết hàm DATA(row,2) trả về giá trị "XK0000010" tại dòng hiện thời trong báo cáo có hàm BS_SQL)
- Và cột 4 bằng "KH001" (giải thiết ô C4 chứa giá trị "KH001" trong sheet).
Một ví dụ khác:
=BS_SQL(
"Lệnh SQL",
"HR=NO; INSERT=YES; OnDblClick=SET(GOTO('DT'!D4), 'DT'!D4=DATA(row, 2), 'DT'!D5="");"
)
Giải thích: Sau khi nhấp đúp chuột sẽ mở sheet DT và chọn ô D4; gán D4 trong sheet DT bằng giá trị tại dòng chuột chọn, cột thứ 2; địa chỉ D5 trong sheet DT nhận giá trị rỗng.
(Ví dụ trích xuất ngược từ báo cáo tổng hợp doanh thu về báo cáo chi tiết doanh thu)
3. 3. OnBeforeUpdate=FunctionName (tên hàm)
Sự kiện này chạy (chạy hàm "FunctionName") sau khi SQL đã được chạy, nhưng chưa đổ dữ liệu vào sheet. Sự kiện này dùng khi cần định dạng vùng hay làm một số việc nào đó trước khi mảng kết quả được trả về bởi hàm BS_SQL()
Ví dụ 3:
=BS_SQL("Lệnh SQL", "OnBeforeUpdate=YourMacroVBA;")
Giải thích: hàm "YourMacroVBA" được chạy trước khi mảng dữ liệu đổ vào bảng tính. Hàm này người dùng tự lập trình trong module của VBA.
+ Cấu trúc hàm "YourMacroVBA" phải viết theo khuân mẫu trong tài liệu
4. OnAfterUpdate=FunctionName (tên hàm)
Sự kiện này chạy (chạy hàm "FunctionName") sau khi mảng kết quả đã được đổ vào bảng tính. Sự kiện này dùng khi cần định dạng lại vùng kết quả hay làm một số việc nào đó khi hàm BS_SQL đã hoàn thành mọi việc.
Ví dụ 4:
=BS_SQL("SELECT...", "OnAfterUpdate=YourMacroVBA;")
Giải thích: hàm "YourMacroVBA" được chạy trước khi mảng dữ liệu đổ vào bảng tính. Hàm này người dùng tự lập trình trong module của VBA.
+ Cấu trúc hàm "YourMacroVBA" phải viết theo khuôn mẫu trong tài liệu
5. 5. OnBeforeExec=FunctionName (tên hàm)
Sự kiện này chạy (chạy hàm "FunctionName") trước khi câu lệnh SQL chạy. Sự kiện này dùng khi cần chuẩn bị các vấn đề trước khi thực thi câu lệnh SQL, như định nghĩa lại name, định dạng,…
Ví dụ 5:
=BS_SQL("SELECT...", "OnBeforeExec=UPDATE(A3,H3);")
Giải thích: hàm UPDATE sẽ cập nhật công thức tại ô A3, H3 trước khi thực hiện câu lệnh SQL của công thức này.