rahul.sh1
Member
Hi all,
I have a Sales cube with Sale-price measure and product dimensions. For a particular point of time, i want to see the total sales with respect to distinct products.
For example,
Prd1 sales(200$)
Prd2 sales(240$)
Prd2 sales(240$)
Prd3 sales(230$)
I want the result set to be like this.
Total sales = sales(200$) + sales(240$) + sales(230$)
If anyone have the ready-made MDX query for this, that will be grateful.
Thanks
Rahul
siva tataiah maganti
Member
WITH MEMBER MEASURES.AXISDEMO AS
SUM(AXIS(1), [Measures].[Order Quantity])
SELECT {[Measures].[Order Quantity],MEASURES.AXISDEMO} ON 0,
{[Product].[Category].Children} ON 1
FROM [Adventure Works] WHERE PeriodsToDate(
[Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].[August 2006])
The above query works like
1.First it will display product categorys for jan2006-august2006
2.here making sum of product categorys ,i'm using axis() and sum functions.
run the above query the output is :
Order Quantity AXISDEMO
Accessories 2,579 31,328
Bikes 14,373 31,328
Clothing 7,809 31,328
Components 6,567 31,328
i hope it will ulseful for u.
siva tataiah maganti
Member
if u want to distinct product categorys ,apply distinct keyword
WITH MEMBER MEASURES.AXISDEMO AS
SUM(AXIS(1), [Measures].[Order Quantity])
SELECT {[Measures].[Order Quantity],MEASURES.AXISDEMO} ON 0,
distinct{[Product].[Category].Children} ON 1
FROM [Adventure Works] WHERE PeriodsToDate(
[Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].[August 2006])
You must log in to post.
