Incorrect results in TOPCOUNT query for some attributes

Question

I am trying to find out which year had the highest reseller sales across each of the State-Province. I am using Adventure Works DW 2008 R2 database.

Here is the query that I have got -

SELECT { [Reseller Sales Amount] } ON COLUMNS,
{
  Generate (
    [Geography].[Geography].[Country].Members,
    TopCount (
      Order (
        Descendants ( 
           [Geography].[Geography].CurrentMember
         , [Geography].[Geography].[State-Province] 
        ) 
       *[Date].[Calendar].[Calendar Year].Members,
        [Reseller Sales Amount],
        DESC
      ),
      1
    )
  )
} ON ROWS
FROM [Adventure Works]

When it comes to France it's displaying (null) as result set. Also why is it not listing all the State-Provinces but only select few of them? Is it also possible to get the Country alongside the results. When I tried another cross-join I get error saying Geography hierarchy already exists.

Answer 1

We will explore the universal option realization of TOP-filters on the levels.

TOP-rules can be set for every level.

It is used for detailed of analysis of data (Expand, Drilldown).

Aggregates are formed based on TOP.

Setting the Sort order will do, as long as the data is sorted in the most Pivot Table.

SELECT
  { [Measures].[Reseller Sales Amount] }
  DIMENSION PROPERTIES PARENT_UNIQUE_NAME , HIERARCHY_UNIQUE_NAME , CUSTOM_ROLLUP , UNARY_OPERATOR , KEY0 ON 0
, ORDER
  ( HIERARCHIZE
    ( HIERARCHIZE ( [Geography].[Geography].Levels ( 0 ).Members )
    )
  , ([Measures].[Reseller Sales Amount])
  , BDESC
  )
  DIMENSION PROPERTIES PARENT_UNIQUE_NAME , HIERARCHY_UNIQUE_NAME , CUSTOM_ROLLUP , UNARY_OPERATOR , KEY0 ON 1
FROM
( SELECT
    { Generate
      ( [Geography].[Geography].Levels ( 3 ).Members
      , TopCount
        ( Filter
          ( [Geography].[Geography].CurrentMember.Children
          , NOT IsEmpty ( [Measures].[Reseller Sales Amount] )
          )
        , 3
        , [Measures].[Reseller Sales Amount]
        )
      )
    } ON COLUMNS
  FROM
  ( SELECT
      { Generate
        ( [Geography].[Geography].Levels ( 2 ).Members
        , TopCount
          ( Filter
            ( [Geography].[Geography].CurrentMember.Children
            , NOT IsEmpty ( [Measures].[Reseller Sales Amount] )
            )
          , 3
          , [Measures].[Reseller Sales Amount]
          )
        )
      } ON COLUMNS
    FROM
    ( SELECT
        { Generate
          ( [Geography].[Geography].Levels ( 1 ).Members
          , TopCount
            ( Filter
              ( [Geography].[Geography].CurrentMember.Children
              , NOT IsEmpty ( [Measures].[Reseller Sales Amount] )
              )
            , 3
            , [Measures].[Reseller Sales Amount]
            )
          )
        } ON COLUMNS
      FROM
      ( SELECT
          { Generate
            ( [Geography].[Geography].Levels ( 0 ).Members
            , TopCount
              ( Filter
                ( [Geography].[Geography].CurrentMember.Children
                , NOT IsEmpty ( [Measures].[Reseller Sales Amount] )
                )
              , 3
              , [Measures].[Reseller Sales Amount]
              )
            )
          } ON COLUMNS
        FROM
        ( SELECT
            { [Geography].[Geography].Levels ( 0 ).Members } ON COLUMNS
          FROM [Adventure Works]
        )
      )
    )
  )
)
CELL PROPERTIES BACK_COLOR , CELL_ORDINAL , FORE_COLOR , FONT_NAME , FONT_SIZE , FONT_FLAGS , FORMAT_STRING , VALUE , FORMATTED_VALUE , UPDATEABLE , ACTION_TYPE

Answer 2

To get rid of the null you can use BDESC rather than DESC. The extra B means that all natural hierarchy order will be Broken

SELECT { [Reseller Sales Amount] } ON COLUMNS,
{
  Generate (
    [Geography].[Geography].[Country].Members,
    TopCount (
      Order (
        Descendants ( 
           [Geography].[Geography].CurrentMember
         , [Geography].[Geography].[State-Province] 
        ) 
       *[Date].[Calendar].[Calendar Year].Members,
        [Reseller Sales Amount],
        BDESC //<<< 
      ),
      1
    )
  )
} ON ROWS
FROM [Adventure Works];

To add in country and avoid the error that you are seeing use the unused attribute hierarchy [Geography].[Country] rather than the user hierarchy [Geography].[Geography] which you already have on the 1 axis:

SELECT 
  {[Reseller Sales Amount]} ON COLUMNS
 ,{
    Generate
    (
      [Geography].[Geography].[Country].MEMBERS
     ,TopCount
      (
        Order
        (
            [Geography].[Country].[Country]*
            Descendants
            (
              [Geography].[Geography].CurrentMember
             ,[Geography].[Geography].[State-Province]
            )*
            [Date].[Calendar].[Calendar Year].MEMBERS
         ,[Reseller Sales Amount]
         ,bDESC
        )
       ,1
      )
    )
  } ON ROWS
FROM [Adventure Works];

Results are the following: