V minulých dílech tutorialu jsme si
ukázali, jak si napsat svůj vlastní MDX dotaz. Stejně tak jsme si ukázali, jak
pracovat s funkcí currentmember a tvořit vlastní počítané členy (článek:
http://www.neoral.cz/2015/05/mdx-tutorial-3-pocitane-cleny.html).
Aby naše snažení přineslo i nějaké hmatatelné ovoce, ukažme si jak vlastní
dotaz dostat do reportu v SSRS (SQL Server Reporting Services). Některé
věci je lepší jednou ukázat, než se snažit psát dlouhé popisy. Tvorba
reportu je jednou z nich. Celý proces jsem zaznamenal se slovním
komentářem a můžete jej sledovat zde https://youtu.be/RFX84weMqA8.
Video není jen o MDX, ale ukazuji i pár tipů a
triků pro práci s parametry v Reporting Services. Při nahrávání jsem zapomněl
namapovat do hlavního dotazu parametr SUBCATEGORY. Princip je stejný jako u
parametru CATEGORY a nedostatek jsem napravil alespoň v textu článku.
Pojďme tedy tvořit report z kostky. Výsledkem našeho snažení bude report analýzy prodejů v čase s parametry Kategorie a Podkategorie produktů.
Co budeme potřebovat? V prvé řadě dataset pro hlavní report.
Zdrojový select pro hlavní dataset bez parametru vypadá:
SELECT
{[Measures].[Reseller Sales]
,[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
{[Dim Date].[Year].[Year]}*
[Dim Date].[Quarter].[Quarter]*
[Dim Date].[Month].[Month]
ON ROWS
FROM [MDX Tutorial]
Pokud chceme dotaz parametrizovat, narazíme na dva problémy. Zaprvé MDX jazyk nezná nic jako deklaraci
proměnných. Zadruhé Reporting Services
budou hodnotu parametru podsouvat do SSAS zdroje jako text. Textovou hodnotu můžeme převést na
člena funkcí StrToMember nebo na
množinu prvků funkcí StrToSet. Pro
parametry většinou použijete funkci StrToSet. Složitější je řešení problému
s chybějící deklarací. Pokud se na parametr odvolávám, nejdou mi načíst
pole v designeru, takže si chystám dataset tak aby se dal ladit v Management
Studiu. Nachystám si něco jako defaultní filter a parametr ve funkci StrToSet dočasně zakomentuji. Hodnoty parametrů se
zpravidla píšou do Subselectů kvůli omezení, že jedna hierarchie v MDX může
figurovat pouze na jedné ose.
Spustitelný dotaz z SSMS (SQL Server
Management Studio) kopíruji do schránky
SELECT
{[Measures].[Reseller Sales]
,[Measures].[Internet Sales]}
ON COLUMNS,
non empty
{[Dim Date].[Year].[Year]}*
[Dim Date].[Quarter].[Quarter]*
[Dim Date].[Month].[Month]
ON ROWS
FROM
(SELECT [Dim Product].[Category].&[Bikes]/*StrToSet(@CATEGORY)*/ ON
COLUMNS FROM
[MDX
Tutorial]
)
pravé tlačítko na datový zdroj Analysis
Services, query designer (přímo psát nejde), přepnutí ikonkou s trojúhelníkem
z design módu do textového, náhrada dotazu mým skriptem ve schránce, ok.
To by mělo načíst seznam polí na záložce fields. Tlačítko Fx mě dostane do
dotazu v textovém režimu, kde můžu umazat komentáře a získat dotaz
SELECT
{[Measures].[Reseller Sales]
,[Measures].[Internet Sales]}
ON COLUMNS,
non empty
{[Dim Date].[Year].[Year]}*
[Dim Date].[Quarter].[Quarter]*
[Dim Date].[Month].[Month]
ON ROWS
FROM
(SELECT StrToSet(@CATEGORY)
ON COLUMNS FROM
(SELECT StrToSet(@SUBCATEGORY)
ON COLUMNS FROM
[MDX
Tutorial]
)
)
Parametry CATEGORY a SUBCATEGORY musím vytvořit manuálně.
Zbývá napsat dva datasety, kterými budu parametry plnit.
Syntaxe datasetu plnící jednopoložkový parametr CATEGORY by mohla vypadat následovně
WITH MEMBER ParameterValue AS
[Dim Product].[Category].currentmember.uniquename
MEMBER ParameterLabel AS
[Dim Product].[Category].currentmember.name
SELECT
{ParameterValue
,ParameterLabel}
ON COLUMNS,
nonempty(
{[Dim Product].[Category].members}
,{[Measures].[Reseller
Sales],[Measures].[Internet Sales]}
)
ON ROWS
FROM [MDX Tutorial]
Počítaný člen ParameterValue vrací unikátní
identifikaci každého záznamu, ParameterLabel se odkazuje na name column, zde by
se dalo použít i MEMBER_CAPTION což je jméno po aplikaci translations pokud v kostce existují. Výraz na
řádcích vratí všechny členy atributové hierarchie včetně All membera, ale pouze
ty kategorie, které se prodávaly.
Dataset pro parametr SUBCATEGORY je
ekvivalentní, jen s odkazem na předchozí parametr.
WITH MEMBER ParameterValue AS
[Dim Product].[Subcategory].currentmember.uniquename
MEMBER ParameterLabel AS
[Dim Product].[Subcategory].currentmember.name
SELECT
{ParameterValue
,ParameterLabel}
ON COLUMNS,
nonempty(
{[Dim
Product].[Subcategory].[Subcategory]}
,{[Measures].[Reseller
Sales],[Measures].[Internet Sales]}
)
ON ROWS
FROM [MDX Tutorial]
WHERE StrToSet(@CATEGORY)
Hotový report si můžete stáhnout zde
Závěr:
Článek shrnuje tvorbu parametrizovaného Reporting
Services reportu vytvořéného nad daty z OLAP kostky, pro nějž jsme si
napsali všechny dataset. Všechny kouzla jsou vidět na videu, článek slouží spíš
jako doplněk kvůli dohledání „jak ten skript vypadal“
zdravim, mam dataset, kde si vybere z casove dimenze roky. To vse nactu do parametru v reportu. Dataset mi vrati jednotlive roky 2010,2011,2012,.. Ja potrebuju pridat jeste jednu hodnotu a to "n/a" a te nastavit rok 9999. Jedna se o parametr, kde se vybiram rok ke srovnani. Hodnota "n/a" slouzi k tomu, aby se vyjely jen aktualni data bez porovnani se zadanym. Muj MXD dotaz vypada zatim nasledovne
OdpovědětVymazatWITH MEMBER ParameterValue AS
[Time].[Rok].currentmember.uniquename
MEMBER ParameterLabel AS
[Time].[Rok].currentmember.name
SELECT
{ParameterValue
,ParameterLabel}
ON COLUMNS,
nonempty(
{[Time].[Rok].members - [Time].[Rok].[all]}
,{[Measures].[Platba]}
)
ON ROWS
FROM [Masek Pohledavky]
dekuji za pomoc
Zdravim,
OdpovědětVymazatomlouvam se za pozdni reakci, nejak mi notifikace zapadla v poste
WITH MEMBER ParameterValue AS
[Time].[Rok].currentmember.uniquename
MEMBER ParameterLabel AS
[Time].[Rok].currentmember.name
MEMBER [Time].[Rok].[all].[N/A] AS
9999
SELECT
{ParameterValue
,ParameterLabel}
ON COLUMNS,
nonempty(
{[Time].[Rok].allmembers - [Time].[Rok].[all]}
,{[Measures].[Platba]}
)
ON ROWS
FROM [Masek Pohledavky]
By snad melo fungovat
JN