CODEWITHSUNDEEP
reporting-servicesssasmdx
Anthony Robinson
Anthony Robinson

Reputation: 1

Troubling MDX Query

I have an MDX query I have been struggling with for a while and just can't seem to get it to fly right!! THe pain point is the subquery that is trying to pull in only those jobs that have a status of anythign BUT "Closed" (DIM PROJECT].[Job Status].&[Closed]}. I know/think I'm maybe missing a parentheses somewhere possibly???

MAybe a second set of eyes would help???

SELECT 
  NON EMPTY 
    {
      [Measures].[Estimate Extended Amount]
     ,[Measures].[Estimate Line Total]
     ,[Measures].[Estimate Markup Amount]
    } ON COLUMNS
 ,NON EMPTY 
    {
        [DIM PROJECT].[Client - Division - Product - Component].[Job Component Number].ALLMEMBERS*
        [DIM PROJECT].[Component Description].[Component Description].ALLMEMBERS*
        [DIM PROJECT].[Client PO Number].[Client PO Number].ALLMEMBERS*
        [DIM PROJECT].[Job Status].[Job Status].ALLMEMBERS*
        [DIM PROJECT].[Project Start Date].[Project Start Date].ALLMEMBERS*
        [DIM PROJECT].[Project End Date].[Project End Date].ALLMEMBERS*
        [DIM FUNCTION].[Function Category].[Function Category].ALLMEMBERS*
        [DIM ESTIMATE].[Estimate Number].[Estimate Number].ALLMEMBERS*
        [DIM ESTIMATE].[Estimate Status].[Estimate Status].ALLMEMBERS
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM 
(
  SELECT 
    {[DIM ESTIMATE].[Estimate Status].&[Approved]} ON COLUMNS
  FROM (-{[DIM PROJECT].[Job Status].&[Closed]} ON COLUMNS
  (
    SELECT 
      StrToSet
      (@DIMPROJECTProduct
       ,CONSTRAINED
      ) ON COLUMNS
    FROM 
    (
      SELECT 
        StrToSet
        (@DIMPROJECTDivision
         ,CONSTRAINED
        ) ON COLUMNS
      FROM 
      (
        SELECT 
          StrToSet
          (@DIMPROJECTClient
           ,CONSTRAINED
          ) ON COLUMNS
        FROM [ESTIMATES]
      )
    )
  )
)
WHERE 
  (
    IIF
    (
        StrToSet
        (@DIMPROJECTClient
         ,CONSTRAINED
        ).Count
      = 1
     ,StrToSet
      (@DIMPROJECTClient
       ,CONSTRAINED
      )
     ,[DIM PROJECT].[Client].CurrentMember
    )
   ,IIF
    (
        StrToSet
        (@DIMPROJECTDivision
         ,CONSTRAINED
        ).Count
      = 1
     ,StrToSet
      (@DIMPROJECTDivision
       ,CONSTRAINED
      )
     ,[DIM PROJECT].[Division].CurrentMember
    )
   ,IIF
    (
        StrToSet
        (@DIMPROJECTProduct
         ,CONSTRAINED
        ).Count
      = 1
     ,StrToSet
      (@DIMPROJECTProduct
       ,CONSTRAINED
      )
     ,[DIM PROJECT].[Product].CurrentMember
    )
  )
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

Upvotes: 0

Views: 301

Answers (1)

Peter
Peter

Reputation: 634

I am not quite sure what you are trying to do, but it seems like you might be over complicating your query a little bit. I think the function you are looking for is EXCEPT:

Except([DIM PROJECT].[Job Status].[All].Children,[DIM PROJECT].[Job Status].&[Closed])

This will give you all job statuses except closed. If you put it in your where clause (ymmv with my simplification) it might read:

SELECT 
NON EMPTY 
{
  [Measures].[Estimate Extended Amount]
 ,[Measures].[Estimate Line Total]
 ,[Measures].[Estimate Markup Amount]
} ON COLUMNS
,NON EMPTY 
{
    [DIM PROJECT].[Client - Division - Product - Component].[Job Component Number].ALLMEMBERS*
    [DIM PROJECT].[Component Description].[Component Description].ALLMEMBERS*
    [DIM PROJECT].[Client PO Number].[Client PO Number].ALLMEMBERS*
    [DIM PROJECT].[Job Status].[Job Status].ALLMEMBERS*
    [DIM PROJECT].[Project Start Date].[Project Start Date].ALLMEMBERS*
    [DIM PROJECT].[Project End Date].[Project End Date].ALLMEMBERS*
    [DIM FUNCTION].[Function Category].[Function Category].ALLMEMBERS*
    [DIM ESTIMATE].[Estimate Number].[Estimate Number].ALLMEMBERS*
    [DIM ESTIMATE].[Estimate Status].&[Approved]
}
DIMENSION PROPERTIES 
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM [ESTIMATES]
WHERE 
( StrToSet(@DIMPROJECTProduct),
  StrToSet(@DIMPROJECTDivision),
  StrToSet(@DIMPROJECTClient), 
  Except([DIM PROJECT].[Job Status].[All].Children,[DIM PROJECT].[Job Status].&[Closed]))

If your parameters happen to be single members you can probably just use StrToMember in the where clause.

Upvotes: 2

Related Questions