Mdx generates complex sets

Order (expression, sorting metric value, DESC | ASC | bdesc | basc) function.

Hierarchize function. Sort all the rows and install their corresponding hierarchize

Bottomcount and topcount are similar, but they have different functions.. You can check

Head Function(Http://msdn.microsoft.com/zh-cn/library/ms144859 (V = SQL .105) returns the specified number of elements at the top of the SetAnd retained duplicate items.. The head function returns the specified number of tuples from the beginning of the specified set. And keep the order of elements. The default value of count is 1. If the number of specified tuples is less than 1, the head function returns an empty set. If the number of specified tuples exceeds the number of tuples in the Set, this function returns the original set.

Tail Function(Http://msdn.microsoft.com/zh-cn/library/ms146056.aspx), the tail function returns the specified number of tuples from the end of the specified set. The order of elements is retained. The default value of count is 1. If the number of specified tuples is less than 1, the function returns an empty set. If the number of specified tuples exceeds the number of tuples in the Set, this function returns the original set.

The item function returns a member.

 
Filter (Set_expression,Logical_expression)
 

 
Union (set, set), except T (set, set) first set is greater than second set, intersect (set, set) Union
 

Generate (Set_expression1,Set_expression2[, All])Apply a set to each member of another set, and then calculate the Union set for the obtained set. In addition, this function returns the concatenation string created by using a string expression to evaluate the set value.
 

Order

 
Select
 

 
{([Measures]. [reseller sales amount])}OnColumns,
 

 
Order(
 

 
{[Product]. [category]. [category]. Members }*
 

 
{[Product]. [subcategory]. [subcategory]. Members },
 

 
([Measures]. [reseller sales amount]),
 

 
Desc
 

 
)On Rows
 

 
From[Step-By-Step]
 

 
;
 

 

In the preceding query, the default order is used for the crosstab chart, and [measures] is specified later. [reseller sales amount]), but you have noticed that this column is not sorted by the specified value. Why? Because the default ASC and desc are hierarchical, in other words, the sorting of the previous members of these tuples is retained. To disrupt this hierarchy, you can use basc and bdesc (http://msdn.microsoft.com/zh-cn/library/ms145587 (V = SQL .105 )).

 

Hierarchize

 

Item Function

 
With
 

 
Member [measures]. [TopProduct Sales]As
 

 
{
 

 
Existing
 

 
Topcount (
 

 
[Product]. [product]. [product]. Members,
 

 
1,
 

 
([Measures]. [Internet sales amount])
 

 
)*
 

 
{[Measures]. [Internet sales amount]}
 

 
}. Item (0)
 

 
, Format_string = "currency"
 

Member [measures]. [TopProduct Name]As
 

 
{
 

 
Existing
 

 
Topcount (
 

 
[Product]. [product]. [product]. Members,
 

 
1,
 

 
([Measures]. [Internet sales amount])
 

 
)
 

 
}. Item (0). Item (0). Name
 

 
Member [measures]. [TopProductvalue name]As
 

 
{
 

 
Existing
 

 
Topcount (
 

 
[Product]. [product]. [product]. Members,
 

 
2,
 

 
([Measures]. [Internet sales amount])
 

 
)*
 

{[Measures]. [Internet sales amount]}
 

 
}. Item (1). Item (1). Name
 

 
Select
 

 
{
 

 
([Measures]. [Internet sales amount]),
 

 
([Measures]. [TopProduct Sales]),
 

 
([Measures]. [TopProduct Name])
 

 
, ([Measures]. [TopProductvalue name])
 

 
}OnColumns,
 

 
{
 

 
([Date]. [CalendarYear]. [CY 2001]):
 

 
([Date]. [CalendarYear]. [CY 2004])
 

}On Rows
 

 
From[Step-By-Step]
 

 
;
 

The item function is a very interesting function. Let's look at it first ([measures]. [TopProduct Sales]), which is the second column.

Is obtained. what does this mean. that's what I understand. returns the highest product ordered by Internet sales amount. multiply by the Internet sales amount metric value. get a square. you need to add the existing keyword. because the square needs to be cut. however. after testing, you can do it without adding it. it does not seem to need to be added.

Item (0). Of course, the first column of the square is returned. (It is actually a member. Because the top return is a product .),

In this case, we can see that the returned result is the 1205 one, which is only dispersed by every year.

For example, if the test result shows that item (0) is returned, but the two limitations above (understood as key) are meaningless, and only value is used.

If you want to obtain the above key. item (0 ). obtain road-150 red, 62, item (0) from item (0 ). item (1) gets Internet sales amount. it's fun. it is also very esoteric. but it returns a member, remember.

 

Filter filtering. Different filter and where. Where can only be used for dimension slicing. filter can be switched to tuples because of the true and false expressions. For example.

 

Generate (Set_expression1,Set_expression2[, All]) apply a set to each member of another set, and then calculate the union of the obtained set. In addition, this function returns the concatenation string created by using a string expression to evaluate the set value.

This is the result of a normal crossjoin. All sets cannot be obtained.

See http://www.cnblogs.com/downmoon/archive/2011/11/22/2258963.html for details