How to filter an MDX query with > or <>

Question

I have the following MDX query:

 
SELECT NON EMPTY {[Measures].[Amount], [Measures].[Gross Margin], [Measures].[Coil Weight] } ON COLUMNS, NON EMPTY { ([Customer].[Name].[Name].ALLMEMBERS * [Customer].[Account Code].[Account Code].ALLMEMBERS * [Date Shipped].[Month].[Month].ALLMEMBERS * [Date Shipped].[Year].[Year].ALLMEMBERS)}DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM 
( 
SELECT ( STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED) : STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED) ) ON COLUMNS FROM
( 
SELECT (STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM
( 
SELECT ( STRTOSET(@SalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM 
( 
SELECT ( STRTOSET(@ShipFromLocation, CONSTRAINED) ) ON COLUMNS FROM 
[Heidtman DW]))))
WHERE
( 
IIF
 
( STRTOSET(@SalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@SalesTypeDescription, CONSTRAINED), [Sales Type].[Description].currentmember), 
IIF
 
( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ), 
IIF
 
( STRTOSET(@ShipFromLocation, CONSTRAINED).Count = 1, STRTOSET(@ShipFromLocation, CONSTRAINED), [Location].[Company].currentmember ) ,
[Coil Status].[SMSTS Code].&[Hist-Ship])
 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 The cube has some irrlevent data that returns with a Month of 0.  I can't remove that data from the cube right now.

How would I filter the query to return Month>0 or Month <>0?

I've used the EXCEPT before, as in:

Except

 

([Date SHipped].[Month].[All].Children,[Date Shipped].[Month].[0])

Is that the best way to go in this case?
Thanks.

Answer 1

Except should work, if you wish to exclude [Date Shipped].[Month].[0] from the set of months (assuming the parameters are on the same Month heirarchy):

...
SELECT ( Except({STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED)
: STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED)},
{[Date Shipped].[Month].[0]} ) ON COLUMNS
...

---

- Deepak

Answer 2

Not so much.  I still get the 0 month data using:

(

SELECT ( Except({STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED)

:

STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED)},

{[Date Shipped].[Month].[0]} ))

ON

 

COLUMNS FROM

Answer 3

Then you can start at the basics - what month rows do you get for this simple query, and what are the parameter values?

select
{[Measures].[Amount], [Measures].[Gross Margin], [Measures].[Coil Weight] } ON COLUMNS
Except({STRTOMEMBER(@FromDateShippedMonth, CONSTRAINED)
: STRTOMEMBER(@ToDateShippedMonth, CONSTRAINED)},
{[Date Shipped].[Month].[0]} ) on columns
FROM [Heidtman DW]

---

- Deepak

Answer 4

My cube contains spurious data which is noted by sales data not having a sales date.  At this point the users do not wish to purge the cube of bad data.  Most of my query's ruturn data by date.  I am getting data with a month of (0) and it is triggering error messages in my reports.  I was just looking for an easy way to filter data that had a month of 0.

Most of my querys include filtering as in:

 

SELECT NON EMPTY { [Measures].[Gross Margin], [Measures].[Gross Profit], [Measures].[Coil Weight], [Measures].[Steel Margin], [Measures].[Amount] } ON COLUMNS, NON EMPTY { ([Inside Rep].[Rep].[Rep].ALLMEMBERS * [Sales Release].[SOORR Num].[SOORR Num].ALLMEMBERS * [Sales Release].[SOORH Number].[SOORH Number].ALLMEMBERS * [Date Shipped].[Year].[Year].ALLMEMBERS * [Date Shipped].[Month].[Month].ALLMEMBERS, lastperiods(@RollBackNum,STRTOMEMBER(@DateShippedYearMonth,CONSTRAINED)) ) }



The user probably can't go back far enough to get to day 0, which, in my date dim, is 01/01/1900.

Other of my queries use the FromMonth:ToMonth filter and when I use "all" I get the month 0 data.  It's not an unsurmountable problem.  I was looking for a quick fix.

Perhaps rather than spending a lot of time changing my queries I should just remove the spurious data.

Answer 5

Hi John,

 

How about using Scope function, I mean hide the values belong to month0:

Scope([Measures].Members);

Freeze;

Descendants([Date Shipped].[Month].[0])=NULL;

End Scope;

 

And then drop the null value from the query.

 

Hope this helps.

Raymond

Answer 6

Raymond,

Where do I insert the Scope in my query?  I've never used it before.

Thanks.

I added it to one of my queries:

Scope

 

([Measures].Members);
Freeze;
Decendents([Date Shipped].[Month].[0])=NULL;
End Scope;

SELECT

 

NON EMPTY { [Measures].[Steel Margin], [Measures].[Coil Weight], [Measures].[Amount], [Measures].[Gross Margin], [Measures].[Gross Profit] } ON COLUMNS, NON EMPTY { ([Date Shipped].[Month].[Month].ALLMEMBERS * [Date Shipped].[Year].[Year].ALLMEMBERS * [Customer].[Account Code].[Account Code].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS, lastperiods(@RollBackNum,STRTOMEMBER(@DateShippedYearMonth,CONSTRAINED)) ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT (STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@SalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW]))
WHERE
(
IIF( STRTOSET(@SalesTypeDescription, CONSTRAINED).Count = 1, STRTOSET(@SalesTypeDescription, CONSTRAINED), [Sales Type].[Description].currentmember ),
IIF( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ) ,
[Coil Status].[SMSTS Code].&[Hist-Ship]
) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


And I get an error: Failed to parse the query to detect if it is MDS or DMX.

Answer 7

You could add a MDX script assignment with scope function for the cube. Click the Calculations tab, and then click the Script View button on the toolbar. More information, see:

http://msdn.microsoft.com/en-us/library/ms145515.aspx

 

Hope this helps.

Raymond

 

Answer 8

I don't want to drop them for the entire cube, just for individual queries. 
Thanks.