Results 1 to 5 of 5

Thread: currentMember: difference between AS2000 e AS2008

  1. #1
    Join Date
    Jun 2011
    Posts
    4

    currentMember: difference between AS2000 e AS2008


    Good morning,
    i am migrating from SS2000 / AS2000 to SS2008 / AS2008 and fixing my own olap tool to work correctly within but i am in trouble in time comparison.

    In AS2000 when i would to compare a measures (e.g. Quantity) in a time period (e.g. 1st Jan until today in 2009,2010,2011), i execute an mdx like follow:

    WITH
    MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1]:[orderDate].currentmember.[5]},[measures].[quantity])+sum({[orderDate].currentmember.[6].[1]:[orderDate].currentmember.[6].[21]},[measures].[quantity])'
    SELECT
    {[orderDate].[All orderDate].[2009],[orderDate].[All orderDate].[2010],[orderDate].[All orderDate].[2011]} on columns,
    non empty toggledrillstate({[nation].[All nation]},{[nation].[All nation]}) on rows
    FROM orders
    WHERE ([measures].[time_quantity])


    or simplier, just for monthly comparison

    WITH
    MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1].[1]:[orderDate].currentmember.[1].[31]},[measures].[quantity])'
    SELECT
    {[orderDate].[All orderDate].[2009],[orderDate].[All orderDate].[2010],[orderDate].[All orderDate].[2011]} on columns,
    non empty toggledrillstate({[nation].[All nation]},{[nation].[All nation]}) on rows
    FROM orders
    WHERE ([measures].[time_quantity])


    In AS2008 when i run this query, an error occour with the following description.

    Parser: Sintassi di '[1]' non corretta. (Parser: incorrect sintax of '[1]')

    Where is the issue? Can't i use currentMember in WITH MEMBER?

    Thanks for your support,
    Cristian

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Sql2k8 books online has syntax and samples under 'CurrentMember (MDX)'.

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    Maybe i didn't look in right way, but i had not found example for my scenario.
    I try to look better

  4. #4
    Join Date
    Jun 2011
    Posts
    4
    In order to be clear, i try to explain better the reason of currentMember usage and issue's origin
    Think to this scenario

    1. User require order quantity in 2011 from 1st january to 15 ; i can inquery in many way but i would use this one (in 2. i'll be more clear)

    WITH
    MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1].[1]:[orderDate].currentmember.[1].[15]},[measures].[quantity])'
    SELECT
    {[orderDate].[All orderDate].[2011]} on columns,
    {[nation].[All nation]}) on rows
    FROM orders
    WHERE ([measures].[time_quantity])



    2. User would compare quantity with same period of others year (i.e. 2010); with this query i have just to add requested year on column and currentMember calculate total for right period

    WITH
    MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1].[1]:[orderDate].currentmember.[1].[15]},[measures].[quantity])'
    SELECT
    {[orderDate].[All orderDate].[2010],
    [orderDate].[All orderDate].[2011]} on columns,
    {[nation].[All nation]}) on rows
    FROM orders
    WHERE ([measures].[time_quantity])



    3. In this way i can easily add year to compare (and doing from a visual basic olap client is a good job!) and manage time period just from with member

    WITH
    MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1]:[orderDate].currentmember.[5]},[measures].[quantity])
    + sum({[orderDate].currentmember.[6].[1]:[orderDate].currentmember.[6].[21]},[measures].[quantity])'
    SELECT
    {[orderDate].[All orderDate].[2000],
    [orderDate].[All orderDate].[2001],
    [orderDate].[All orderDate].[2010],
    [orderDate].[All orderDate].[2011]} on columns,
    {[nation].[All nation]}) on rows
    FROM orders
    WHERE ([measures].[time_quantity])

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    If someone should be interested in, i had answer on Microsoft's Social

    http://social.msdn.microsoft.com/For...0-53c30859a27c



    WITH
    MEMBER [measures].[time_quantity] as
    sum(
    {
    --instead of [orderDate].currentmember.[1].[1]
    strToMember("[orderDate].[" + [orderDate].currentMember.member_key + "].[1].[1]")
    :
    --instead of [orderDate].currentmember.[1].[31]
    strToMember("[orderDate].[" + [orderDate].currentMember.member_key + "].[1].[31]")
    }
    ,[measures].[quantity])
    SELECT
    {[orderDate].[All orderDate].[2009],[orderDate].[All orderDate].[2010],[orderDate].[All orderDate].[2011]} on columns,
    non empty toggledrillstate({[nation].[All nation]},{[nation].[All nation]}) on rows
    FROM orders
    WHERE ([measures].[time_quantity])

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •