Using Following Methods ,we can filter the data using MDX Query.
Different ways of filtering the data :-
/* HOW TO FILTER THE DATA */
-- Actual MDX
Query
SELECT {
[Measures].[Order Quantity]
} ON COLUMNS
,[Dim Date].[Calendar
Year].[Calendar Year]
ON ROWS
FROM [MSBI]
-- Example-1
SELECT [Measures].[Sales Amount]
ON COLUMNS
FROM [MSBI]
WHERE [Dim Date].[Calendar Year].&[2005]
--Example- 2
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,[Dim Date].[Calendar
Year].&[2005] ON ROWS
FROM [MSBI]
--Example- 3
SELECT {
[Measures].[Order Quantity]
} ON COLUMNS
,[Order
Date].[Calendar Hierachy].[Calendar Year].&[2005]
ON ROWS
FROM [MSBI]
-- IN operator
in SQL
--Example- 4
SELECT {
[Measures].[Order Quantity]
} ON COLUMNS
,{
[Order
Date].[Calendar Hierachy].[Calendar Year].&[2005]
, [Order
Date].[Calendar Hierachy].[Calendar Year].&[2006]
}
ON ROWS
FROM [MSBI]
--Example- 5
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,
{
[Dim
Date].[Calendar Year].&[2005]
,[Dim Date].[Calendar Year].&[2008]
} ON ROWS
FROM [MSBI]
-- BETWEEN or
RANGE operator in SQL
--Example- 6
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,
{
[Dim
Date].[Calendar Hierachy].&[2005] : [Dim Date].[Calendar
Hierachy].&[2008]
} ON ROWS
FROM [MSBI]
-- <> NOT
EQUEL operator in SQL
-- NOT IN YEAR
2008
--Example- 7
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,
{
[Order
Date].[Calendar Hierachy].[Calendar Year].MEMBERS
- [Order Date].[Calendar Hierachy].[Calendar
Year].&[2008]
} ON ROWS
FROM [MSBI]
--Example- 7
-- NOT IN
(2008,2009)
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,
{
[Order Date].[Calendar
Hierachy].[Calendar Year].MEMBERS
- {[Order Date].[Calendar Hierachy].[Calendar
Year].&[2008],
[Order
Date].[Calendar Hierachy].[Calendar Year].&[2009] }
} ON ROWS
FROM [MSBI]
-- HAVING
Function in MDX
-- Example -1
SELECT (
[Dim Customer].[Geography
Hierarchy].CHILDREN
,[Measures].[Order Quantity]
) HAVING [Dim Customer].[Geography Hierarchy] <>
[Dim Customer].[Geography Hierarchy].[France]
AND [Dim Customer].[Geography Hierarchy] <>
[Dim Customer].[Geography Hierarchy].[Canada]
ON COLUMNS
,{
[Order
Date].[Calendar Hierachy].[Calendar Year].MEMBERS
} ON ROWS
FROM [MSBI]
-- Example -2
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
}
ON COLUMNS
,{
[Order
Date].[Calendar Hierachy].[Calendar Year]
} HAVING [Order
Date].[Calendar Hierachy] = [Order Date].[Calendar Hierachy].[2005]
ON ROWS
FROM [MSBI]
-- Example -3
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
}
ON COLUMNS
,{
[Order
Date].[Calendar Hierachy].[Calendar Year]
} HAVING [Order
Date].[Calendar Hierachy] <> [Order Date].[Calendar Hierachy].[2005]
ON ROWS
FROM [MSBI]
-- Example -4
SELECT {
[Measures].[Order Quantity]
}
ON COLUMNS
,{
[Order
Date].[Calendar Hierachy].[Calendar Year]
} HAVING TRUE
ON ROWS
FROM [MSBI]
-- Example -5
SELECT {
[Measures].[Order Quantity]
}
ON COLUMNS
,{
[Order
Date].[Calendar Hierachy].[Calendar Year]
} HAVING FALSE
ON ROWS
FROM [MSBI]
-- Example -6
[LIKE Oerator in SQL Server]
-- THE MEMBERS
WHICH STARTS WITH "U"
SELECT {
[Measures].[Order Quantity]
}
ON COLUMNS
,{
[Dim
Customer].[Geography Hierarchy].[English Country Region Name]
} HAVING InStr([Dim Customer].[Geography Hierarchy].CURRENTMEMBER.PROPERTIES('MEMBER_CAPTION') ,'U')=1
ON ROWS
FROM [MSBI]
-- Example -7
-- THE MEMBERS
WHICH STARTS WITH "C"
SELECT {
[Measures].[Order Quantity]
}
ON COLUMNS
,{
[Dim Customer].[Geography
Hierarchy].[English Country Region Name]
} HAVING InStr([Dim Customer].[Geography Hierarchy].CURRENTMEMBER.PROPERTIES('MEMBER_CAPTION') ,'C')=1
ON ROWS
FROM [MSBI]
-- Example -8
SELECT {
[Measures].[Order Quantity]
} --HAVING [Measures].[Order Quantity]>1000
ON COLUMNS
,{
[Dim
Customer].[Geography Hierarchy].[State Province Name]
} HAVING [Measures].[Order Quantity]>1000
ON ROWS
FROM [MSBI]
/* & Refers to the KEY Column where as if we
remove & symbal then it will refers Name Column */
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,
{
[Dim
Date].[Calendar Year].[2005]
} ON ROWS
FROM [MSBI]
-- FILTER
FUNCTION
-- Example -8
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,[Dim Customer].[Geography
Hierarchy].[English Country Region Name]
ON ROWS
FROM [MSBI]
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,FILTER (
[Dim
Customer].[Geography Hierarchy].[English Country Region Name]
,[Measures].[Sales Amount] >
5000000
)
ON ROWS
FROM [MSBI]
-- Example -9
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,FILTER (
[Dim
Customer].[Geography Hierarchy].[English Country Region Name]
,[Measures].[Order
Quantity]<4000
)
ON ROWS
FROM [MSBI]
-- Example -10
-- FILTER
Function with OR operator
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,FILTER (
[Dim
Customer].[Geography Hierarchy].[English Country Region Name]
,[Measures].[Order
Quantity]<4000 OR [Measures].[Sales Amount]
> 5000000
)
ON ROWS
FROM [MSBI]
-- Example -11
-- FILTER
Function with AND operator
SELECT {
[Measures].[Sales Amount]
,[Measures].[Order Quantity]
} ON COLUMNS
,FILTER (
[Dim
Customer].[Geography Hierarchy].[English Country Region Name]
,[Measures].[Order
Quantity]>4000 AND [Measures].[Sales Amount]
> 5000000
)
ON ROWS
FROM [MSBI]
-- Another
Example for WHERE condtion
-- Only
Australia and Canada data
SELECT {
[Measures].[Order Quantity]
} ON COLUMNS
,[Dim Date].[Calendar
Year].[Calendar Year]
ON ROWS
FROM [MSBI]
WHERE
(
{
[Dim Customer].[English Country Region
Name].&[Australia]
,[Dim
Customer].[English Country Region Name].&[Canada]
,[Dim
Customer].[English Country Region Name].&[Germany]
}
,
{
[Dim Currency].[Currency Name].&[Swiss
Franc]
,[Dim
Currency].[Currency Name].&[US Dollar]
}
)
Thanks for Looking into this.....
No comments:
Post a Comment