take SSAS MDX with parameters used in SSRS report and execute it in MDX in management studio

• andre.quitta

  SSCommitted

  Points: 1889

  More actions

  August 24, 2018 at 2:26 pm

  #376768

  I'd like to work my way through some numbers I'm getting in an SSRS report that doesn't line up with the data behind it.

  My report dataset

  
WITH MEMBER [Measures].[X]
  WHEN ______________ THEN
  ELSE _____________
  END
SELECT { [Measures].[X], [Measures].[Y] } ON 0
  ,NONEMPTY(
   ( EXISTS( [Organization].[Org Hierarchy].[Level 6].ALLMEMBERS , STRTOSET(@OrganizationOrgHierarchy, CONSTRAINED) ) * TAIL( LASTPERIODS(@PreviousPeriods + 1, STRTOMEMBER(@DateFiscalWeek, CONSTRAINED) ), @PreviousPeriods) )
   ,[Measures].[FTE Sum]
  ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON 1
 FROM [Model]
 WHERE (
    IIF( STRTOSET(@AccountSubaccountFieldHierarchy, CONSTRAINED).Count = 1, STRTOSET(@AccountSubaccountFieldHierarchy, CONSTRAINED), [Account-Subaccount].[Field Hierarchy].currentmember )


  My question is this:
  How do I insert the value below into @AccountSubaccountFieldHierarchy?
  [Account-Subaccount].[Field Hierarchy].[Level 1].&[Operating Income].&[Center Contribution].&[Gross Margin].&[Direct Operating Income].&[Net Revenue].&[Gross Revenue].&[Total Tuition],
  or insert values to any of the other @Parameters ?

  Thanks much
  A

• PB_BI

  SSCoach

  Points: 17461

  More actions

  August 28, 2018 at 5:59 pm

  #2003748

  andre.quitta - Friday, August 24, 2018 2:26 PM

  I'd like to work my way through some numbers I'm getting in an SSRS report that doesn't line up with the data behind it.

  My report dataset

  
WITH MEMBER [Measures].[X]
  WHEN ______________ THEN
  ELSE _____________
  END
SELECT { [Measures].[X], [Measures].[Y] } ON 0
  ,NONEMPTY(
   ( EXISTS( [Organization].[Org Hierarchy].[Level 6].ALLMEMBERS , STRTOSET(@OrganizationOrgHierarchy, CONSTRAINED) ) * TAIL( LASTPERIODS(@PreviousPeriods + 1, STRTOMEMBER(@DateFiscalWeek, CONSTRAINED) ), @PreviousPeriods) )
   ,[Measures].[FTE Sum]
  ) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON 1
 FROM [Model]
 WHERE (
    IIF( STRTOSET(@AccountSubaccountFieldHierarchy, CONSTRAINED).Count = 1, STRTOSET(@AccountSubaccountFieldHierarchy, CONSTRAINED), [Account-Subaccount].[Field Hierarchy].currentmember )


  My question is this:
  How do I insert the value below into @AccountSubaccountFieldHierarchy?
  [Account-Subaccount].[Field Hierarchy].[Level 1].&[Operating Income].&[Center Contribution].&[Gross Margin].&[Direct Operating Income].&[Net Revenue].&[Gross Revenue].&[Total Tuition],
  or insert values to any of the other @Parameters ?

  Thanks much
  A

  Using STRTOSET you pass it in as a literal string. There are examples here: https://docs.microsoft.com/en-us/sql/mdx/strtoset-mdx?view=sql-server-2017
  If you keep the CONSTRAINED keyword then you have to pass fully qualified members with braces.
  More here: https://www.iccube.com/support/documentation/mdx/StrToSet.php

  ---

  I'm on LinkedIn