CODEWITHSUNDEEP
sqlsql-server-2008-r2ssasmdxolap
Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

intersect and topcount and order function in MDX query

I use SQL Server 2008 R2 and i use SSRS and i use Adventure Work DataBase.

I write this MDX query for get 10 city that were in top ten in both years 2003 , 2004.

with 
set [Best Cities in CY 2003/2004] as
    intersect(
        order(
            topcount(
                [Customer].[Customer Geography].[City],
                10,
                (
                    [Measures].[Internet Sales Amount],
                    [Date].[Calendar].[Calendar Year].[CY 2003]
                )
            ),
            [Measures].[Internet Sales Amount],
            bdesc
        ),
        order(
            topcount(
                [Customer].[Customer Geography].[City],
                10,
                (
                    [Measures].[Internet Sales Amount],
                    [Date].[Calendar].[Calendar Year].[CY 2004]
                )
            ),
            [Measures].[Internet Sales Amount],
            bdesc
        )
    )
Select [Measures].[Internet Sales Amount] on columns,
       [Best Cities in CY 2003/2004] on rows
From [Adventure Works]
Where 
{
    [Date].[Calendar].[Calendar Year].[CY 2003],
    [Date].[Calendar].[Calendar Year].[CY 2004]
}

But i want get list of cities that internet sales has decreased by 35% compared to the previous year and cities among the top 10 cities in the same year as well.

How i can get this result?

Upvotes: 1

Views: 2812

Answers (1)

FrankPl
FrankPl

Reputation: 13315

You would use Filter for this:

with 
set [Best Cities in CY 2003/2004] as
    filter(
        intersect(
            topcount(
                [Customer].[Customer Geography].[City],
                10,
                (
                    [Measures].[Internet Sales Amount],
                    [Date].[Calendar].[Calendar Year].[CY 2003]
                )
            ),
            topcount(
                [Customer].[Customer Geography].[City],
                10,
                (
                    [Measures].[Internet Sales Amount],
                    [Date].[Calendar].[Calendar Year].[CY 2004]
                )
            )
        ),
        ([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].[CY 2004])
        /
        ([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].[CY 2003])
        - 1.0
        < -0.35
    )
Select [Measures].[Internet Sales Amount] on columns,
       [Best Cities in CY 2003/2004] on rows
From [Adventure Works]
Where 
{
    [Date].[Calendar].[Calendar Year].[CY 2003],
    [Date].[Calendar].[Calendar Year].[CY 2004]
}

Upvotes: 3

Related Questions