Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First, it's important to understand the MDX syntax, and how it related to the concepts of <a href="http://msdn.microsoft.com/en-us/library/aa216769%28v=sql.80%29.aspx">tuples, members and sets</a>.</p> <h2>Tuples</h2> <p>Using parentheses denotes a tuple:</p> <pre><code>( [Policy].[Policy Status].&amp;[Void], [Policy].[Tran Type].&amp;[Renewal], [Measures].[FK Policy Distinct Count] ) </code></pre> <p>A tuple can only include a single member from any hierarchy.</p> <h2>Sets</h2> <p>To retrieve results from multiple members in the same hierarchy, you must query for a set. An MDX set is denoted by braces:</p> <pre><code>{ [Policy].[Policy Status].&amp;[Void], [Policy].[Policy Status].&amp;[Policy] } </code></pre> <p>A set is, <a href="http://msdn.microsoft.com/en-us/library/aa216769%28v=sql.80%29.aspx">by definition</a>, </p> <blockquote> <p>an ordered collection of zero, one or more tuples.</p> </blockquote> <p>So, if you wish to query for the <code>[FK Policy Distinct Count]</code> measure against both of those members, the set's tuples must each include the measure:</p> <pre><code>{ ( [Policy].[Policy Status].&amp;[Void], [Measures].[FK Policy Distinct Count] ), ( [Policy].[Policy Status].&amp;[Policy], [Measures].[FK Policy Distinct Count] ) } </code></pre> <p>To simplify this expression, it is possible to <a href="http://msdn.microsoft.com/en-us/library/ms144816.aspx">crossjoin</a> two sets of different dimensionality:</p> <pre><code>{ [Policy].[Policy Status].&amp;[Void], [Policy].[Policy Status].&amp;[Policy], [Policy].[Policy Status].&amp;[Something], [Policy].[Policy Status].&amp;[Something else], [Policy].[Policy Status].&amp;[Yet another member] } * { [Measures].[FK Policy Distinct Count] } </code></pre> <h2>Excluding rows</h2> <p>Now that we can define sets, it's time to remove some members from one. In your example, it sounds like you want to start with a level (which, to the MDX engine, is just a predefined set in the cube which includes every member at that level of the hierarchy), and exclude certain members. MDX has lots of functions that operate on sets, and we're going to use <code>EXCEPT</code>.</p> <p>The <code>EXCEPT</code> function <a href="http://technet.microsoft.com/en-us/library/ms144900.aspx">takes two parameters</a>, the first being the set to remove from, and the second being the set which should be removed from the first. It returns a set.</p> <p>In this example, I'm going to assume <code>[Policy].[Policy Status]</code> is an attribute hierarchy, and that its sole level has the Unique Name of <code>[Policy].[Policy Status].[Policy Status]</code>.</p> <pre><code>EXCEPT( [Policy].[Policy Status].[Policy Status], { [Policy].[Policy Status].&amp;[Void], [Policy].[Policy Status].&amp;[Policy] } ) </code></pre> <p>This will return every member from the <code>[Policy].[Policy Status].[Policy Status]</code> level, except for <code>[Policy].[Policy Status].&amp;[Void]</code> and <code>[Policy].[Policy Status].&amp;[Policy]</code>.</p> <p>To get useful results, we can cross-join the result by a measure:</p> <pre><code>EXCEPT( [Policy].[Policy Status].[Policy Status], { [Policy].[Policy Status].&amp;[Void], [Policy].[Policy Status].&amp;[Policy] } ) * { [Measures].[FK Policy Distinct Count] } </code></pre> <h2>Using a set as a single member</h2> <p>Sets are nice, but sometimes all we want from them is to treat them as a single member, as in your calculated member requirement. To do this, we need to use an aggregation function. Aggregation functions take in a set and return a member that represents the entire set. </p> <p>There are a number of these, and the right one to use depends on the data stored in your cube: <code>MIN</code>, <code>MAX</code>, <code>COUNT</code>, and <code>SUM</code> are some of them (see "Numeric Functions" in <a href="http://msdn.microsoft.com/en-us/library/ms145970.aspx">the MDX Function reference</a> for a more complete list). In this example, I'll assume your dimension aggregates by using SUM:</p> <pre><code>SUM( EXCEPT( [Policy].[Policy Status].[Policy Status], { [Policy].[Policy Status].&amp;[Void], [Policy].[Policy Status].&amp;[Policy] } ), [Measures].[FK Policy Distinct Count] ) </code></pre> <p>Here, I have passed the measure to be aggregated as the second parameter to SUM.</p> <hr> <p>MDX is a complex language which supports many common and uncommon set operations. If you haven't already, I advise taking the time to read over the documentation available online, or grab yourself a good MDX book. There's a lot to know :)</p> <p>&lt;3</p>
    singulars
    1. This table or related slice is empty.
    1. POMDX Calculated member filter by dimension attribute
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTullo_x86
    1. USTullo_x86
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMDX Calculated member filter by dimension attribute
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for such detailed explanation. I have tried these queries in Calculated Member (Form View) field and non of them works. It throws following error: 'The function expects a string or numeric expression for the argument. A tuple set expression was used'
      singulars
      1. PO
      1. USilija veselica
    2. COTo use the collected results from a set in a single member, the set needs to be converted by using an aggregation function. The easiest one of these to use is `AGGREGATE`, since it automatically selects the right aggregation type (sum, minimum, maximum etc) for the set. Defining your calculated member as `AGGREGATE(SetExpression)` will allow you to use the results as a single member. I'll add this to the answer. <3
      singulars
      1. PO
      1. USTullo_x86
    3. COI have tried AGGREGATE but still returns error: 'Aggregate functions cannot be used on calculated members in the Measures dimension'. I literally copy/pasted your example from the answer and it doesn't work
      singulars
      1. PO
      1. USilija veselica
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload