Post reply

Using calculated measure as parameter

  • February 24, 2015 at 1:11 pm

    #314961

    I am new to MDX and having an issue with a parameter.

    I created a calculated measure to add 2 additional regions based on the value of "sub channel". The calculated measure works correctly and I can use the results to group my report.

    The issue is when I want to use this field as a parameter so I can run the report for one, or multiple "calculated regions". For example I select "FL" as the calculated region. The query returns all of FL region - which includes sales for the calculated region of CURP and MBC

    Here is the query SSRS created that is causing the issue

    WITH MEMBER [Measures].[Calculated Variance] AS [Measures].[Annual Premium]-[Measures].[Credit Union Goals]

    MEMBER [Measures].[Calculated Region]

    AS IIF( [Sales Channel].[Sales Sub Channel].membervalue="MBC",

    "MBC",IIF( [Sales Channel].[Sales Sub Channel].membervalue="CURP",

    "CURP",[Credit Union].[Credit Union Region].membervalue ))

    SELECT NON EMPTY { [Measures].[Annual Premium], [Measures].[Credit Union Goals],

    [Measures].[Calculated Variance], [Measures].[Calculated Region] }

    ON COLUMNS, NON EMPTY { ([Credit Union].[Credit Union Region].[Credit Union Region].ALLMEMBERS *

    [Credit Union].[Credit Union Name].[Credit Union Name].ALLMEMBERS *

    [Product].[Product Name].[Product Name].ALLMEMBERS *

    [Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *

    [Sales Channel].[Sales Sub Channel].[Sales Sub Channel].ALLMEMBERS ) }

    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

    ( SELECT ( STRTOSET(@MeasuresCalculatedRegion, CONSTRAINED) ) ON COLUMNS FROM

    ( SELECT ( STRTOSET(@SalesChannelSalesChannel, CONSTRAINED) ) ON COLUMNS FROM

    ( SELECT ( STRTOMEMBER(@FromSalesDateFullDate, CONSTRAINED)

    : STRTOMEMBER(@ToSalesDateFullDate, CONSTRAINED) ) ON COLUMNS FROM [Sales])))

    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

    FONT_NAME, FONT_SIZE, FONT_FLAGS

  • February 25, 2015 at 7:52 am

    #1779917

    I decided to approach this with a different method by adding a calculated field in the report and filtering the report on it.

    Still doesn't help my MDX skill:-P, but my report works now.

    Thank for looking.

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

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