Post reply

Returning a count for a specific Date

  • December 20, 2011 at 7:03 am

    #149774

    Hello All,

    I am trying the following code:

    SELECT

    [Measures].[Revision Count] ON 0,

    {STRTOMEMBER('[Date].[Date].[' + FORMAT(cdate("12/01/2011"),"MMMM d, yyyy") + ']'):

    STRTOMEMBER('[Date].[Date].[' + FORMAT(cdate("12/01/2001"),"MMMM d, yyyy") + ']')} ON 1

    FROM [Team System]

    WHERE [Work Item].[Microsoft_VSTS_Common_Priority_String].&[2 - Medium] >=1

    And I get the following error:

    The WHERE clause function expects a tuple set expression for the argument. A string or numeric expression was used. (Microsoft SQL Server 2008 Analysis Services)

    I know the WHERE clause is wrong.

    Can someone clue me in?

    Thanks,

    Kurt:-D

  • December 21, 2011 at 12:56 pm

    #1424419

    What are you trying to accomplish with the where-clause? It looks like you are trying to filter the cube for only data where the value in [Work Item].[Microsoft_VSTS_Common_Priority_String].[2 - Medium] is > 2. Is [Microsoft_VSTS....] a user-hierarchy?

    Assuming all the values for that member are numeric, you could use something like the following...

    SELECT

    {[Measures].[Internet Sales Amount]} ON 0

    ,{STRTOMEMBER('[Date].[Date].&[' + FORMAT(CDate("09/25/2002"),"yyyyMMdd") + ']')} ON 1

    FROM

    [Adventure Works]

    WHERE

    FILTER(

    [Customer].[Total Children].[Total Children].Members

    ,CInt([Customer].[Total Children].CurrentMember.Properties("Caption")) >= 1

    )

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply