翻译介绍15个经典的MDX查询-08&09

8. For each product brand, what are the two top-selling products and what percentage of total sales do they make up? To answer this question, Listing 8's relatively complicated query uses a combination of a calculated member and the Generate() function. The calculated member determines the percentage of the brand's total unit sales that a brand's top two products make up. The Generate() function searches the list of all brands and returns a set of each brand's top two products and the percent of total value that those products account for.

查出每一种品牌销售最好的两种产品的销量额，以及分别占销售总额的百分比。Listing8相对比较复杂，综合运用了计算成员和Generate()函数。计算成员确定了每个品牌销售最好的前两个产品占所在品牌unit sales总量百分比。Generate函数查询每个品牌并返回每个品牌下销售最好的两个产品，以及每个产品的销售额和百分比。

 Listing_08.Determining Two Top-Selling Products.txt

说明：查出每种品牌 前2名 产品的销售记录，以及各自分别占所在品牌的百分比

 

with member [Measures].[PercTotalSales] as

' Sum( TopCount([Product].CurrentMember.Children, 2, [Unit Sales]), [Unit Sales] )

/([Product].CurrentMember, [Unit Sales])',

FORMAT_STRING = '##.0%'

select [Store].[(All)].Members on COLUMNS,

  Generate( [Product].[Brand Name].Members,

   Union(

      TopCount( [Product].CurrentMember.Children, 2, [Unit Sales] ) * {[Unit Sales]},

      { ([Product].CurrentMember, [PercTotalSales]) }

      )

   ) on ROWS

from Sales

9. Show all the product brands for the past four quarters, highlighting brands that sold in the bottom 10 percent for that quarter. Cell properties are a convenient way to perform exception highlighting (i.e., changing the font or color to draw the user's attention to important information) in a query. In Listing 9, I added the cell property FONT_FLAGS to the calculated member HLUnit Sales to boldface the unit sales numbers in the bottom 10 percent of all product brands for that quarter. Because a cell property's value can be an MDX expression, you can perform conditional logic to determine whether the font will be roman or boldface. In this case, the condition logic determines whether the current brand is in the bottom 10 percent by doing a set intersect with the full list of brands in the bottom 10 percent. If the intersect yields a count of 0, the brand isn't among the bottom members and will appear in a roman font. If the count is 1, the brand is among the bottom 10 percent, and the value will appear in boldface.

显示四个季度所有品牌的销售情况，高亮显示各个季度销售组成最少10%的品牌。单元格属性是在查询中突出展示异常数据的便捷方式（如，改变字体风格或者颜色以吸引读者对重要信息的注意）。Listing9，通过向计算成员HLUnit Sales添加单元格属性Font_FLAGS以黑体显示各个季度销售居于最低10%的品牌。由于一个MDX只能设置一个单元格属性，所以只能通过条件逻辑判断是显示罗马字体还是黑体。在本例中，使用的条件逻辑是判断当前品牌与组成最低10%的所有品牌的交集是否为空。如果产生的交集为0，说明该品牌不再组成10%的品牌中，罗马字体显示；如果交集是1，说明该品牌在这10%中，黑体显示。

 Listing_09.Highlighting Products in the Bottom 10 Percent.txt

说明：查出4个季度中，每个时期销售量在后10%的产品销售量，并显示为粗体

 

with set [LastQuarter] as 'Tail(Filter([Time].[Quarter].Members, Not

IsEmpty([Time].CurrentMember)))'

 set [Last4Quarters] as ' [LastQuarter].item(0).item(0).Lag(3) : [LastQuarter].item(0).item(0)'

 member [Measures].[HLUnit Sales] as '[Unit Sales]',

  FONT_FLAGS = 'iif( Count(

       Intersect( BottomPercent( [Product].[Brand Name].Members, 10, ([Unit Sales]) ),

{[Product].CurrentMember})

       ) = 0, 0, 1)'

select [Last4Quarters] on COLUMNS,

  [Product].[Brand Name].Members on ROWS

from Sales

where ([HLUnit Sales])

cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS