July 21, 2010 at 5:26 am
Hi, i am newbie on mdx query. so i need some help to make a simple mdx query.
suppose that I have the following mdx code:
WITH MEMBER [Measures].x AS
Count((
[Dim Costumer].[Cod].ALLMEMBERS ,
[Dim Costumer].[Age Group].&[0-14],
)
)
SELECT x ON 0
FROM [StoreDM]
the result would be:
x = 400
but suppose that i want filter by store and date, now i made the following code:
WITH MEMBER [Measures].x AS
Count((
[Dim Costumer].[Cod].ALLMEMBERS ,
[Dim Costumer].[Age Group].&[0-14],
[Dim Store].[Cod].&[51],
[Dim Date].[D].&[2010-07-21]
)
)
SELECT x ON 0
FROM [StoreDM]
the result :
x= 400.
So I don't understand what's going on... why the result is same? why the this lines:
[Dim Store].[Cod].&[51],
[Dim Date].[D].&[2010-07-21]
don't affect the final result.
Can anyone please, help me here?
Thanks in advance,
Cafc
July 21, 2010 at 10:02 am
You could use the FILTER funtion to remove those that are <= 0. Here is an example that works in the Adventure Works sample cube:
SELECT
[Measures].[Internet Sales Amount] on columns,
FILTER([Promotion].[Promotion].children, [Measures].[Internet Sales Amount]>15000) on rows
FROM [Adventure Works]
The result only contains Promotions with sales greater then 15000.
Here is some pseudo code that should assist you in writing your query:
SELECT
[MEASURES].[Sales] on columns,
FILTER
(
[DimCustomer].[Age].Children,[MEASURES].[Sales]>0
) on rows
FROM [Your Cube]
Hope this helps!
July 21, 2010 at 10:14 am
Thanks for your answer but your mdx code will return sum of sales... and i want count the number of costumers...
A change my initial post because it wasn't well formulated to solve my problem! so i put my code... and results. if you can help i will appreciate it.
Thanks
Cafc
July 21, 2010 at 11:03 am
The Count() function simply counts the number of members in a set. If the addition of the new members to the tuple that defines your set doesn't narrow down your set, then the count will be unchanged. This depends as much on the data, measure groups, and attribute relationships in your cube as it does on the query.
The best approach here would be to set up a simple Count measure, and then use the members as part of the Where clause. This would be more effiecient, and result in a simpler query:
SELECT
[DMCount] ON 0
FROM [StoreDM]
WHERE
(
[Dim Costumer].[Cod].ALLMEMBERS ,
[Dim Costumer].[Age Group].&[0-14],
[Dim Store].[Cod].&[51],
[Dim Date].[D].&[2010-07-21]
)
July 21, 2010 at 11:22 am
If you already have a count measure, usually defined by the cube design wizard in the form "<<MeasureGroupName>> Count",
then
select [Measures].[MeasureGroupName Count] on 0,
FILTER([dimCustomer].[Age].Children, [Measures].[Sales]<>0) on 1
from <<YourCubeNameHere>>
Will yield the count of all measuregroup rows, with non-zero sales, grouped by Customer Age (in dimension order).
barry
July 21, 2010 at 11:49 am
thank you very much for your answer!! but can you explain me better your code? can you please explain me what do mean by [DMCount]?
do you mean to this code:
WITH MEMBER [Measures].DMCount AS
Count((
[Dim Costumer].[Cod].ALLMEMBERS ,
[Dim Costumer].[Age Group].&[0-14],
[Dim Store].[Cod].&[51],
[Dim Date].[D].&[2010-07-21]
)
)
SELECT
[DMCount] ON 0
FROM [StoreDM]
WHERE
(
[Dim Costumer].[Cod].ALLMEMBERS ,
[Dim Costumer].[Age Group].&[0-14],
[Dim Store].[Cod].&[51],
[Dim Date].[D].&[2010-07-21]
)
i try-it and it didn't work... sorry!!
thanks
cafc
July 21, 2010 at 12:18 pm
hi Barry Ralston,
i try to use the measure count as you said... but this measure will count the number of sales of costumer and not number of coustumers.... you see?
thanks
cafc
July 22, 2010 at 2:40 am
Are you looking for something like this?
WITH
SET FilteredSet AS filter (
[Dim Costumer].[Cod].ALLMEMBERS,
[Dim Costumer].[Cod].CurrentMember.Properties("Age Group") = ... and
[Dim Costumer].[Cod].CurrentMember.Properties("Cod") = ... and
[Dim Costumer].[Cod].CurrentMember.Properties("D") = ...
)
MEMBER [Measures].[x] AS Count(FilteredSet)
SELECT {[Measures].[x]} ON 0,
FilteredSet ON 1
FROM [StoreDM];
July 22, 2010 at 3:21 am
hi,
i try your code as followed:
SET FilteredSet AS filter (
[Dim Costumer].[Cod].ALLMEMBERS,
[Dim Costumer].[Age Group].CurrentMember.Properties("Age Group") = "0-14" and
[Dim Store].[Store Cod].CurrentMember.Properties("Store Cod") = 4
)
MEMBER [Measures].[x] AS Count(FilteredSet)
SELECT {[Measures].[x]} ON 0,
FilteredSet ON 1
FROM [StoreDM]
But always return empty values...
thanks
July 22, 2010 at 3:34 am
try the query below it should perform better the using the filter function
WITH
SET FilteredSet AS count (
[Dim Costumer].[Cod].ALLMEMBERS *
[Dim Costumer].[Age Group].&[0-14] *
[Dim Store].[Cod].&[51] *
[Dim Date].[D].&[2010-07-21]
)
MEMBER [Measures].[x] AS Count(FilteredSet)
SELECT {[Measures].[x]} ON 0,
FilteredSet ON 1
FROM [StoreDM];
July 22, 2010 at 3:43 am
it return the following error:
"the function expects a tuple set expression for the argument. A string or numeric expression was used"
July 22, 2010 at 4:00 am
sorry, it should have been
WITH
SET FilteredSet AS
[Dim Costumer].[Cod].ALLMEMBERS *
[Dim Costumer].[Age Group].&[0-14] *
[Dim Store].[Cod].&[51] *
[Dim Date].[D].&[2010-07-21]
MEMBER [Measures].[x] AS Count(FilteredSet)
SELECT {[Measures].[x]} ON 0,
FilteredSet ON 1
FROM [StoreDM];
July 22, 2010 at 4:51 am
if you want to see what's going on you could please visit this link:
http://img84.imageshack.us/img84/3012/mdx.png
the mdx code that you provide generate the result that i show on this picture.
but it not what i expect to.
thanks.
July 22, 2010 at 5:06 am
shouldn't SET FilteredSet AS count in the second line, be just a tuple to define the set? That is: Set FilteredSet as (Dimensions that define the set)
In other words, if the problem is to count all customers of certain criteria Age, Code, Date, then the FilteredSet establishes the area of the cube containing the Customers of interest. Then a simple count(FiltereredSet) yields the number of those customers, correct?
July 22, 2010 at 5:09 am
Yes, I think I may have answered an outdated version of the question. Is the problem then:
count of all customers to which a sale has been made, by age?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply
