CODEWITHSUNDEEP
reporting-servicesmdxolapcube
Jash92
Jash92

Reputation: 1

MDX query for a set with nested conditions depending on parameters

I want to define a MDX-Set with nested conditions. I have two parameters: @parYearMonthBusinessMonth_From and @parYearMonthBusinessMonth_To.

My script for the set:

SET [OrdersLocal] AS
Filter(
[Orders].[Date FP Posted].[Date FP Posted].AllMembers,


IIF ( StrToMember(@parYearMonthBusinessMonth_From) = NULL AND StrToMember(@parYearMonthBusinessMonth_To) = NULL, LEFT([Orders].[Date FP Posted].currentmember.Properties( 'Member_Caption' ),7) = IIF( StrToMember(@parYearMonthBusinessMonth_To) = NULL, '', FALSE)

    ,IIF(
        StrToMember(@parYearMonthBusinessMonth_From) = NULL AND StrToMember(@parYearMonthBusinessMonth_To) <> NULL, LEFT([Orders].[Date FP Posted].currentmember.Properties( 'Member_Caption' ),7) >= StrToMember(@parYearMonthBusinessMonth_From).NAME

    ,FALSE) 

)
)

I want to filter my [Orders].[Date FP Posted].[Date FP Posted] depending on the values of the parameters.

Case 1: @parYearMonthBusinessMonth_From = NULL AND @parYearMonthBusinessMonth_To = NULL --> Date FP Posted = empty

Case 2: @parYearMonthBusinessMonth_From <> NULL AND @parYearMonthBusinessMonth_To = NULL --> Date FP Posted >= @parYearMonthBusinessMonth_From.Value OR Date FP Posted = empty

Case 3: @parYearMonthBusinessMonth_From = NULL AND @parYearMonthBusinessMonth_To <> NULL --> Date FP Posted <= @parYearMonthBusinessMonth_To.Value OR Date FP Posted = empty

Case 4: @parYearMonthBusinessMonth_From = NULL AND @parYearMonthBusinessMonth_To <> NULL --> Date FP Posted >= @parYearMonthBusinessMonth_From.Value AND <= @parYearMonthBusinessMonth_To.Value

How can I define the set? I've tried several IIF-Conditions but it doesn't worked.

Upvotes: 0

Views: 23

Answers (0)

Related Questions