August 28, 2012 at 2:33 pm
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 @StatusToDate
AND cte.Resolved = '19000101' --Not Resolved
)
OR @status = 'C'
AND ( ( CTE.Resolved BETWEEN @StatusFromDate
AND @StatusToDate
AND CTE.Closed = '19000101' --Not Closed
)
OR Cte.Closed BETWEEN @StatusFromDate
AND @StatusToDate
)
Wes
(A solid design is always preferable to a creative workaround)
August 29, 2012 at 1:12 pm
What are these things that are opened or closed? Sales orders? Estimates? Claims? (I guess it really doesn't matter, but it helps me wrap my head around the probem if it's more concrete)
In what dimension is your Status stored? Wouldn't it just be a matter of putting that in your slicer? (WHERE clause)
Something like:
WHERE
(
[WhateverItIs].[Status].@[Closed]
)
Of course this depends on how your dimension and hirearchies are setup. I hope this isn't so general as to not be of help.
HTH,
Rob
October 9, 2012 at 5:53 am
In your cube, how do you know if something is open or closed? You must have a dimension that describes the status. Where is that? That's what you'd put in your slicer.
Rob
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply