Incorrect data when attribute hierarchy is queried (Numbers are dislayed with sign reversal)

hello all,

i'm encountering strange issue while querying attribute hierarchy. following details of issue:

the dimensions , cube details :

dimensions : time (level based)

                     scenario (regular)

                     account (parent child)

cube : 1 cube without aggregations.

 

account dimension has 1 user defined hierarchy : account group -> accountgroup level -> account leaf level.

we have enabled unary operator , custom members on account dimension.

 

mdx query negative numbers:

select
  non empty
      {
        hierarchize
        (
          {
            {
              descendants
              (
                {[accountgroup].[accountgroup_level].&[5991].&[140]}, -- user hierarchy account dimension
               ,leaves
              )
            }
          }
        )
      }
 on rows
 ,non empty
    {
      hierarchize
      (
        {
          {
            descendants
            (
              {[time].&[2010]}
             ,[time].[month]
             ,self
            )
          }
        }
      )
    } on columns
<cube name>
where
  (
    (
      [scenario].&[1]
    )
   ,[account].&[1] -- account dimension root member
  );

 

mdx query positive numbers:

select
  non empty
      {
        hierarchize
        (
          {
            {
              descendants
              (
                {[accountgroup].[accountgroup_level].&[5991].&[140]}, -- user hierarchy account dimension
               ,leaves
              )
            }
          }
        )
      }
 on rows
 ,non empty
    {
      hierarchize
      (
        {
          {
            descendants
            (
              {[time].&[2010]}
             ,[time].[month]
             ,self
            )
          }
        }
      )
    } on columns
<cube name>
where
  (
    (
      [scenario].&[1]
    )
   ,[account].&[140] -- account dimension member referred in user hierarchy
  );

in both cases query results same in terms of numbers difference sign reversal. the positive numbers correct result.

i'm not able figure out why getting negative numbers first mdx query. can please me in this?

 

this critical issue organization stand point, quick appreciated.

 

thanks,

ranjith

 

 

 

 

 

 

well, unary operators define how values aggreagated

if unary operator '-' then aggregated [value]*-1
here little example how unary operators work and which demonstrates how signs can switched them

all		150
a	+	-50
a1	+	50
a2	-	100
b	-	-200

a = a1 + (a2 *-1) --> = 50 + (100 * -1) = -50
= + (b * -1) --> = -50 + (-200 * -1) = 150

hth,
gerhard

---

- www.pmone.com -

SQL Server  >  SQL Server Analysis Services