Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
mdx mtd error please help

Answered mdx mtd error please help

  • Saturday, February 02, 2013 7:08 PM
     
      Has Code
    Sign In to Vote
    0

     I am getting the following error when trying to calculate the MTD function

    By default, a month level was expected. No such level was found in the cube.
    FORMATTED_VALUE #Error Query (7, 1) By default, a month level was expected. No such level was found in the cube.

    I am trying to get the total of the month of april for the year 1995. Here is the code.

    MEMBER [Measures]. [hh] as 
mtd([Dim_DateTime].[Calender].[MonthofYear].&[1995]&[4])
		
				Select
						{[Measures].[Value Actual],[Measures]. [hh]} on columns
from cube

    Thanks for your help

    -Sarah

     

All Replies

  • Sunday, February 03, 2013 9:12 AM
     
      Has Code
    Sign In to Vote
    0

    Hi Sarah ,

    If you'll brose the cube and open your Date Dim, would it look like the below ? with month level appers in your hierarchy ..

    Plese try the next code :

    with MEMBER [Measures]. [hh] as 
AGGREGATE( {mtd([Dim_DateTime].[Calender].[MonthofYear].&[1995]&[4])}
	,[Measures].[Actual Value])
,FORMAT_STRING = "###,###"		
				Select
						{[Measures]. [hh]} on columns
from Cube

    Regards, David .

     
  • Monday, February 04, 2013 4:35 AM
     
     Answered
    Sign In to Vote
    0

    Sarah, mtd() will only work if you have a level on your [Dim_DateTime] marked as Month.

    mtd() is really just a shorthand for PeriodsToDate, so you could also do something like

    AGGREGATE(periodstodate([Dim_DateTime].[Calender].[MonthofYear], [Dim_DateTime].[Calender].[MonthofYear].&[1995]&[4])
    ,[Measures].[Actual Value])

    http://RichardLees.blogspot.com 

    Richard