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. Eb­ben 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áb­lá­zat­ból, csa­to­lás­sal 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.
dátum-mezők a PowerPivot naptár-tábla és kapcsolódó táblájában
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ít­mé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 fel­hasz­ná­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ő elem­zen­dő 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, amely­nek egy ol­da­lán a naptár áll.
kapcsolat létrehozása a PowerPivot naptár-tábla és kapcsolódó táblája között
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 meg­ha­tá­roz­ni, 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áb­lá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 kap­cso­la­tot 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 meg­cse­ré­lőd­tek.
a húzás irányának szerepe kapcsolat létrehozásakor a PowerPivot naptár-tábla és kapcsolódó táblája között
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 se­gít­sé­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 kap­cso­lat 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ít­mény ab­la­ká­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ő be­jegy­zé­se­it. 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 me­ző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 el­vá­lasz­tó­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ép­let­ben? 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 u­gyan­en­nek 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 kap­cso­lat­ra. 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 nyis­suk 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öl­jü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 ak­ti­vál­ni 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!
PowerPivot naptár-tábla, a kapcsolat szerkesztése parancstábla
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!
PowerPivot naptár-tábla, a kapcsolat szerkesztése parancstábla karikatúrája
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 kap­cso­ló 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.
PowerPivot naptár-tábla, a kapcsoló mezők váltása
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 egyet­len mezője a dátumokat tartalmazó Date mező. A CALENDAR függvény dátumtartományát két argumentumával kell meg­ha­tá­roz­nunk: 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 el­ső 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áb­lát és adatnézetben néhány egyéni összesítést.
DAX, a CALENDARAUTO függvény
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 Ter­ve­zé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 je­le­nít­he­tünk meg.
PowerPivot naptár-tábla intervallumának beállítása
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 Pa­ge 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 áll­nak, dátumegységek szerint, csökkenő sorrendben. Ha munkák során, az alkalmazott dátumegységek állandóak, akkor hozzunk létre hi­e­rar­chi­kus mezőcsoportot és mentsük a sablonba.
ismertető letöltése pdf-ben munkafüzetek letöltése
margitfalvi.arpad@gmail.com