Okazuje się że jest to możliwe aczkolwiek w instrukcji zapomniano o tym wspomnieć.

Wystarczy użyć klawisza CTRL i po kliknięciu i przesortowaniu pierwszej kolumny “doklikiwać” z CTRLem kolejne sortowania

Bardzo miłe i użyteczne a tak jakby ukryte

SELECT
TRUNC (ADD_MONTHS (CURRENT_DATE, -12)) data12mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first12mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -12))) LAST12mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’) RM12mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -6)) data6mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -6)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first6mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -6))) LAST6mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -6)), ‘YYYY-MM’) RM6mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -3)) data3mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -3)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first3mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -3))) LAST3mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -3)), ‘YYYY-MM’) RM3mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -2)) data2mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first2mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -2))) LAST2mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -2)), ‘YYYY-MM’) RM2mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -1)) data1mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -12)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first1mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -1))) LAST1mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -1)), ‘YYYY-MM’) RM1mago,
TRUNC (ADD_MONTHS (CURRENT_DATE, -0)) data0mago,
TO_DATE(TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -0)), ‘YYYY-MM’)||’01′,’YYYY-MM-DD’) first0mago ,
LAST_DAY(TRUNC (ADD_MONTHS (CURRENT_DATE, -0))) LAST0mago,
TO_CHAR (TRUNC (ADD_MONTHS (CURRENT_DATE, -0)), ‘YYYY-MM’) RM0mago,
TO_DATE(TO_CHAR(CURRENT_DATE,’YYYY’)||’-01-01′,’YYYY-MM-DD’) FirstOfYear,
TRUNC (CURRENT_DATE-1) data1dago
FROM DUAL

Jak widać ten SQL zwraca jeden wiersz z preparowanymi na podstawie dzisiejszej daty różnymi wariacjami dat.

Teraz wystarczy tylko poszczególnym kolumnom zwróconym przez blok inicjalizacyjny podpiąć odpowiednie nazwy zmiennych jak we wpisie poprzednim (oczywiście można użyć dowolnej nazwy )

Nie zapomnijmy ustaiwić odświeżania tego bloku przynajmniej 1 raz dziennie

So, go on….

Bardzo często filtrujemy po datach i bardzo dobrze po to są. Więc tworzymy mozolnie raport, robimy skomplikowane funkcje, przestawiamy to wszystko w tabelach przestawnych, robimy super kolorowe wykresy i… dodajemy filtr na daty tak aby pokazać wynik za poprzedni miesiąc czyli np filtrujemy jakies pole aby zawierało się w datach 2009-11-01 do 2009-11-30. Zapisujemy ten raport na pulpicie i już mamy  gotowca którego możemy oglądać codziennie i cieszyć się dobrymi wynikami ale…. tylko przez miesiąc za miesiąc musimy wejść i przestawić datę i tak co miesiąc. Jeszcze gorzej jest gdy mamy raport filtrowany datą dzienną czyli załóżmy zawsze na wczoraj. Jakie jest rozwiązanie

1. Filtrować funkcją opartą na dacie (nie o tym ten topik ale pokażę) czyli np zamiast filtrować na sztywno w raporcie  Data=’2009-11-16′ robimy filtr na funkcji która wygląda następująco:

Data=TIMESTAMPDIFF(SQL_TSI_DAY,Current_Date,1)

O funkcjach na datach zrobię osobny wpis

2. Użyć zmiennych przedefiniowanych przez administratora (czyli w VB Leasing przeze mnie )

Otóż
Zrobiłem na potrzeby użytkowników szereg zmiennych tzw repozytorium które zwracają różne daty ustalane na podstawie bieżącej daty.
Oto lista tych zmiennych wraz z komentarzem i przykładem co zwróci taka zmienna zakładając że wykonana jest w dniu zrobienia tego wpisu tj w dniu: 2009-12-16

Tak więc użycie tak więc użycie takiej zmiennej i dynamiiczne filtrowanie danych będzie polegało na zastąpieniu statycznie wprowadzonej daty zmienną repozytorium.

Przykład:

Tworzymy raport z polem daty do filtrowania i filtrujemy

Wybieramy zmienną serwera

Wpisujemy zmienną z tabeli wyzej (na przykłądzie chce pokazac raport z dnia wczorajszego więc użyję zmiennej varData1dago)

i cieszymy się wynikiem

Dodano 2010-01-05

Dodałem jeszcze parę zmiennych do tego zestawu tj
tj zestaw zwracający daty od 13 do 24 miesięcy wstecz
varData13mago

varData14mago

varData15mago

varData16mago

varData17mago

varData18mago

varData19mago

varData20mago

varData20mago

varData20mago

varData20mago

• udało nam się bardzo szybko pokazać wyniki tj po miesiącu od rozpoczęcia wdrożenia można już było analizować dane w rewolucyjny jak dla naszego biznesu  sposób (wcześniej dominował Excel).
• udał nam się odejść od nieszczęśliwego Excela -użytkownik biznesowy nie szuka już wartości analizująć pojedyńcze transakcję w Excelu tj filtrując i sumując wyciągając komórkę nr H:45678 w której ma jakiś tam wynik oznaczony kolorem zielonym. Teraz użytkownik ma jak na talerzu podaną tę “zieloną” wartość wartość na której trzeba skupić uwagę  jako pierwszą i jeśli będzie chciał to używając drill-down i nawigacji kierowanej będzie mógł analizować szczegóły.
• W końcu jest jeden spójny obraz na firmę i że użytkownik może zrobić dosłownie wszystko samemu.
• Drastycznie wzrosła “kultura” raportowa użytkownika w naszej firmie.
• Na raporty się nie czeka (max 3 min ) a większość tj 90% casz oczekiwania wynosi 0 -10 sek (oczywiście po “scaschowaniu”) wogóle się nie czeka.
• No i IT ma mniej roboty

Jednocześnie nasz zarząd pojechał na Leasing Forum do Wiednia gdzie spotkały się wszystkie spółki holdingu. Przedstawiono tam BI wdrożony w polskiej spółce  a w zasadzie sposób zarządzania firmą poprzez Buisniess Intelligence. Pokaz live przygotowany specjalnie na maszynie wirtualnej – najpierw myśleliśmy o roamingu iPlusa i VPN do siedziby w Posce ale cena 100kb transmisji pakietowej  w roamingu nas powaliła – ok 1 EURO ). Trochę obawialiśmy się o tę maszynę wirtualną (baza, serwer aplikacj, serwer BI) czy zarząd poradzi sobie z uruchomieniem (szczególnie w przypadku problemów z uruchomieniem VM). Na szczęście tego dnia nie dostaliśmy SMSó ani telefonów w stylu: “nie działa”. Dostaliśmy jedynie takiego: “cytat po prezentacji: “Polacy przesiedli się z furmanki do Ferrari- gratulacje”

i wynik

Zróbmy więc raport z filtrem 100000$ :

Na pierwszy rzut oka wydawałoby się że  to jest to o co nam chodzi -  sprawdźmy wynik:

Taki sam jak poprzednio – czy filtr nie działa? Oczywiście działa lecz ponieważ filtrujemy fakt to tak naprawdę najpierw dokonywana jest grupowanie szczegółów dla regionu ameryki a potem odfiltrowywane są te regiony które mają sprzedaż poniżej 100000$ (tutaj takich nie ma więc nic nie zostało odfiltrowane).

Aby zrobić poprawnie raport na filtrowanych wstępnie faktach mamy dwie metody:

1) użyć tabel przestawnych ( dobry ale czasami  otrzemy się o  limit rekordów dla tabel przestawnych)

2) użyć mechanizmu podraportów

Metoda 1

Wyciągamy szczegół na którego poziomie miałby byc zastosowany filtr dla sumy sprzedaży ( w naszym wypadku jak powiedzieliśmy wcześniej nie chcemy produktów gdzie sprzedaż była mniejsza niż 100000$ a więc jest to Nazwa produktu)

jak widać na wyniku odfiltrowaliśmy sprzedaż gdzie suma była mniejsza niż 100000$

ok ale my chcieliśmy tylko prosty wynik w postaci sumy całości a nie szczegóły. Sprawę załatwia prosty Pivot table czyli po naszemu tabela przestawna

Tak więc w ten prosty sposób uzyskaliśmy to o co nam chodziło – sumę sprzedaży dla regionu Ameryki produktów których wartość sprzedaży była większa niż 1000000$

Metoda 2

Tworzymy prosty raport z  filtrami jak powyżej i zostawiamy tylko kolumnę nazwa produktu

zapisujemy go pod dowolną nazwą i przystępujemy do stworzenia raportu końcowego:

w którym filtrujemy nazwę produktu na podstawie wcześniej stworzonego wstępnego raportu w którym określiliśmy już kryteria filtrowania. Filtrowanie na podstawie zapisanego raportu można dokonać w okienku Filtrowania pod przyciskiem “Zaawansowane” – wybieramy raport i kolumnę której dotyczy filtrowanie

ostatecznie mamy

i poprawny wynik

Jak widać drugą metodą osiągnęliśmy ten sam rezultat . Użycie tabel przestawnych jest w zasadzie szybsze ale spotkałem się z sytuacją że analizowane były poszczególne transakcje które były filtrowane po fakcie i po odfiltrowaniu zostało ich tak wiele iż użytkownik otrzymał komunikat o przekroczeniu limitu rekordów ustawionego dla tabel przestawnych (które są notabene miniaturowymi kostkami OLAP) i w takim przypdku pozostało użycie metody 2.

Jeśli często filtrujemy po jakimś fakcie na poziomie wymiaru należało by sie zastanowić czy nie przeprojektować hurtownię/ repozytorium OBI EE (a w zasadzie jednego z jej wymiarów) tak by w tabeli wymiarów dla każdego rekordu przechowywać zagregowany na odpowiednim poziomie dany fakt  po którym w łatwy sposób będzie można filtrować. W tym momencie ten sam fakt będziemy mieć w tabeli wymiarów (jako  zagregowanie) i w tabeli faktów jako policzalny/sumowalny  fakt .

Czuwaj

Możemy jadnak właczyć debugowanie BI Publishaera w tym celu należy zlokalizować na serwerze aplikacji udostępniającym Publishera scieżkę gdzie zainstalowano Java SE Development Kit (JDK) Dla systemu windows moze to być np:

c:\Program Files\Java\jdk1.6.0_10\jre\lib\xdodebug.cfg”

(jak się dowiedziałem ostatnio z metalinka OBIEE nie supportuje Javyw wersji 1.6 ale nie mówcie o tym nikomu)

tworzymy plik xdodebug.cfg w poniższej scieżce:

c:\Program Files\Java\jdk1.6.0_10\jre\lib\xdodebug.cfg”

Do pliku tego wpisujemy co następuje:

LogLevel=STATEMENT
LogDir=c:\OracleBI\xmldebug

Gdzie LogDir to katalog gdzie będziemy się składować logi ( w szczególności plik xdo.log – który zawiera SQLe wysyłane do bazy). Oczywiście taki katalog musi istnieć na serwerze.
Przeładowywujemy serwer aplikacji (OAS,OC4J) i powtarzamy błąd w BI Publisher by po chwili wychwycić go w logach, naprawić, poprawić i zapomnieć.

Znalazłem jeszcz następujący opis plików dostępnych w katalogu logowania:

xdo.log : główny plik logu
xdo_…xsl : XSL-FO wersja templateu RTF
xdo_…xml : Dane w postaci XML uzyte w raporcie
xdo_…fo : temporary file created by the FO Engine (only RTF / XSL templates)
xdo_…out : output file (may not exist in case of complete failure)

Jak widać wstawiłem dwa wymiary i kilka faktów w tym jeden jest faktem obliczanym z formułą:

Zróbmy z tego pivota używając do tego dostępnych na raporcie miar i jednego z faktów

i już mamy gotowy raport pokazujący zyski każdego sklepu w poszczególnych miesiącach 2008 roku.

I teraz sedno sprawy

Dodajmy do raportu selektor kolumn – jeśli dodamy selektor do obu wymiarów i faktu będziemy mieli (a w zasadzie użytkownik końcowy będzie miał) możliwość dowolnego wyboru co ma być w kolumnie rzędzie i jaki fakt ma być na przecięciu

Od tej chwili użytkownik na pulpicie może dowolnie wybierać dane wyświetlane w tabelce (na jednej stronie pulpitu – zamiast na kilu podstronach). Oczywiście możliwość wyboru ograniczoną tylko do kolumn zdefiniowanych w selekorze.

np zysk w podziale obszar na typ klienta

lub ten sam przekrój ale inny fakt to jest Ilosc wyfakturowaną

Jak widać do selektora kolumn możemy dodać dowolną z kolumn dostępną w repozytorium – nie musi być ona składową pierwotnego raportu możemy wybrać dowolną kolumnę z repozytorium

Ale………

No właśnie czy czegoś wam tu nie brakuje – a co z kolumnami które z tak mozolnie wyliczamy na raporcie ( różnymi Case’mi filtrami mnożeniami dodawaniami potęgowaniami i czym tam można sobie jeszcze wyobrazić)

Teoretycznie na pierwszy rzut oka nie można … a jednak od czegóż pocciwy XML dostępny poprzez zaawansowane funkcje raportu (przepraszam “moich” użytkowników – wiem że nie widzicie tej zakładki)

Zróbmy więc mały example i wystawmy możliwość przełączenia faktu z zysku (Revenue) na kolumnę obliczaną w raporcie którą jest Avg revenue będące ilorazem revenue przez quantity

Na początek wyciągnijmy sobie XMLa z zawansowanych do jakiegoś edytora najlepiej z podświetlaną składnią XML.

Szukamy składni odpowiedzialnej za kolumn selektor czyli:

Doklejamy (lub przerabiamy dotychczasową kolumnę) nowy fakt który ma formułe obliczaną

wklejamy z powrotem w zakładce zaawansowane nie zapominając kliknąć guziczka ustaw XML – który zwaliduje nam XMLa

i oglądamy wynik

I co widzimy – Można już wybrać fakt obliczany na podstawie wzoru i faktycznie pokaże nam się średnia wartość zysku dla wymiarów jednakże opis w wyborze faktu jest niezadawalający ( być może tu tego nie widać ale w przypadku stosowania składni CASE we wzorze obliczeniowym nazwa ta będzie zupełnie nieczytelna). Wymieńmy ją więc na dowolną nazwę. Dokonamy tego oczywiście przez dalszą edycję XMLA i dodamy składnię odpowiedzialną za nazwę wybieranej kolumny:

Musimy dodać do XMLa w odpowiednim miejscu następujące znaczniki:

i mamy to co chcieliśmy czyli mozłiwość wybrania obliczenej kolumny z dowolną nazwą w selektorze wyboru.

W podobny sposób możemy przeprowadzić formatowanie tak wybranej kolumn (kolorki, miejsca po przecinku itp, itd)

Jednakże jak zwykle uważam że w nowej wersji Colum Selector powinien być wzbogacony o wybór formatu i możliwość wprowadzenia funkcji.

Bezt Regardz

Jednakże wynik moze nam sie leciutko “rojechać” tzn liczba będzie zawinięta.

Dzieje się tak gdzyż taka jest natura HTMLa który traktuje spacje jako potencjalne rozdzielenie tekstu i pozwala sobie na zawijanie liczb

Aby uniknąć zawijania oczywiście możemy

a)wyłączyć pokazywanie separatora tysięcy – wrócimy do punktu wyjścia – liczba będzie znów nieczytelna

b) zastosować separator w postaci np kropki – jak ktoś lubi

c) użyś styli CSS

w tym celu należy we właściwościach kolumny w sekcji niestandardowe style CSS wpisać poniższy tekst

white-space: nowrap

i mamy sformatowany tekst bez zawijania na spacjach

Użyteczne chociaż moim zdaniem w OBIEE powinnien byc dostepna wlasciwość rodzaju (checkbox) : nie zawijaj tekstu

CU

A więc w schemacie tym mamy wymiar sklepu “Market”

Jak widać mamy tutaj Distrykty (nazwijmy je Oddziałami) oraz sklepy. Załóżmy że pracownik centrali chce obserwować wyniki na poziomie oddziałów  zaś kierownicy oddziałów chcą obserwowac wyniki swoich sklepów. Jak napisałem wyżej chcemy aby odbyło sie to na wspólnym dla wszystkich pulpicie pt: Dochody

W tym celu musimy (o ile juz nie mamy) przypisac poszczególnych kierowników Oddziałów do wspolnej grupy  która nazwiemy Oddzialy

na potrzeby tego tematu założę użytkownika Oddziału 1 o nazwie: Adam.Nowak

Dodam też tego uzytkownika grupy ODDZIALY

Stwórzmy dwa raporty jeden na potrzeby pracownika centrali  – nazwiemy go Dochody w oddziałach

Drugi na potrzeby Oddziału 1 – nazwiemy go Dochody oddzału 1

Dodatkowo zróbmy prosty raport który będzie zwracał wynik o ile zaloguje się ktoś z oddziału

W tym celu użyjemy zmiennej GROUP która wyświetla nam do jakich grup prynależy uzytkownik oraz funkcji LOCATE która jest podobna do Funkcji INSTR znanej z wielu języków (w tym SQL) i  zwraca numer znaku szukanego ciągu

Nasza funkcja wyglądać będzie następująco

LOCATE(VALUEOF(NQ_SESSION.GROUP),'ODDZIALY')

a cały raport tak:

Nieważna jest zawartość takiego raportu ważne że zwróci on wiersze wyłacznie gdy uzytkownik będzie należał do grupy ODDZIALY w innym pryzpadku funkcja LOCATE zwróci zero i rekordy zostaną odfiltrowane (brak wyników)

Raport ten posłuży nam jako raport warunkowy nawigacji sterowanej sekcji na pulpicie.

Stwórzmy więc pulpit o nazwie DOCHODY. Jak wspomniałem idea tego pulpitu że jest to jeden wspólny dla całej firmy pulpit do monitorowania dochodów lecz zależnie od stopnia manadżera widzi on różne raporty (bardziej szczegółowe dla manadżera oddziału , mniej szczegółowe dla manadżera w centrali.

Umieśćmy na tym pulpicie dwie sekcje i rozmieśćmy oba raporty: Dochody w oddziałach i Dochody oddzału 1 – każdy w osobnej sekcji.

Teraz każdą z sekcji oznaczamy iż ma byś sterowana poprzez funkcję Nawigacji Sterowanej. Jako raport sterujący wybieramy raport z funkcją LOCATE utworzony poprzednio i dla pierwszej sekcji oznaczamy iż sekcja ma się pokazywać gdy raport nie zwaraca żadnych wierszy

Natomiast w drugiej sekcji  ustawmy odwrotnie czyli opcję Jeśli raport zwraca wiersze

Teraz możemy przetestować pulpit. Logując się jako użytkownik nie należący do grupy ODDZIALY zobaczymy pulpit z raportem przeznaczonym dla centrali:

Natomiast logując się jako pracownik Oddziału Adam Nowak widzimy pulpit z raportem przygotowanym dla oddziału:

Zauważmy że raport przeznaczony dla oddziału wstępnie przefiltrowaliśmy dla oddziału 1 – oczywiście powiiniśmy zastosować dynamiczne filtrowanie w zależnośći z jakiego oddziału pochodzi uzytkownik logujący się do OBI – tak aby Manager Oddziału 1 widział dane Oddziału 1, Manager oddziału 2 – dane oddziału 2 itd. Zagadnienie to jest szczegółowo poruszone w tutorianu dostępnym na OTN ie – Ja też za parę dni sprobuję pokazać/ przypomnieć jak dla naszego stworzonego pulpitu filtrować dane dynamicznie.

Uff – cięzko mi idzie ale mam nadzieję że się rozkręcę

Wynik dla takiego zapytanie (ograniczonego do dwóch lat) jest mało satysfakcjonujący:


A gdybyśmy chcieli sprawdzić ile było takich faktur w poszczególnym roku (2007,2008)
No dobrze ale przecież nie mamy wyciągniętych przez IT pól “Rok daty faktur” ani innych jak “Miesiąc daty faktury”. Stwórzmy je sobie więc sami stosując funkcję

(dla przypomnienia funkcję wpisujemy stosując ten przycisk na kolumnie:)

A funkcja pobierająca rok z pola daty wygląda następująco:

YEAR(data)

Nie zapomnijmy kliknąć w pole nagłówek niestandardowy i zmienić nazwę na krótką i treściwą (Kółka na czerwono) – gdy tego nie zrobimy to nagłowkiem kolumny będzie nieczytelna cała funkcja.
Zgrupowany po roku wynik
jest całkiem czytelny i co najwazniejsze miesci sie na jednej stronie – o czym fani excela nie chcą nawet słyszeć
Co jednak gdy chcielibyśmy pogrupować dodatkowo po miesiącach. Oczywiscie zastosujemy analogiczną funkcję tylko wyciągającą numer miesiąca z pola daty

MONTH(data)

UWAGA. nie możemy zamienić funkcji YEAR z poprzedniego przykładu na MONTH gdyż tym sposobem poagregujemy na poziomie miesięcy ale wszystkich lat razem
czyli będziemy mieli w wyniki ilość ze stycznia lat 2007 i 2008 w następnym wierszu ilośc z lutego 07 i 08 itd (no chyba żę tak chcemy). Musimy dodać dodatkową kolumnę aby pozostawić rok i rozróżnić miesiąca w obrębie roku. W tym celu

• klikamy na dowolną kolumnę (proponuję tę samą którą będziemy obrabiać czyli datę faktury)
• zmieniamy funkję tę kolumny na MONTH(Przedmiot.”Data faktury”)
• zmieniamy nagłówek kolumny tak jak poprzednio

Należy pamiętać o poprawnej (naturalnej kolejności kolumn tj Rok, miesiąć, iloś). Oczywiście można sobie dowolnie inaczej poprzestawiać kolumny wynik oczywiści będzie prawdziwy  ale sens wątpliwy:

W prosty sposób za pomoca tabeli przestawnej możemy sobie taki widok przedstawić w inny sposób:

Prawda że łatwo szybko i przyjemnie

Co jeszcze możemy wyciągnąć z dat? Proponuję nacisnąć przycisk “funkcje” w polu edycji funkcji/formuły kolumny

znajdziemy tamm.in:

• funkcje zwracającą aktualną dzisiejszą datę CURRENT_DATE
• funkcję odejmującą dwie daty (np aby zbadać ile dni minęło od jakiejś daty do dziś lub pomiędzy datami TIMESTAMP_DIFF
• funkcję dodającą/odejmującą zadany okres (dni, miesiące, lata) do daty zwracającą kolejną datę: TIMESTAMP_ADD
• i wiele innych

O arytmetyce na datach zrobięosobny wpis.

Kolejną sprawą którą chcielibyśmy usprawnić w naszym raporcie jest połączenie roku i miesiąca w jedną kolumnę aby otrzymać ciągi YYYY-MM

wydawało by się żę można połączyć w funkcji te dwie poprzednie funkcje. Ciągi łączy się znakami ‘||’ czyli zastosować coś o takiej składni:

YEAR(Przedmiot.”Data faktury”)||’-'||MONTH(Przedmiot.”Data faktury”)

niestety nie zadziała – zobaczymy na czerwono bład mówiący nam o tym: “że cyferek (jkaim są rok i miesiac) nie można łączyć w łańcuchy tekstowe znakiem ‘||’ ”
należy więc przed połączeniem zamienić cyferkę roku i miesiąca na format tekstowy stosujemy tutaj funkcję CAST która konwertuje nam formaty na zadany

CAST(wartość AS VARCHAR(n))

mamy więc

CAST(YEAR(Przedmiot.”Data faktury”) AS VARCHAR(4))||’-'||CAST(MONTH(Przedmiot.”Data faktury”) AS VARCHAR(2))

i wynik:

Wszystko fajnie tylko coś jest nie tak – sortowanie – a to za sprawą tego że zamiast miesięcy 01,02,03 mamy cyfry 1,2,3.
Jak to obejść. Jest kilka sosobów jednym z nich jest użycie funkcji

CASE WHEN….THEN…ELSE…END

o czym kiedy indziej a drugim łatwiejszym w tym przypadku jest całkowicie odmienna funkcja niz stosowane do tej pory MONTH i YEAR albowiem możemy użyć funkcji “wycinającej pierwsze 7 znaków daty otrzymując w ten sposób z formatu YYYY-MM-DD format YYYY.
Zastosujemy tu funkcję SUBSTRING która wycina ciąg tekstowy o zadanej długośći począwszyod zadanej pozycji z innego ciągu tekstowego (w naszym przypadku daty zamienionej na tekst)

SUBSTRING(text FROM pozycja FOR długość)

w naszym przypadku musimy pamiętać że data to nie format tekstowy i przed użyciem funkcji SUBSTRING musimy ją zamienić na tekst czyli:

CAST(Przedmiot.”Data faktury” AS VARCHAR(10))

to zwróci nam datę w postaci YY/MM/DD (tak jest ustawione na serwerze format daty)
teraz musimy “wykroic” z tego tekstu ciąg YY/MM

SUBSTRING(CAST(Przedmiot.”Data faktury” AS VARCHAR(10)) FROM 1 FOR 5)

i wynik:

możemy użyć jeszcze funkcji REPLACE zamieniając znak ‘/’ na ‘-’

REPLACE(SUBSTRING(CAST(Przedmiot.”Data faktury” AS VARCHAR(10)) FROM 1 FOR 5),’/',’-')

otrzymujemy:

Oraz dokleic z przodu “20″

‘20′||REPLACE(SUBSTRING(CAST(Przedmiot.”Data faktury” AS VARCHAR(10)) FROM 1 FOR 5),’/',’-')

by otrzymać w pełni poprawni i dobrze posortowany wynik pogrupowany dla miesięcy w latach

W kolejnych wpisach spróbuję przybliżyć arytmetykę na datach i funkcę CASE WHEN… THEN… ELSE… END