April 13, 2010 at 4:41 am
Hi, I am working with MDX, and I have the following MDX-statement:
WITH Member [Measures].[SummarizedStock] as
aggregate(PeriodsTODAte([Time dimension].[(All)]),[Measures].[Stock])
Select{[Measures].[SummarizedStock], [Measures].[Stock] } on columns,
nonempty([Time Dimension].[Month].members) on rows
from [Itemtransaction CUBE]
It gives the following result:
SummarizedStockStock
All 18,0018,00
May 200930,0030,00
June 2009 0,000,00
July 2009 -2,00 -2,00
August 2009-17,00-17,00
September 2009 0,000,00
October 2009 -2,00-2,00
December 2009 9,009,00
HELP!! I want it to summarize, but I can't get it to work 🙁
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
April 22, 2010 at 5:06 am
Hello Søren
It could be that the mdx you composed is not correct. I've had some difficulties with the aggregate function as well, but if you want to sum your stock value over all periods, you could switch to using sum instead of aggregate. I just tried something like this and it works fine:
WITH Member [Measures].[SummarizedStock] as
SUM(
PERIODSTODATE(
[Time Dimension].[Month].currentmember.LEVEL,
[Time Dimension].[Month].currentmember )
, [Measures].[Stock])
Select {[Measures].[SummarizedStock] } on columns,
nonempty([Time Dimension].[Month].[Month].members) on rows
from [Itemtransaction CUBE]
Of course I don't know the exact definition of your time dimensions, but something like this should work.
Kind regards,
Cees
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply