MDX year,month function

Is there a equivalent function from mdx to convert date to year or month like datepart from sql? ytd for example seems only working on a specified date member. But I want something like :

WITH

MEMBER [Measures].[ParameterCaption] AS '[aaaa].[bbbb].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[aaaa].[bbbb].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[aaaa].[bbbb].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

[aaaa].[bbbb].AllMembers ON ROWS

FROM [XXXX]

...

but return all available years with the dates in the cube.

Thanks.

Regards

Alu

What I did was to amend my datasource view on which my cube is based, for date with a month and year field.

Then I did not need to worry about this in the MDX.|||MDX supports VBA function VBA!DatePart, but it performs worse then other MDX functions.|||

Hi thanks for the reply. However I have a data source view time dimension table and the mdx query which i have provided does not come from it. rather it comes from just a field with normal dates.

Thanks.

Regards

Alu

MDX Syntax equivalent of SQL (NOT IN) Clause

I have a cube that I'm trying to separate into 2 distinct populations. After filtering on several combinations of dimensions, I get a set of unique people. I have the MDX syntax for this and it works well. However, I then want to turn around and get all of the other people not selected in the first group.

I can't merely take the "not" dimensions of the first group because I have both one-to-one and one-to-many dimensions and doing that will double count a lot of them. Oh yea, I've got a distinct count member hanging around that is needed.

In sql, its very rudimentry but using the "where x not in (select y from tablename .... )" will get the all the others not in the first group.

Any suggestions on getting the equivalent statement in MDX built?

Thanks! jmac

Hi,

I think you should be able to accomplish what you're trying to do using the 'Except' function. Google has lot's of examples of it..

C
|||I'll take a look. Not sure how to get both results into two sets for comparison.|||If you have some crossjoining to get to the set of members you are interested in, you might also need to use the Extract() function in combination with Except() to get just the members from one attribute.

MDX Syntax equivalent of SQL (NOT IN) Clause

I have a cube that I'm trying to separate into 2 distinct populations. After filtering on several combinations of dimensions, I get a set of unique people. I have the MDX syntax for this and it works well. However, I then want to turn around and get all of the other people not selected in the first group.

I can't merely take the "not" dimensions of the first group because I have both one-to-one and one-to-many dimensions and doing that will double count a lot of them. Oh yea, I've got a distinct count member hanging around that is needed.

In sql, its very rudimentry but using the "where x not in (select y from tablename .... )" will get the all the others not in the first group.

Any suggestions on getting the equivalent statement in MDX built?

Thanks! jmac

Hi,

I think you should be able to accomplish what you're trying to do using the 'Except' function. Google has lot's of examples of it..

C
|||I'll take a look. Not sure how to get both results into two sets for comparison.|||If you have some crossjoining to get to the set of members you are interested in, you might also need to use the Extract() function in combination with Except() to get just the members from one attribute.

MDX query for SQL equivalent function IN

I need help in writing MDX query which will return the count of Sales for differnt SalesPerson. Something equivalent to SQL query

Select Sales_Count from Sales where SalesPersonId IN (6200, 4367, 87650, 2222, 18, 334, 9090).

There are hundred thousands of SalesPersons, so is there any way i can run the MDX Query for a batch of SalesPersonId s. One more problem is the SalesPersonId s are not consecutive..Please can anyone help me solve this problem.

How are your user's selecting which sales persons to return?

B.

|||

Just place your MDX strings for each ID you want to be returned in the ROWS() part of the MDX.

SELECT { [Measures].[SomeCount] } ON COLUMNS,

{ [Heir].[ID1], [Heir].[ID2], [Heir].[ID3], [Heir].[ID4] } ON ROWS

FROM [Cube]

Will return:

[Heir].[ID1] 1259

[Heir].[ID2] 51454

[Heir].[ID3] 65514

[Heir].[ID4] 124

|||

That is one of many ways to handle this problem. It just depends on what exactly you are wanting to have returned and how you intend to assemble your list of Sales Reps.

B.

|||

True, I just supplied the exact syntax for the SQL statement provided.|||If you have a (front-end) Sql client that doesn't expose value hierachies to the user, and hence rely on users typing in the literals. You may consider use the class of functions that convert strings to member/tuple/set; so it will not result into an invalid member error when your client translates sql to mdx.

select city, profit from sales where city in ('Redwood City', 'Fremont') group by city;
==>
select [Measures].[Profit] on columns,
StrToSet("{[Customers].[Redwood City], [Customers].[Fremont]}") on rows from sales

but if the user typed RedwoodCity instead or Redwood City is not a valid member, the query wouldn't fail, and would still return results for Fremont.

That may be why Brian was asking how the IN list is constructed.

MDX query for SQL equivalent function IN

I need help in writing MDX query which will return the count of Sales for differnt SalesPerson. Something equivalent to SQL query

Select Sales_Count from Sales where SalesPersonId IN (6200, 4367, 87650, 2222, 18, 334, 9090).

There are hundred thousands of SalesPersons, so is there any way i can run the MDX Query for a batch of SalesPersonId s. One more problem is the SalesPersonId s are not consecutive..Please can anyone help me solve this problem.

How are your user's selecting which sales persons to return?

B.

|||

Just place your MDX strings for each ID you want to be returned in the ROWS() part of the MDX.

SELECT { [Measures].[SomeCount] } ON COLUMNS,

{ [Heir].[ID1], [Heir].[ID2], [Heir].[ID3], [Heir].[ID4] } ON ROWS

FROM [Cube]

Will return:

[Heir].[ID1] 1259

[Heir].[ID2] 51454

[Heir].[ID3] 65514

[Heir].[ID4] 124

|||

That is one of many ways to handle this problem. It just depends on what exactly you are wanting to have returned and how you intend to assemble your list of Sales Reps.

B.

|||True, I just supplied the exact syntax for the SQL statement provided.|||If you have a (front-end) Sql client that doesn't expose value hierachies to the user, and hence rely on users typing in the literals. You may consider use the class of functions that convert strings to member/tuple/set; so it will not result into an invalid member error when your client translates sql to mdx.

select city, profit from sales where city in ('Redwood City', 'Fremont') group by city;
==>
select [Measures].[Profit] on columns,
StrToSet("{[Customers].[Redwood City], [Customers].[Fremont]}") on rows from sales

but if the user typed RedwoodCity instead or Redwood City is not a valid member, the query wouldn't fail, and would still return results for Fremont.

That may be why Brian was asking how the IN list is constructed.