Filtering records in MDX / Excel

Question

Hi All,

 Can anyone let me know how to filter the records at the excel level and cube as in the backend.

 Based on some condition (2g.. If my measure value is in between some range) then i need to display only those measures in the report.

 

 

---

Regards, Nagaraj

Answer 1

Hi,

For MDX,you can use FILTER function.

SELECT 
  [Measures].[Internet Sales Amount] ON 0
 ,Filter
  (
    [Date].[Date].[Date].MEMBERS
   ,
      [Measures].[Internet Sales Amount] > 10000
    AND 
      [Measures].[Internet Sales Amount] < 20000
  ) ON 1
FROM [Adventure Works]; 

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

For Excel, click on "Row Labels" -> then "Value Filters" -> use "Between" and mention the measure value.

http://imageshack.us/f/710/forumimg.png/

---

Aniruddha http://aniruddhathengadi.blogspot.com/

Answer 2

Hi,

 

 Value filters in excel are disabled. Not able to set

---

Regards, Nagaraj

Answer 3

Anriudhha,

 

 Any idea to enable the value filters

---

Regards, Nagaraj

Answer 4

Hi Nagaraj

Typically you would create either a dynamic named set or a calculated member with the filter condition. This is all on the server side (SSAS), and excel has really nothing to do with it, other than submitting the query with the appropriate slicers

---

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx

Answer 5

Hi Guillen,

 I have four calculated members and consider for one of the measures if it is empty, i need to exclude those records from the result set.

 Can you let me know the steps to achieve this using named set or filter condition.

---

Regards, Nagaraj

Answer 6

Expanding on what Ani already posted, you can query the cube as:

 

with dynamic set x as

Filter

 (

 [Date].[Date].[Date].MEMBERS

 ,

  [Measures].[Internet Sales Amount] > 10000

 AND 

  [Measures].[Internet Sales Amount] < 20000

 )

 

select {[measures].[internet sales amount]} on 0,

x on 1

from [adventure works]

where [date].[calendar year].[cy 2003]

 

in the cube script you can declare it as

create dynamic set currentcube.[x] as

Filter

 (

 [Date].[Date].[Date].MEMBERS

 ,

  [Measures].[Internet Sales Amount] > 10000

 AND 

  [Measures].[Internet Sales Amount] < 20000

 );

 

---

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx

Answer 7

Javier,

 I have tried based on the above suggestions, but no luck. What exactly the situation is

 We have a report with four columns

  1. Current Year Current Month

  2. Last Year Current Month

  3. Current YTD

   4. Last YTD

All above are calculated columns and are based on the database column ACTUALS. Upon selection of Year and Period filters in the report these fields will get populated.

Now if i run the report for current month current year (6/2011), all the four fields will be populated. At the dimension level, we have four levels of drill down as below

Ledger Group  -->  Ledger account  -- > Document Number  ---> Sales Order No.

For a particular ledger account there will be so many document numbers for two years (2010 and 2011 -- if we run the report for 6/2011 ).

If a particular document number is not having any value for [Current Month Current Year] and if it has value for any other month as per the filter selection, this will be displayed in the report with a value under [Current YTD].

What we are trying to do here is, We need to hide the document number if the value for [Current Month Current Year] if empty or null.

How we can use named set to achieve this or if you can think of any other way, please let me know.

---

Regards, Nagaraj

Answer 8

Javier,

 I tried as per the above suggestion but it is not working. What exactly the situation is

I have four columns in my report

1. Current Year Current Month

2. Last Year Current Month

3. Current YTD

4. Last YTD

All the above columns are calculated columns inside the cube on a database field ACTUALS.

Now if any measures is null under [Current Year Current Month] column i need to hide those records even though it has values for other columns.

 

See below report for your reference and please suggest

 

---

Regards, Nagaraj

Answer 9

Hi Nagaraj

I couldn't see the image you posted, but if my understanding is correct, each of those columns are members of a dimension.  (Perhaps calculated members).

You can then define a dynamic set that will be populated with one or more members based on a filter condition.  Does that make sense?

---

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx

Answer 10

Hi Javier,

 I tried developing MDX named set with your suggestions. I have minimal knowledge in MDX, can you please help me in writing the MDX.

 I posted the screen shot and detailed explaination of my problem at the below mentioned url

 

http://nagaraju-dwh.blogspot.com/2011/06/filtering-records-using-named-set.html

---

Regards, Nagaraj

Answer 11

Javier .. Any suggestions on the last post

---

Regards, Nagaraj

Answer 12

Hi Nagaraj

I went to the url you posted, but I can't see any images

---

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx

Answer 13

Javier,

 Can you please try now again, I am able to see the screenshots.

---

Regards, Nagaraj

Answer 14

Javier,

 If you are unable to see the images, please share your mail id. I will share the screenshots with you.

 

---

Regards, Nagaraj