Artykuł AFIN.NET
Język zapytań księgowych AFIN.NET © AFIN
1997-2008
Wojciech Gardziński, 2008.04
Najprostszy, a zarazem najbardziej efektywny sposób
definiowania dowolnych raportów finansowych
Summary:
AFIN.NET Financial Query
Language is the easiest way to define financial reports.
Very complicated financial
queries are replaced with very simply logic of accountat’s thinking way „Get an
account summary for (any combination of fields and account numbers using
wildcards)”. The sample at the end of this article.
Księgowy punkt widzenia
Z punktu widzenia
księgowego, tworzącego raporty, informatyka pełni wyłącznie rolę usługową.
Problemy księgowe nie mogą być zwielokrotnione jakąkolwiek uciążliwością,
wynikającą z komplikacji problemów informatycznych. Księgowy porusza się w
pewnym wąskim (mówimy tu wyłącznie o potrzebach informatyczno-raportowych)
zakresie pojęć, ale muszą być one dla niego proste, jednoznaczne i – tu
napotykamy na problemy informatyczne – NATYCHMIASTOWO wykonywalne. Komplikacja
zapytań raportowych nie polega w tym obszarze na różnorodności pojęć, ale na
komplikacji wewnętrznej – merytorycznej.
Księgowy mówi: „Potrzebuję
saldo po stronie winien konta 101
(nazwijmy to przykładem A), „obroty winien kont: 400, 411 dla MPKów: 123 i 124” (przykład B) oraz „wartość majątku trwałego bez
gruntów, czyli saldo winien konta 010
pomniejszone o 010.1 minus saldo ma
konta 070, pomniejszone o
Aby uzyskać te informacje,
księgowy zwykle bierze do ręki standardowy wydruk swojego programu finansowo-księgowego
(FK) – Zestawienie obrotów i sald (ZOiS) – zwany potocznie obrotówką,
odnajduje tam żądane informacje, sumuje wartości na kalkulatorze i wpisuje
wartość do raportu. I wcale nie jest to proste:
Zacznijmy od przykładu A:
Należy odszukać właściwe konto – obrotówka ma czasami kilkadziesiąt stron – i
wybrać odpowiednią kolumnę danych. ZOiS zwykle składa się z następujących
kolumn:
|
Konto |
nazwa |
Bilans otwarcia |
Obroty miesięczne |
Obroty narastające od początku roku |
Saldo |
||||
|
Wn |
Ma |
Wn |
Ma |
Wn |
Ma |
Wn |
Ma |
||
|
010 |
MT gr.0 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
|
011 |
MT gr.1 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
|
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
|
101 |
Gotówka |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
0,00 |
|
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
Konta zwykle drukowane są
jako analityka, więc tworzą bardzo długą listę. Zwykle są odpowiednio
podsumowane, zdarza się jednak, że nie – wtedy pozostaje sumowanie ręczne
poszczególnych wartości analityk lub, wtedy już zwykle tylko na ekran,
wykonanie zestawienia w wybranym stopniem szczegółowości danych lub wybranymi
kontami. Załóżmy jednak, dla uproszczenia, że takie podsumowania są dostępne.
|
Konto |
… |
saldo wn |
saldo ma |
|
010.1 |
… |
10,00 |
0 |
|
010.2 |
… |
20,00 |
0 |
|
010.3 |
… |
30,00 |
0 |
|
010.4 |
… |
40,00 |
0 |
|
010 |
… |
100,00 |
0 |
|
… |
… |
… |
… |
Informatyczny punkt
widzenia
Informatyk, aby wykonać
powyższe zadanie, musi zlokalizować dane – konkretnie tabelę lub zestaw tabel,
które zawierają potrzebne wartości. Niektóre systemy FK (te, którym nieobce
jest pojęcie „raport”) posiadają w swojej bazie gotową tabelę ZOiS,
uaktualnianą po każdym księgowaniu – raporty robią się wtedy dużo szybciej.
Zapytanie, o którym mowa w najprostszym przykładzie, wygląda tak: SELECT SUM(sw) FROM tabela200701 WHERE
konto=’101’ . Księgowy by tego nie napisał. A co dopiero przykłady bardziej
skomplikowane:
Gdy dane przechowywane są w
hurtowni danych, w tzw. kostce OLAP, zapytanie jest jeszcze bardziej
skomplikowane, bo taki jest język zapytań MDX – jest on znany wyłącznie przez
specjalistów.
Jak pogodzić potrzeby
księgowych z możliwościami programistów? Powszechna opinia to: Zakup
odpowiedniego systemu! W tym gotowych raportów. Termin wdrożenia – rok.
I tu, paradoksalnie,
napotykamy na problem największy – to, co dla księgowych łatwe, dla
informatyków trudne i odwrotnie, a najgorsze jest to, że potrzeby raportowe
księgowych są… DYNAMICZNE! Stale dochodzą nowe konta, ciągle trzeba
przedefiniowywać zapytania. Samo ich stworzenie to problem. Metoda prób i
błędów jest tu powszechnie stosowana. I księgowy, tak właściwie, nigdy nie ma
pewności, czy uwzględnił wszystkie konta w danej pozycji raportu. Dopiero, gdy
zgodzi się suma, jest pewny, że raport może ujrzeć światło dzienne.
Informatycy, tzn. dostawcy
systemów raportowych, wysyłają więc do księgowych konsultantów, którzy mają
pomóc w zdefiniowaniu raportów. Kosztuje to klienta dużo, a efekt jest
dyskusyjny, gdyż cała wiedza pozostaje dalej w głowie księgowego. Iteracje
poprawek nie mają końca i odwoływanie się do zapisanej w umowie specyfikacji
również.
Jak to usprawnić?
Uprośćmy nomenklaturę dla
nazw kolumn:
|
Standardowa nazwa |
Uproszczenie |
|
Konto |
konto |
|
Nazwa |
nazwa |
|
Bilans otwarcia po stronie
Wn |
bw |
|
Bilans otwarcia po stronie
Ma |
bm |
|
Obroty miesięczne po
stronie Wn |
mw |
|
Obroty miesięczne po
stronie Ma |
mm |
|
Obroty narastające od
początku roku po stronie Wn |
ow |
|
Obroty narastające od
początku roku po stronie Ma |
om |
|
Saldo po stronie Wn |
sw |
|
Saldo po stronie Ma |
sm |
Poznajmy zapytania
wieloznaczne
Od czasów DOS-a w
zapytaniach bazodanowych stosuje się tzw. symbole wieloznaczne (tzw.
„wildcard”-y):
? (pytajnik) – zastępuje
pojedynczy, dowolny znak
* (gwiazdka) – zastępuje
dowolny ciąg dowolnych znaków
W języku SQL są to
odpowiednio znaki ”%” (=”*”) i ”_” (=”?”), ale księgowi i tak znają system
„*?”, gdyż jest często stosowany, nawet w raportach systemów FK.
Uprośćmy sposób zapisu
zapytania
Nie: „podaj saldo winien
konta
(Znaku „/” nie stosuje się
zwykle w zapisie konta. Gdy jednak tak jest – można to ominąć, stosując inny
znak w zapytaniu.)
I tak, nasze przykłady,
wyglądałyby tak:
A) sw/101
B) sw/400???123+sw/400???124+sw/411???123+sw/411???124
C) sw/010-sw/010.1-sm/070+sm/070.1
,
inaczej: sw/010-sw/010.1-(sm/070-sm/070.1)
Proste? Tak, ale komplikacja
księgowości wymaga niekiedy zastosowania zapytań o bardzo znacznej ilości
znaków (np. ok.1000)
Uprośćmy więc zapytanie
jeszcze bardziej.
Jeżeli operacje wykonywane
są na jednym polu, a w większości zapytań tak jest, można zapisać to tak:
A) sw/101 (tu
bez uproszczenia)
B) sw/400???123>400???124>411???123>411???124
C) sw/010<010.1-sm/070<070.1
(Można to odczytać np. tak: ”Saldo winien konta 010 pomniejszone o analitykę 010.1
minus saldo ma konta 070,
pomniejszonego o analitykę
Zastosowane znaki „>” i
„<” oznaczają odpowiednio ”+” i ”-”, ale wyłącznie w odniesieniu do operacji
wewnątrz bloku zapytania dla jednego pola:
sw/1>2<3 jest równoznaczne: sw/1+sw/2-sw/3
.
Wykonanie raportu
Adresaci raportów życzą
sobie, aby raporty posiadały następujące cechy:
Normalnie skoroszyty Excela
cechy te posiadają, co więcej, każdy umie je obsłużyć (otworzyć, wydrukować,
zamknąć)
Rozwiązanie, czyli co
oferuje AFIN.NET?
AFIN.NET
oferuje dostęp do dowolnych danych zewnętrznych poprzez funkcję excelową. Skomplikowane zapytania księgowe, omówione powyżej,
można wpisywać on-line w arkuszu.
* Stopień
komplikacji księgowej – dowolny
* Dostęp do
informacji – natychmiastowy
*
Konieczność znajomości informatycznych języków zapytań – brak
Oczywiście, pod, z pozoru, prostą
funkcją, kryje się bardzo skomplikowany algorytm, przetwarzający zapytania na
język SQL lub MDX w zależności od typu źródła danych.
I tak (dane dla okresu 2007.01 – w zapytaniu musi być
uwzględniony również okres) zapytania księgowe przetwarzane są na:
Przykład A:
|
Język zapytania |
Tekst zapytania, kierowany
do bazy danych |
|
Język zapytań AFIN.NET |
”sw/101” ; ” |
|
SQL |
SELECT SUM(sw) FROM
tabela200701 WHERE konto='101' |
|
MDX |
WITH MEMBER
[Measures].[wynik] AS '0+SUM({[ko1].[1]}*{[ko2].[0]}*{[ko3].[1]},[Measures].[Suma
sw])' SELECT {[Measures].[wynik]} ON 0 FROM OCWCube WHERE
([rok].[2007],[mc].[01]) |
Przykład B:
|
Język zapytania |
Tekst zapytania, kierowany
do bazy danych |
|
Język zapytań AFIN.NET |
”sw/400???123>400???124>411???123>411??? ” |
|
SQL |
SELECT SUM(pole) FROM
(SELECT 1 AS nrw, +1*SUM(sw) AS pole FROM tabela200701 WHERE konto LIKE
'400___123%' UNION SELECT 2 AS nrw, +1*SUM(sw) AS pole FROM tabela 200701
WHERE konto LIKE '400___124%' UNION SELECT 3 AS nrw, +1*SUM(sw) AS pole FROM
tabela200701 WHERE konto LIKE '411___123%' UNION SELECT 4 AS nrw, +1*SUM(sw)
AS pole FROM tabela200701 WHERE konto LIKE '411___124%') |
|
MDX |
WITH MEMBER
[Measures].[wynik] AS
'0+SUM({[ko1].[4]}*{[ko2].[0]}*{[ko3].[0]}*{[ko7].[1]}*{[ko8].[2]}*{[ko9].[3]},[Measures].[Suma
sw])+SUM({[ko1].[4]}*{[ko2].[0]}*{[ko3].[0]}*{[ko7].[1]}*{[ko8].[2]}*{[ko9].[4]},[Measures].[Suma
sw])+SUM({[ko1].[4]}*{[ko2].[1]}*{[ko3].[1]}*{[ko7].[1]}*{[ko8].[2]}*{[ko9].[3]},[Measures].[Suma
sw])+SUM({[ko1].[4]}*{[ko2].[1]}*{[ko3].[1]}*{[ko7].[1]}*{[ko8].[2]}*{[ko9].[4]},[Measures].[Suma
sw])' SELECT {[Measures].[wynik]} ON 0 FROM OCWCube WHERE
([rok].[2007],[mc].[01]) |
Przykładu C („sw/010-sw/010.1-sm/070+sm/070.1”),
rozpisanego na SQL i MDX, już nie podajemy, gdyż zajął by kolejną stronę.
I pomyśleć, że zapytania
księgowe mogą być np. takie (przykład RZECZYWISTY!):
„sw/??.701.?.*<??.701.0.*>??.702.?.*<??.702.0.*>??.703.?.*<??.703.0.*>??.704.?.*<??.704.0.*>??.706.?.*<??.706.0.*>??.707.?.*<??.707.9>??.708.?.*<??.708.9.0*>02.708>??.730.?.*<??.730.0.*>??.740<??.740.0>06.740.0.??<06.740.0.00”
Dla AFIN.NET nie jest to
problem.
Co więcej, my, jego twórcy,
zdajemy sobie sprawę z konieczności zapisania tychże zapytań dla innych
systemów raportowych.
Wystarczy, w funkcji AFINA,
umieścić (jako ostatni!) dodatkowy argument „:” (dwukropek) i zapytanie w
języku AFIN.NET AUTOMATYCZNIE TŁUMACZY SIĘ na zapytanie w SQL lub MDX. Stąd
wystarczy je wykopiować do dowolnego innego systemu i uruchomić dany raport.