SSAS多维数据集MDX编写求助：层级下Top10/Bottom10客户收入占比

Hey there! I’ve built plenty of these sorts of SSAS reports in Visual Studio, so let’s get this sorted for you. The key here is making sure the MDX adapts automatically to whatever level of the Time hierarchy you’re viewing, and correctly calculates the income percentage relative to that time level’s total.

Here’s a complete MDX query that works seamlessly in Visual Studio (it’s optimized for multidimensional cubes, which matches your setup):

WITH 
  -- Calculate total income for the currently selected time level (day/month/quarter/year)
  MEMBER [Measures].[Total Income for Time Level] AS 
    Sum(
      [Time].CurrentMember,
      [Measures].[Income]
    )
  -- Calculate each customer's income as a percentage of the time-level total
  MEMBER [Measures].[Income Percentage of Total] AS 
    Divide(
      [Measures].[Income],
      [Measures].[Total Income for Time Level],
      0
    ), FORMAT_STRING = "Percent"
  -- Label rows to distinguish Top 10 vs Bottom 10
  MEMBER [Measures].[Rank Category] AS 
    IIF(
      [CustomerId].[CustomerId].CurrentMember IS NULL,
      "",
      IIF(
        [Measures].[Income] = TopCount(NonEmpty([CustomerId].[CustomerId].[CustomerId].Members, [Measures].[Income]),10,[Measures].[Income]).Item([CustomerId].[CustomerId].CurrentMember).Item(0).Properties("Value"),
        "Top 10",
        "Bottom 10"
      )
    )
-- Get top 10 customers by income (filter out those with no income)
SET [Top 10 Customers] AS 
  TopCount(
    NonEmpty([CustomerId].[CustomerId].[CustomerId].Members, [Measures].[Income]),
    10,
    [Measures].[Income]
  )
-- Get bottom 10 customers by income
SET [Bottom 10 Customers] AS 
  BottomCount(
    NonEmpty([CustomerId].[CustomerId].[CustomerId].Members, [Measures].[Income]),
    10,
    [Measures].[Income]
  )
-- Combine both sets into one for the report
SET [Combined Top/Bottom] AS 
  Union([Top 10 Customers], [Bottom 10 Customers])
SELECT 
  {
    [Measures].[Income],
    [Measures].[Total Income for Time Level],
    [Measures].[Income Percentage of Total],
    [Measures].[Rank Category]
  } ON COLUMNS,
  [Combined Top/Bottom] ON ROWS
FROM [YourCubeName] -- Replace with your actual cube name
WHERE ([Time].CurrentMember) -- Adapts to whatever time member is selected

Let’s break down what each part does: #

• [Total Income for Time Level]: This dynamically calculates the total income for whichever time member you’re viewing—whether that’s a specific day, month, quarter, or year—by summing income across all customers in that period.
• [Income Percentage of Total]: Uses the Divide function to get each customer’s share of the total income for the selected time level. The 0 parameter prevents divide-by-zero errors, and FORMAT_STRING ensures it displays as a percentage in your report.
• [Rank Category]: Adds a clear label to each row so you can easily tell if a customer is in the Top 10 or Bottom 10.
• [Top 10 Customers] / [Bottom 10 Customers]: Uses TopCount and BottomCount to pull the relevant customer sets, and NonEmpty filters out customers with no income to avoid blank rows.
• WHERE Clause: [Time].CurrentMember makes the query automatically adapt to whatever time level or member you’ve selected in your report (via a slicer, parameter, or hierarchy drilldown).

Visual Studio-specific tips: #

1. Replace [YourCubeName] with the exact name of your SSAS cube.
2. If your CustomerId dimension uses a different hierarchy name (e.g., [Customer].[CustomerId] instead of [CustomerId].[CustomerId]), adjust that in the sets and member references.
3. When setting up this query as a dataset in Visual Studio Report Builder, you can add a Time dimension parameter to let users select their desired time period—this query will automatically use that selection.
4. If you see syntax highlighting quirks in the Visual Studio MDX editor, don’t worry! As long as your dimension/measure names match your cube exactly, the query will run correctly.

内容的提问来源于stack exchange，提问作者Julia Gumina