InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 22:56:31 GMT20http://www.sqlservercentral.com/Forums/Topic1351259-17-1.aspxIn 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.RobTue, 09 Oct 2012 05:53:21 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1351259-17-1.aspx@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.Wed, 29 Aug 2012 13:19:59 GMTwhenriksenhttp://www.sqlservercentral.com/Forums/Topic1351259-17-1.aspxWhat are these things that are opened or closed? Sales orders? Estimates? Claims? ([i]I guess it really doesn't matter, but it helps me wrap my head around the probem if it's more concrete[/i])In what dimension is your Status stored? Wouldn't it just be a matter of putting that in your slicer? (WHERE clause)Something like:[code="plain"]WHERE ( [WhateverItIs].[Status].@[Closed] )[/code]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,RobWed, 29 Aug 2012 13:12:51 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1351259-17-1.aspxI'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.[code="sql"] 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 )[/code]Tue, 28 Aug 2012 14:33:33 GMTwhenriksen