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.

(upped) nicely put question, using a cube we can all play with. — whytheq, Aug 10 '16 at 08:39

whytheq · Accepted Answer · 2016-08-10T08:42:03.697

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:

Thanks whytheq for the answer with clear explanation. I was just wondering is it not possible to use the same user hierarchy and get the results? Thing is in the cube we have, the non user-defined attributes have been hidden. Would I have to use a subcube to query that? I am not very well acquainted with MDX. — VKarthik, Aug 11 '16 at 00:06
@VKarthik you cannot use the same hierarchy more than once in a select clause - this is a rule of mdx. I've had similar problems in the past - our cube designer had the attribute hierarchies hidden as he thought it was "tidier" - now they are unhidden and placed in a folder "attributes" within each dimension as they are very useful. In the AdvWrks they are visible, so visible is the suggested structure by microsoft. — whytheq, Aug 11 '16 at 07:13
Thanks for the update again. I will have the attributes unhidden as well. — VKarthik, Aug 12 '16 at 01:15

Petr Leschenok · Answer 2 · 2016-08-11T14:47:07.970

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

The user is not using subselects - why overcomplicate the query like this? — whytheq, Aug 12 '16 at 07:53
The query is really simple. But in actual operation in the report uses filters on data, etc. And they are easy to implement using subselect. We almost always use subselect. In the example, you can navigate using TOP for levels in a pivot table. — Petr Leschenok, Aug 12 '16 at 09:02

Incorrect results in TOPCOUNT query for some attributes

2 Answers2