Zulfiqar's web

mdx query: Slicer

Slicer or Tuple

You can get to a paricular cell either through a slicer (where) or a tuple. Here is an example:

select
from
[Adventure Works]
where
(
[Date].[Calendar].[Calendar Year].&[2003]
,
[Geography].[Geography].[Country].&[United States]
,
[Product].[Product Categories].[Category].&[1]
)
go
select
(
[Date].[Calendar].[Calendar Year].&[2003]
,
[Geography].[Geography].[Country].&[United States]
,
[Product].[Product Categories].[Category].&[1]
)
on 0
from
[Adventure Works]

HTH..

Happy Cubing,

ZULFIQAR SYED

mdx query: PERIODSTODATE

PERIODSTODATE FUNCTION

Here is an example on how to use periodstodate function in AS2005. Notice that q12004 starts from the beginning of the year in the following resultset.

with member measures.ptd as
sum(
    periodstodate(
            [Date]
            .[Calendar]
            .[Calendar Year]
                ,
            [Date]
            .[Calendar]
            .currentmember
    )
    ,[Measures].[Reseller Sales Amount]
),format_string = "currency"

select
{
    [Date].[Calendar].[Calendar Quarter].&[2003]&[2]
    ,
    [Date].[Calendar].[Calendar Quarter].&[2003]&[3]
    ,
    [Date].[Calendar].[Calendar Quarter].&[2004]&[1]
} on 0,
{
    [Measures].[Reseller Sales Amount]
    --,measures.yearago
    ,measures.ptd
} on 1
from
    [Adventure Works]

HTH..

Happy Cubing,

ZULFIQAR SYED

mdx query: LASTPERIODS

LASTPERIODS FUNCTION

I responded to a news group post regarding calculating last 7 days total. This function could come in handy as follows against AS2005 samples cube.

with
member measures.x as
sum(
        {
            lastperiods(
                7,
                [Date].[Calendar].[Date].&[629]   
            )
        }
        ,
        [Measures].[Internet Sales Amount]
)
select
{
    lastperiods(
        7,
        [Date].[Calendar].[Date].&[629]   
    )
} on 1
,
{
    [Measures].[Internet Sales Amount]
    ,
    measures.x
}
on 0
from
[Adventure Works]

HTH

Happy Cubing,

ZULFIQAR SYED

mdx query: ParallelPeriod

A very simple sample code for ParallelPeriod in AS2005 using adventure works samples.

with member measures.yearago as
(
    parallelperiod(
            [Date]
            .[Calendar]
            .[Calendar Year]
            ,1
            ,[Date]
            .[Calendar]
            .currentmember
    )
    ,[Measures].[Reseller Sales Amount]
),format_string = "currency"

select
{
    [Date].[Calendar].[Calendar Quarter].&[2002]&[1]
    ,
    [Date].[Calendar].[Calendar Quarter].&[2003]&[1]
    ,
    [Date].[Calendar].[Calendar Quarter].&[2004]&[1]
} on 0,
{
    [Measures].[Reseller Sales Amount]
    ,measures.yearago
} on 1
from
    [Adventure Works]

HTH..

Happy Cubing,

ZULFIQAR SYED

mdx query: now() function

I addressed date() function issue in a news group post. You could use now() instead and here is the sample code.

with
member measures.x as
"[Date].[Calendar].[Calendar Year].&["
+
cstr(year(now())-3)
+"]"
select
{
[Date].[Calendar].[Calendar Year].&[2002]
,
strtomember(measures.x)
}
on 1,
[Measures].[Reseller Sales Amount] on 0
from
[Adventure Works]

HTH..

Thanks

ZULFIQAR SYED

mdx query: Top 3 count

Top 3 count.

Check out this simple code script on how to use topcount to calculate lets say, top 3 count sales in canadian provinces.

select
[Measures].[Reseller Sales Amount]
on 0,
            [Geography].[Geography].[Country]
            .&
            canada
            .children
on 1
from
[Adventure Works]
go
select
[Measures].[Reseller Sales Amount]
on 0,
topcount(
            --set
            [Geography].[Geography].[Country]
            .&
            canada
            .children
            ,
            --count
            3
            ,
            --expression
            [Measures].[Reseller Sales Amount]
            )
on 1
from
[Adventure Works]

HTH..

Thanks

Happy Cubing,

ZULFIQAR SYED

mdx query: Adding member in WHERE

ADDING MEMBER IN WHERE CLAUSE

There was a question on the OLAP news group regarding adding an extra member to the where condition. Here is a very simple example that I posted that might help.

WITH
MEMBER MEASURES.X AS
    [Promotion].[Promotion].&[1]
SELECT
(
[Date].[Calendar].[Calendar Semester].&[2003]&[2]
.CHILDREN
)
ON 1,
(
[Measures].[Reseller Order Quantity]
,

[Product].[Product Categories].[Category]
)
ON 0
FROM
[Adventure Works]

WHERE   
{
    [Promotion].[Promotion].&[1]    
    ,    [Promotion].[Promotion].&[2]   

}

Thanks

ZULFIQAR SYED

mdx query: Where

WHERE condition is a very power ful feature in mdx query. The following query calculates all the product categories order quantites for 2nd quarter of 2003 with promotion known as "no discount".

Enjoy

SELECT
(
[Date].[Calendar].[Calendar Semester].&[2003]&[2]
.CHILDREN
)
ON 1,
(
[Measures].[Reseller Order Quantity]
,

[Product].[Product Categories].[Category]
)
ON 0
FROM
[Adventure Works]
WHERE [Promotion].[Promotion].&[1]

HTH..

Happy Cubing,

ZULFIQAR SYED

mdx query: Repeat Values

REPEAT VALUES

Few days ago there was a post in the OLAP news group regarding a particular measure being repeated in all the cells. I posted a possible approach to the problem. Here is the sample code.

with
member measures.x as
            (
                [Measures].[Reseller Order Count],
                [Date].[Calendar].currentmember.parent.firstchild
            )

select
{
    [Measures].[Reseller Order Count]
    ,    measures.x
}
on 0
,
[Date].[Calendar].[Calendar Quarter]
.&[2003]&[1]
.children
on 1
from
[Adventure Works]

HTH..

Thanks

ZULFIQAR SYED

mdx query: ancestor

ancestor

Here is an example on how to calculate ratio using ancestor

with
member measures.x as
(    [Measures].[Reseller Order Quantity])
    /([Measures].[Reseller Order Quantity]
        ,
     ANCESTOR(
        [Product].[Product Categories]
        .CURRENTMEMBER    ,
        [Product].[Product Categories].[Category]
        )   
    )
select
{
    [Measures].[Reseller Order Quantity]
     , measures.x
} on 0,
non empty
[Product].[Product Categories].[Subcategory]
on 1
from [Adventure Works]

HTH..

Happy Cubing,

ZULFIQAR SYED