locked
MDX To Return Current Month RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi I Have posted before regarding this but ended up using STPS but i have now realised that i need to be able to do this IN SQL as a KPI.

    Need to Create a KPI in SQL SSAS the KPI is for Total Quotes for the Current Month, I can create the query but i would have to change it every month which i dont want to do so i need it to be Dynamic.

    I have a time dimension which is as follows

    [Time].[Year - Month - Date]

    I have tried the following

    ([Measures].[Total Quotes], StrToMember("[Time].[Year - Month - Date].[Month].&["+CStr(Format(Now(),"yyyy-MM-dd"))+"T00:00:00]"))

    but it just returns a value of null

    So all i need it to be able to do is total the number of quotes that have been generated for the current month, and to be able to work when the months Change.

    John

    Wednesday, February 2, 2011 3:37 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    You may want to investigate this article.

    http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx

     

    Wednesday, February 2, 2011 4:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A little bored to answer this question as this would be some kinda of boring mistake

    Try all these....

    1. SELECT [Measures].[Total Quotes] on 0,[Time].[Year - Month - Date].[Month].&[2011-02-01T00:00:00]  on 1 from [CubeName]

    1. Drop the member directly into query analyzer and write the query as simple as with StrToMember

    With member CurrentValue as

    ([Measures].[Total Quotes], [Time].[Year - Month - Date].[Month].&[2011-02-01T00:00:00])

    SELECT CurrentValue on 0 from [CubeName]

    2. Then check

    With member CurrentValue as

    ([Measures].[Total Quotes], STRTOMEMBER("[Time].[Year - Month - Date].[Month].&[2011-02-01T00:00:00]"))
    SELECT CurrentValue on 0 from [CubeName]

    3. Check whether you have value in first place for the date.

    4. Check carefully whether the date is formed exactly as how it is defined. Best it to check first by dropping the member and check against that.

    Once all this done, fit in your Format() stuff...If this does not work then you may not have data for period selected

    vinu
    Wednesday, February 2, 2011 5:34 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You may want to investigate this article.

    http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx

     

    Wednesday, February 2, 2011 4:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for the quick response but i have read this article before and i am still struggling, i am new to MDX

    It seems such a simple thing to be able to do

    Wednesday, February 2, 2011 5:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A little bored to answer this question as this would be some kinda of boring mistake

    Try all these....

    1. SELECT [Measures].[Total Quotes] on 0,[Time].[Year - Month - Date].[Month].&[2011-02-01T00:00:00]  on 1 from [CubeName]

    1. Drop the member directly into query analyzer and write the query as simple as with StrToMember

    With member CurrentValue as

    ([Measures].[Total Quotes], [Time].[Year - Month - Date].[Month].&[2011-02-01T00:00:00])

    SELECT CurrentValue on 0 from [CubeName]

    2. Then check

    With member CurrentValue as

    ([Measures].[Total Quotes], STRTOMEMBER("[Time].[Year - Month - Date].[Month].&[2011-02-01T00:00:00]"))
    SELECT CurrentValue on 0 from [CubeName]

    3. Check whether you have value in first place for the date.

    4. Check carefully whether the date is formed exactly as how it is defined. Best it to check first by dropping the member and check against that.

    Once all this done, fit in your Format() stuff...If this does not work then you may not have data for period selected

    vinu
    Wednesday, February 2, 2011 5:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for the response i will give it a go.

    not to sure why you be bored to answer the question.

     

    John

    Wednesday, February 2, 2011 6:24 PM