Prologika Forums

I'm trying to write some MDX code for a SSRS report and I need to filter out those items with a zero amount or in another case only show items with a dollar amount over a certain dollar amount.

here is what I thought would work but no luck the zero's still show up.

I figured it out - i added the with memeber iif statement to convert the zeros to nulls and then the non empty function cleaned up the records.

But since i have your attention is there a way to clean up the where statement ?

I've notice that after adding the With member statement., the query performace dropped quite a bit and I get a pop up message stating "Query Preparation in progress"

Should the "With Member" statement been done differently?

How about:

SELECT NON EMPTY {[Measures].[Billed Sales Amount]} ON COLUMNS,

NON EMPTY {Filter([CUSTOMER JOB].[By Cust-Title-Job].[Title Name].ALLMEMBERS, [Measures].[Billed Sales Amount]>0 /*or whatever*/} ON ROWS

FROM [DW PREP ARCHIVE]

WHERE

( (lastperiods(12,tail(filter({[Job Complete Date].[Calendar Full].[Month].members* [Measures].[Billed Sales Amount]} as s, Not IsEmpty(s.current)),1).item(0).item(0))), [PREP CONTROL HDR].[Bill Formats].&[19] )

Your code will work fine if the value is zero, However, I'm also working with a slightly different version of the code. Instead of great than zero i'm looking for greater than 5000.

I want the filter applied at the Title name level of the hierarchy.

So if the sum of all the [Job Issue Names] for the last 12 months is greater than 5000,  I want those customers and titles displayed.

When I run your code, I find it is filtering at the level below Title Name, which is Job Issue Name

So lets say i have

Job Issue Names         Sales

ABC                           1,000

CDF                           2,000

LMN                          6,000

Only LMN will show up in the result set.

One good thing it did run much faster.

I can't test your query but try the following by switching to Query Mode:

SELECT NON EMPTY {[Measures].[Billed Sales Amount]} ON COLUMNS,

NON EMPTY {[CUSTOMER JOB].[By Cust-Title-Job].[Title Name].ALLMEMBERS} 

HAVING Axis(0).Item(0) > 5000 ON ROWS

FROM [DW PREP ARCHIVE]

WHERE

( (lastperiods(12,tail(filter({[Job Complete Date].[Calendar Full].[Month].members* [Measures].[Billed Sales Amount]} as s, Not IsEmpty(s.current)),1).item(0).item(0))), [PREP CONTROL HDR].[Bill Formats].&[19] )

Having filters after NON EMPTY is applied. It can take a tuple too, e.g.

HAVING([Measures].[Billed Sales Amount], [Date].[Year].[2006] > 5000) to flter titles where Billed Sales Amount > 5000 for year 2006.

First off let me thank you for your all your help. I was still having trouble with that last set of code, but I was able to finally create the query using the design mode of report services.

here is how it wrote the code, it handled it through a number of select statements.

SELECT

NON EMPTY { [Measures].[Billed Sales Amount] } ON COLUMNS,

NON EMPTY { ([CUSTOMER JOB].[By Cust-Title-Job].[Title Name].ALLMEMBERS ) } ON ROWS

FROM ( SELECT ( FILTER([CUSTOMER JOB].[By Cust-Title-Job].[Title Name].MEMBERS,[Measures].[Billed Sales Amount] > 5000) ) ON COLUMNS

FROM ( SELECT ( (lastperiods(12,tail(filter({[Job Complete Date].[Calendar Full].[Month].members* [Measures].[Billed Sales Amount]} as s, Not IsEmpty(s.current)),1).item(0).item(0))) ) ON COLUMNS

FROM ( SELECT ( -{ [PREP CONTROL HDR].[Bill Formats].&[19], [PREP CONTROL HDR].[Bill Formats].&[7] }) ON COLUMNS FROM [DW PREP ARCHIVE])))