a naptár-tábla kapcsolatai
2018-08-21 Excel 2016
Szerepe szerint, a naptár-tábla csak egy segédtábla, de a többi táblához hasonlóan, kapcsolatokkal épül be az adatbázis struktúrájába. Ebben a cikkben a naptár integrálását és kapcsolatainak kezelését mutatom be a bővítmény 2016-os verziójában.
Első példánk egy web áruház szállításkor fizetendő rendeléseinek adatait tartalmazó tábla, amelyet a rendelések adatbázis-táblázatból, csatolással hoztam létre. Mezői a következők. Számla: számlaszám (szöveg). Rendelés: a megrendelés dátuma. A tábla a 2016-os és a 2017-es év rendeléseit tartalmazza. Érték: a számla összege. Kiegyenlítve: a számla-összeg jóváírásának napja.
Mielőtt hozzáfognánk a tábla vizsgálatához, szúrjuk be az általunk készített és mentett naptár-táblát: Tervezés, Naptárak, Dátumtáblázat, Új!
A naptárt össze kell kapcsolni az összes elemzendő dátum-mezővel. Tehát előfordulhat, hogy két tábla között több kapcsolatot is létre kell hoznunk. Micsoda? Erre az adatbázis-kezelésben még gondolni sem szabad! De a PowerPivot-ban szabad! Sőt! Persze a bővítmény több kapcsolat esetén is mindig csak egyet fog figyelembe venni: az aktív kapcsolatot. Az aktív kapcsolat az elsőnek létrehozott, vagy a felhasználó által kiválasztott kapcsolat. A naptár tábla mindig a kapcsolat egy oldali táblája legyen!
A kapcsolatok deklarálásához váltsunk nézetet a Kezdőlap, Nézet, Diagramnézet utasítással. Kattintással jelöljük ki az első elemzendő dátum-mezőt, majd lenyomott egérbillentyűvel húzzuk a naptár Date mezőjére. A bővítmény létrehozza a kapcsolatot, amelynek egy oldalán a naptár áll.
A húzás iránya általában lényegtelen, de abban az esetben ha a dátum-mező minden bejegyzése egyedi, akkor a húzás iránya fogja meghatározni, melyik tábla áll majd a kapcsolat egy oldalán. Készítettem egy kis modellt ennek a helyzetnek a szemléltetésére. Van két táblánk: egyik, másik. A kapcsoló mező az „egyik AZ”. Hozzuk létre a kapcsolatot az „egyik” tábla kapcsoló mezőjének húzásával, majd a kapcsolatot a Delete billentyűvel törölve, a „másik” tábla kapcsoló mezőjének húzásával. Ahogy látjuk a második esetben a szerepek megcserélődtek.
Térjünk vissza eredeti példánkhoz! Létrehoztuk a kapcsolatot a naptár és a rendelések táblák között, a rendelés és a Date mezők segítségével. Alakítsunk ki egy újabb kapcsolatot, most a kiegyenlítve és a Date mezők között. A második kapcsolat szaggatott vonala a kapcsolat inaktív állapotát jelzi. A vonalra mutatva a bővítmény keretezve mutatja a kapcsoló mezőket.
Összesítsük a 2016 május 14-t követő kétszáz nap megrendeléseinek összegét, havi bontásban! Szúrjuk be a pivot táblát a bővítmény ablakának Kezdőlap, Kimutatás parancsával! Tehát a rendelés mező dátumai fogják meghatározni az összesítendő érték mező bejegyzéseit. Ez azt jeleni, gondolhatnánk, hogy a rendelés mező kerül majd a SOROK területre. De nem. A naptár év és hónap név mezői lesznek a pivot tábla sor mezői.
Miután kialakítottuk a pivot tábla szerkezetét hozzunk létre egyéni összesítést r_összesen néven a PowerPivot, Mértékek, Új mérték… utasítással: =SUMX( 'rendelések' ) ; CALCULATE( SUM( 'rendelések'[érték] ) ; DATESINPERIOD( Naptár[Date] ; "2016-05-15" ; 200 ; DAY ))). Az én gépemen a bővítmény nem ismerte fel a dátumot a DATESINPERIOD függvényben, ha pontot használtam dátum-egység elválasztónak. A szóközt, a vesszőt, a perjelet és a kötőjelet igen, de a pontot nem.
Gondolkodjunk el egy kicsit a képleten! Honnan tudja a bővítmény, hogy a rendelés mező dátumait kell figyelnie, hiszen nem szerepel a képletben? A kapcsolatból. Mert az aktív kapcsolat a Date és a rendelés mezők segítségével valósul meg. Ezt tapasztalhatjuk akkor is, amikor ugyanennek a periódusnak a bevételeit kívánjuk összegezni, havi bontásban. A feladat végrehajtásához a másik kapcsolatot kell aktiválnunk.
A váltás két lépésben történik: először törölnünk kell a jelenlegi kapcsolat aktív-attribútumát és csak ezután térhetünk át a másik kapcsolatra. Adatnézetben a művelet végrehatásának lépései a következők. [1] A Tervezés, Kapcsolatok, Kapcsolatok kezelése paranccsal nyissuk meg a kapcsolat-kezelő panelt! [2] A szerkesztés funkcióval jelenítsük meg az aktív kapcsolat részleteit! [3] A bal alsó sarokban töröljük az Aktív feliratú jelölőnégyzet pipáját majd az OK gombbal térjünk vissza a kapcsolatkezelő ablakába! [4] Jelenítsük meg az aktiválni kívánt kapcsolat részleteit és jelöljük ki a bal sarokban álló vezérlőt.
És íme, egy gyönyörű panel! Tervezte: Microsoft Co. Vigyázat, nagyon formabontó! Aki nem bírja, ne nézze!
Szeretnék segíteni a fejlesztőknek, ezért készítettem egy tervet a 2019-es változathoz, amelyben tovább megyek a kijelölt úton… Nem kérek pénzt érte!
Folytatva, ahol a design-kitérő előtt tartottunk, a kapcsolat váltása kapcsolatnézetben sokkal egyszerűbb. A kapcsolati vonal helyi menüje tartalmazza a váltás parancsait: Megjelölés inaktívként, Megjelölés aktívként. Természetesen a művelet ebben a nézetben is két lépésből áll, először inaktiváljuk a jelenlegi-, majd aktiváljuk a használni kívánt kapcsolatot.
Lépjünk vissza a program-ablakba. És tessék! Már látható is az eredmény a pivot táblában. Igaz, hogy ez nem a rendelés összesen (r_összesen)… Tehát, akkor ezt a két számítást csak külön pivot táblákban lehet elvégezni? Nem. A DAX rendelkezik egy függvénnyel, amellyel a képlet kiértékelésekor használandó kapcsolatot határozhatjuk meg. Ez a USERELATIONSHIP függvény. Argumentumai a kapcsoló mezők.
Csináljunk mindent vissza! Legyen ismét a rendelés a kapcsoló mező, és hozzunk létre egy újabb egyéni összesítést! A neve legyen b_összesen. Képlete: =CALCULATE( SUMX( 'rendelések' ; CALCULATE( SUM( 'rendelések'[érték] ) ; DATESINPERIOD( 'Naptár'[Date] ; "2016-05-15" ; 200 ; DAY ))) ; USERELATIONSHIP( 'Naptár'[Date] ; 'rendelések'[kiegyenlítve] )). És ezt kapjuk eredményül.
Ha már a függvényekről van szó: a CALENDAR és a CALENDARAUTO függvényekkel virtuális naptár-táblát generálhatunk. A tábla egyetlen mezője a dátumokat tartalmazó Date mező. A CALENDAR függvény dátumtartományát két argumentumával kell meghatároznunk: első dátum, utolsó dátum.
A CALENDARAUTO függvény dátumtartományának első napja, az adatbázis legkorábbi dátuma évének január elsejéje, utolsó napja az adatbázis légkésőbbi dátuma évének szilvesztere. A függvény egyetlen argumentuma opcionális: a pénzügyi év utolsó hónapjának száma. Ha a bővítmény argumentumot észlel, akkor a visszaadott virtuális naptár első napja az adatbázis legkorábbi dátuma pénzügyi évének első napja és utolsó napja az adatbázis legkésőbbi dátuma pénzügyi évének utolsó dátuma lesz.
A függvény argumentumos használatára is készítettem egy kis modellt. A dátumok nevű adatbázis-táblázatból hoztam létre csatolt táblát és adatnézetben néhány egyéni összesítést.
De térjünk vissza eredeti témánkhoz! Már csak azt kell megbeszélnünk, mi történik a naptárral akkor, ha a táblák frissítését követően bővül az adatbázis dátum-tartománya? Semmi. Az új határ-napokat nekünk kell meghatározni. [1] Aktiváljuk a naptár-táblát, [2] majd adjuk ki a Tervezés, Naptárak, Dátumtáblázat, Tartomány frissítése (<jelenlegi első nap> - <jelenlegi utolsó nap>) utasítást [3] és adjuk meg az új kezdő-, illetve záró-dátumot a megjelenő parancstáblán. A bővítményhez hasonlóan mi is kezdő-dátumnak újév napját, záró-dátumnak szilveszter napját határozzuk meg. Az adatbevitelt naptár segíti, amelyet a vezérlők jobb oldalán álló, szamárfüles papírlapra kattintva jeleníthetünk meg.
A naptár napok részletezésben jelenik meg, de a Ctrl+↑ billentyűkkel hónapos-, éves- és évtizedes bontást is kérhetünk. A Page Up és Page Down billentyűkkel, a részletezéstől függően, a hónapokat, az éveket, az évtizedeket és a évszázadokat válthatjuk.
Ahogy láttuk, a naptár-tábla mezői a pivot táblában csoportosító szerepet töltenek be, tehát vagy a sor-, vagy az oszlop területen állnak, dátumegységek szerint, csökkenő sorrendben. Ha munkák során, az alkalmazott dátumegységek állandóak, akkor hozzunk létre hierarchikus mezőcsoportot és mentsük a sablonba.
margitfalvi.arpad@gmail.com
