Working with filters in MDX

[5621]

Hi friends!!!

I need work with filters in MDX, but I don't know how can use the multiples sentences in the clause WHERE or WITH, for example

SELECT ..... ON COLUMNS
FROM SALES
WHERE or WITH
gender="F" and Store="USA" and Products="Drink" and Age="20 years old" and Promotions>"20%"

Thank's and I hope your answers

Karin.... Sorry my poor English

 

[issahar]

SELECT ..... ON COLUMNS
FROM SALES
where ([gender].[F], [Store].[USA], [Products].[Drink],...) Issahar Gourfinkel
senior software engineer
Israel

http://softwatch.com

 

[5621]

Hi Issahar!!!

Thanks for your answer, but i need know how include logic operators in my consult, since the type of consult that need built use logic operator for compare elements with values, percentage and/or measures.

I know that in the where sentence don't can use logic operators, but in the with sentence i can, but i don't know how incorporate this operators for give sense to the consult.

Answer me soon.

Bye.
Karin.

 

[issahar]

the where clause that I have sent you is logic AND of all the mentioned values...
About OR and NOT - It is not so easy, I think, if you will send me some example of the query that you want to ask, I can propose some form of answer. So, send me and I will try to help...
Issahar Gourfinkel
senior software engineer
Israel

http://softwatch.com

 

[5621]

Hi Issahar!!!!

This is an example of the type of consult that i need, but in this consult i don't use the clause AND. I need to introduce logic operators, in this example "xxx > 20%"


SELECT
{[Gender].[All Gender].Children} On Columns,
{CrossJoin(CrossJoin([Marital Status].[All Marital Status].Children,[Product].[All Products].Children),[Store].[All Stores].Children)}
On Rows
FROM
[Sales]
WHERE
([Measures].[Unit Sales], [Promotions].[All Promotions].[Bag Stuffers] > 20%)



Thanks and i wait your answer.
Bye
Karin

 

[issahar]

Hi, Karin
Try this I didn't run it but I have similar query...



WITH Member [Promotions].[myPromotions] AS
'Aggregate( [Promotions].[All Promotions].[Bag Stuffers].[0]:[Promotions].[All Promotions].[Bag Stuffers].[20]} )'



SELECT
{[Gender].[All Gender].Children} On Columns,
{CrossJoin(CrossJoin([Marital Status].[All Marital Status].Children,[Product].[All Products].Children),[Store].[All Stores].Children)}
On Rows
FROM
[Sales]
WHERE
([Measures].[Unit Sales], [Promotions].[myPromotions]
)

...
please, mark this message as expert one if it helped you.
Thanks

Issahar Gourfinkel
senior software engineer
Israel

http://softwatch.com

 

[5621]

Hi Issahar!!!!!

How are you????

I proved the code that you sent to me, but it did not work to me. I continued looking for and exists a Filter function that in theory would allow to make what I need, but this only works if it is in rows or columns, as that form to me does not serve to me, since I need to use it in the Where sentence. I proved this filter directly creating a calculated member and the Where clause, but in both cases it does not work. I enclose the three mentioned codes to you.

************************************************************
SELECT {(Filter({[Product].[All Products].[Drink].Children}, ([Measures].[Sales Count]) > 1000))} On Columns
FROM [Sales]

************************************************************

With Member [Measures].[Filtro1] As
'Filter({[Product].[All Products].[Drink].Children}, ([Measures].[Sales Count])> 1000)'

SELECT
{[Gender].[All Gender].Children} On Columns,
{CrossJoin(CrossJoin([Marital Status].[All Marital Status].Children,[Product].[All Products].Children),[Store].[All Stores].Children)}
On Rows
FROM
[Sales]
WHERE [Measures].[Filtro1]

************************************************************

SELECT
{[Gender].[All Gender].Children} On Columns,
{CrossJoin(CrossJoin([Marital Status].[All Marital Status].Children,[Product].[All Products].Children),[Store].[All Stores].Children)}
On Rows
FROM
[Sales]
WHERE ({Filter({[Product].[All Products].[Drink].Children}, ([Measures].[Sales Count]) > 1000)})


I need to know if you have some example of the use of filters, since as I explained previously only it to you I can use as it leaves from the consultation select (to show it in rows or columns).


Thanks to respond to my consultations.
Greetings.
Karin

 

[issahar]

Hi, Karin.
First thing - I acted according the first formula that you have sent me. Now I fixed it a little (I didn't know that I could be tested versa FoodMart)

Lets suppose that the next formula will give use filter for
Promotions="Bag Stuffers" or Promotions="Best Savings"

WITH Member [Promotions].[myPromotions] AS
'Aggregate({[Promotions].[Bag Stuffers],[Promotions].[Best Savings]} )'

SELECT
{[Gender].[All Gender].Children} On Columns,
{CrossJoin(CrossJoin([Marital Status].[All Marital Status].Children,[Product].[All Products].Children),[Store].[All Stores].Children)}
On Rows
FROM
[Sales]
WHERE
([Measures].[Unit Sales], [Promotions].[myPromotions])


**********
You first formula should be like:

SELECT {(Filter({[Product].[All Products].[Drink].Children}, ([Measures].[Sales Count]) > 1000))} On Columns,
{([Measures].[Sales Count])} on rows
FROM [Sales]
**********
I hope it will help you.
Have a nice Day.
(By the way, if you have troubles to understand my terrible English, I can write you in Spanish or Italian (Or Hebrew if you want )

Issahar Gourfinkel
senior software engineer
Israel

http://softwatch.com