Tworząc dashboard czy też cykliczny raport, zazwyczaj zasila się go w dane korzystając z bezpośredniego połączenia z bazą danych. Połączenie takie można dodatkowo wzbogacić o dynamicznie wybierany zakres kryteriów korzystając z makra napisanego w języku VBA. Wspomniane makro odwołuje się do danych wybranych w komórce i na ich podstawie zmienia klauzulę WHERE w zapytaniu T-SQL. Prześledźmy poniższe kroki potrzebne do skonfigurowania takiego makra.
1. Konfiguracja połączenia w Excelu
Aby wykonać wszystkie kroki przedstawione w przykładzie należy zainstalować instancję SQL Server np. 2012 wraz z bazą danych AdventureWorks2012. Jeżeli jednak posiadamy dostęp do innej bazy danych, również możemy odtworzyć ćwiczenie pamiętając o modyfikacji zapytania SQL. Na początku otwórzmy nowy skoroszyt programu Excel i zapiszmy go jako skoroszyt z obsługą makr. Następnie kliknijmy na kartę Dane --> Pobieranie danych zewnętrznych --> Z innych źródeł --- > Z programu SQL Server. W ten sposób przejdziemy do Kreatora połączeń danych, w którym skonfigurujemy nasze połączenie. Połączenie to będziemy konfigurować w różny sposób w zależności od tego jak zainstalowaliśmy naszą instancję. W moim przypadku w polu Nazwa serwera wpisuję nazwę swojego komputera łamaną na nazwę instancji SQL Servera. Pole poświadczeń logowania zostawiam domyślnie ustawione na Uwierzytelnienie systemu Windows.
Klikamy Dalej i przechodzimy do kolejnego okna Kreatora połączeń, w którym możemy wybrać dowolny widok czy tabelę.
Klikamy ponownie Dalej i przechodzimy do ostatniego okna, którego użyjemy do zmiany nazwy połączenia na łatwiejszą np. na połączenie.
Klikamy Zakończ i ustawiamy aby nasze połączenie danych zwracało dane jako tabela w arkuszu w komórce A5.
W okienku Importowanie danych przed kliknięciem OK zmienimy jeszcze właściwości połączenia. W tym celu klikamy na przycisk Właściwości... i w karcie Definicja zmieniamy Typ polecenia na SQL a w oknie tekst polecenia wpisujemy poniższe zapytanie SQL. Zapytanie to zwróci nam ilość sztuk zapasu danej kategorii i podkategorii asortymentu.
select
pc.Name
,ps.Name
,SUM(PI.Quantity)
from Production.Product as PP
inner join Production.ProductSubcategory as PS on PS.ProductSubcategoryID=PP.ProductSubcategoryID
inner join Production.ProductCategory as PC on PS.ProductCategoryID=PC.ProductCategoryID
inner join Production.ProductInventory as PI on PI.ProductID=PP.ProductID
where pc.Name like 'Bikes'
group by pc.Name,ps.Name
Po kliknięciu OK i akceptacji pojawiającego się powiadomienia, w komórce A5 pojawi się nam tabelka z określonym zapasem danego typu roweru.
2. Marko w VBA
Nim przejdziemy do napisania marka stwórzmy listę rozwijaną w komórce A1, w której będą znajdowały się nasze kryteria wyboru do klauzuli WHERE. Stworzona lista powinna zawierać następujące kategorie sprzętów: Bikes, Components, Clothing i Accessories. Używając kombinacji klawiszy Alt+F11, otwieramy okno Visual Basic i wklejamy poniższy kod. Makro to będzie śledziło czy w komórce A1 dokonała się zmiana, jeżeli tak to pobierze dane używając naszego połączenia i odświeży dane w tabeli.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
ActiveWorkbook.Connections("połączenie").OLEDBConnection.CommandText = _
"select pc.Name,ps.Name,SUM(PI.Quantity)from Production.Product as PP inner join Production.ProductSubcategory as PS on PS.ProductSubcategoryID=PP.ProductSubcategoryID inner join Production.ProductCategory as PC on PS.ProductCategoryID=PC.ProductCategoryID inner join Production.ProductInventory as PI on PI.ProductID=PP.ProductID where pc.Name = '" & Range("A1").Value & "' group by pc.Name,ps.Name"
ActiveWorkbook.Connections("połączenie").Refresh
End If
End Sub
Pamiętajmy żeby całe zapytanie SQL znajdowało się w jednej linijce. O tym jak przedstawić kod SQL w poszczególnych linijkach opowiem w kolejnych postach. Zapisujemy nasze makro i zamykamy edytor Visual Basic. Od tej pory każda zmiana w komórce będzie skutkowała pobraniem nowych danych i odświeżeniem tabeli.