StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Assuming the objects in your cube are named as follows:</p> <ul> <li>Seller attribute: <code>[DimSeller].[Seller]</code>,</li> <li>date hierarchy: <code>[DimTime].[Date]</code> with level <code>Month</code> containing the months,</li> <li>type attribute hierarchy: <code>[DimType].[Type]</code> with keys 1 and 2,</li> <li>the measure based on the <code>Amount</code> column has the name <code>Amount</code>,</li> <li>there is an attribute hierarchy <code>[Sale].[SaleId]</code> identifying different sales events (needed as I understand you want to count sales events, not sellers),</li> <li>and your cube is named <code>CubeName</code>.</li> </ul> <p>Then the following MDX should deliver what you want:</p> <pre><code>WITH Member [Measures].[SellerCount] AS Filter( [Sale].[SaleId].[SaleId].Members * { [DimType].[Type].&[1] } ( [DimSeller].[Seller].CurrentMember, [DimTime].[Date].CurrentMember, [DimType].[Type].&[2], Measures.Amount ) = 0 ).Count SELECT [DimSeller].[Seller].[Seller].Members ON COLUMNS, [DimTime].[Date].[Month].Members ON ROWS FROM [CubeName] WHERE [Measures].[SellerCount] </code></pre> <p>This assumes that there are no positive refunds, i. e. refunds with 0 amount do not count, and it cannot happen that several (possibly negative and positive) refunds in a month add up to 0.</p> <p>However, you could also implement this via an attribute e. g. "Has Refund In Same Month" and assuming a measure "Sales Event Count" just as count on the fact table, then users could make use of this in more or less every client tool that can access the cube, as there would not be any <code>WITH</code> clause needed: The MDX would simply be:</p> <pre><code>SELECT [DimSeller].[Seller].[Seller].Members ON COLUMNS, [DimTime].[Date].[Month].Members ON ROWS FROM [CubeName] WHERE ( [Measures].[Sales Event Count], [Sales].[Has Refund In Same Month].[Yes] ) </code></pre> <p>To implement this, you would add a column either as named column in the DSV, or maybe in a view on your table with the following SQL code:</p> <pre><code>CASE WHEN EXISTS ( SELECT 1 FROM table e WHERE DatePart(Month, e.Date) = DatePart(Month, mainTable.Date) AND DatePart(Year, e.Date) = DatePart(Year, mainTable.Date) AND e.Seller = mainTable.Seller ) THEN 'Yes' ELSE 'No' END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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