Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX query help IF/IIF statement Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 2:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:29 AM
Points: 39, Visits: 224
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
)

Post #1351259
Posted Wednesday, August 29, 2012 1:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:50 AM
Points: 1,234, Visits: 1,273
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
Post #1351868
Posted Wednesday, August 29, 2012 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:29 AM
Points: 39, Visits: 224
@Status is a report variable that determines if I need to filter by open date or resolved/closed date. @Status is not contained in the cube.
Post #1351873
Posted Tuesday, October 9, 2012 5:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:50 AM
Points: 1,234, Visits: 1,273
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
Post #1370340
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse