I'm converting a report from a TSQL dataset to use a SSAS dataset.
The report has a status parameter to choose Open or Closed items.
If open is chosen, the filter will need to use the Opened field. When closed is selected, I need to filter the result set by Closed, or Resolved if the item is not closed. Also, the default Closed value is '19000101', nulls are not allowed.
I'm hoping I can simplify the closed filter by merging the Resolved/Closed dates, using Resolved only if the Closed date is '19000101'.
I need to create the MDX equivalent, but am new to MDX and not sure how to do it. I assume I'll need to use IIF in a calculated member to check a value and choose a dimension member based on the result.
Here is the equivalent filter in SQL. Any help would be appreciated.
WHERE ( @Status = 'O'
AND CTE.Opened BETWEEN @StatusFromDate
AND cte.Resolved = '19000101' --Not Resolved
OR @Status = 'C'
AND ( ( CTE.Resolved BETWEEN @StatusFromDate
AND CTE.Closed = '19000101' --Not Closed
OR Cte.Closed BETWEEN @StatusFromDate
(A solid design is always preferable to a creative workaround)