Post reply

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

  • 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

  • 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

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

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