November 27, 2008 at 9:06 pm
Hi Guys,
I need a help for the following situation.
I have a measure which is the total amount of sales of the products
I have a DimProduct dimension tables which has 9 products: product1, product2, product3... product9
Now I want to know the total amount of sales made by customers who only buy product1 and don't buy anything else. (customer bought product1 counts. Customer bought product1 and product2 does not count as product1 is not the only one he bought.)
I guess we can take AdventureWorks database for samples as well. Lets say I want to know the total sales made by customers who buy bikes only and not anything else.
Much appreciated if you can provide me a sample piece of MDX based on AdventureWorks.
Best Regards,
George
November 28, 2008 at 8:10 am
This isn't the only way to get at the result, but here is an example against the Adventure Works. Basically creating a set of the products excluding the item that you are interested in, creating an aggregate member of that set, and then filtering the customers based on that member and the measure you are looking at:
with set NonBikeSales as Except([Product].[Category].children, [Product].[Category].&[1])
member [Product].[Category].[NonBike] as Aggregate(NonBikeSales)
select [Measures].[Internet Sales Amount] on 0,
non empty(
filter([Customer].[Customer].[Customer]
, ([Product].[Category].[NonBike], [Measures].[Internet Sales Amount]) = 0)) on 1
from [Adventure Works]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
November 30, 2008 at 3:53 pm
Hi Dan,
Thank you very much for the explanation.
Regards,
George
December 18, 2008 at 9:24 pm
I wonder is there any way to do it in Excel 2007 dynamically?
I'm sure I dont want to define pre-defined set for each similar request. I wish there is a dropdown or filter to say I dont want to inlcude A, B and C etc.
If Excel 2007 can't do it, is there any other tool to support such request? Proclarity?
The worst senario would be writing pre-defined sets manually.
Merry Christmas Every BI experts!
December 19, 2008 at 5:23 am
There are lots of filter and sorting capabilities within Excel that you can leverage to control what is being displayed. In this scenario you are wanting to implement a custom set that is filter the actual metric value that is displayed to exclude certain customers. In both Excel and ProClarity you can expand on functionality and add items that are not available in the cube, but it is always best to keep the logic centralized in a central store (SSAS database).
You might want to take a look at this add-in on CodePlex for Excel if you have not seen it before:
OLAP PivotTable Extensions is an Excel 2007 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel 2007 API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply