HEAD Function :-
The HEAD Function is used to select the required number of records from the source data.
Example: If you want to get Top 10 Countries Based on Sales Orders.
-- HEAD FUNCTION
--Example-1
SELECT
{
[Measures].[Sales
Amount]
} ON COLUMNS,
HEAD (
[Dim
Customer].[Geography Hierarchy].[State Province Name]
,10
) ON ROWS
FROM [MSBI]
--Example-2
-- Wrong
Results
SELECT
{
[Measures].[Sales
Amount]
} ON COLUMNS,
ORDER (
HEAD (
[Dim
Customer].[Geography Hierarchy].[State Province Name]
,10
)
,[Measures].[Sales
Amount]
,BDESC
) ON ROWS
FROM [MSBI]
--Example-3
-- Correct Answer
SELECT
{
[Measures].[Sales
Amount]
} ON COLUMNS
, HEAD
(
ORDER(
[Dim Customer].[Geography
Hierarchy].[State Province Name]
,[Measures].[Sales Amount]
,BDESC
)
,10
)
ON ROWS
FROM [MSBI]
TAIL Function :-
The TAIL Function is used to select the required number of records from the source data.
Example: If you want to get Bottom 10 Countries Based on Sales Orders.
-- TAIL FUNCTION
--Example-1
SELECT
{
[Measures].[Sales Amount]
} ON COLUMNS,
TAIL (
[Dim Customer].[Geography Hierarchy].[State Province Name]
,5
) ON ROWS
FROM [MSBI]
--Example-2
-- Wrong Results
SELECT
{
[Measures].[Sales Amount]
} ON COLUMNS,
ORDER (
TAIL (
[Dim Customer].[Geography Hierarchy].[State Province Name]
,5
)
,[Measures].[Sales Amount]
,BDESC
) ON ROWS
FROM [MSBI]
--Example-3
SELECT
{
[Measures].[Sales Amount]
} ON COLUMNS
, TAIL (
ORDER(
[Dim Customer].[Geography Hierarchy].[State Province Name]
,[Measures].[Sales Amount]
,BDESC
)
,5
)
ON ROWS
FROM [MSBI]
--Example-4
-- Correct Results with NON EMPTY Function
SELECT
{
[Measures].[Sales Amount]
} ON COLUMNS
, TAIL (
ORDER(
NONEMPTY([Dim Customer].[Geography Hierarchy].[State Province Name])
,[Measures].[Sales Amount]
,BDESC
)
,5
)
ON ROWS
FROM [MSBI]
Thanks for Looking into this.....
No comments:
Post a Comment