MDX Select - To use in Client App

We have a client app that used MDX to display data in the fromtend in grid. And we have requirement to filter by a dimension value (multiselect) + also display the same in the grid. But I'm aware that this is not possible using basic MDX.

I'm trying to acheive something similar to below query i.e. A dimension in slicer Axis and also in rows. Is it possible?

SELECT { [Measures].[LTD Value]} ONCOLUMNS,

NONEMPTY

(

[Currency].[Currency].[Currency].Members ,

[Trade].[Source Trade Code].[Source Trade Code].Members

) ONROWS

FROM [MyCube]

WHERE (

[Calendar].[Calendar].[Date].&[2007-01-04T00:00:00]

,{[Currency].[Currency].[Currency].[EUR], [Currency].[Currency].[Currency].[GBP] }

);

thanks,

Arun

You can define an explicit set on an axis and do a cross product of that set against another:

Code Snippet

select

[Measures].[Reseller Sales Amount] onColumns,

{[Date].[Calendar].[Month].[May 2004],

[Date].[Calendar].[Month].[June 2004]} * [Product].[Category].[Category].MembersonRows

from [Adventure Works]

If you need to make the set more dynamic, look at the STRTOMEMBER() and STRTOSET() functions.

Thanks,

Bryan

|||

Hi Arun,

Have you tried using a sub-select MDX query, like:

select

{[Measures].[Average Rate]} on 0,

[Destination Currency].[Destination Currency Code].[Destination Currency Code] on 1

from (select

{[Destination Currency].[Destination Currency Code].[EUR],

[Destination Currency].[Destination Currency Code].[GBP]} on 0

from [Adventure Works])

where [Date].[Calendar].[Date].[January 4, 2004]

--

Average Rate
EUR .92
GBP 1.46

|||Thanks guys. Both the above solution works.

MDX Question

Can I do an MDX query with a start/stop filter specified as dates, but aggregate results by month? For example, could I query data between Jan 15, 2005 and April 14, 2005 and get results aggregated by month? I would expect four resulting rows or cells:

January (only contains data from the fifteenth to the thirty first)
February (data for entire month)
March (data for entire month)
April (only contains data from the first to the fourteenth)

I know how to filter by month and aggregate by month:

SELECT [Date].[Month].[2005-01]:[Date].[Month].[2005-04] ON Columns FROM [My Cube];

I also know how to filter by day and aggregate by day:

SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube];

But I don't know how to filter by day and aggregate by month. Is this at all possible using MDX?

SELECT Date.Month.Month.MEMBERS ON 0 FROM

(SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube])

|||

Mosha Pasumansky wrote:

SELECT Date.Month.Month.MEMBERS ON 0 FROM

(SELECT [Date].[Day].[2005-01-15]:[Date].[Day].[2005-04-14] ON Columns FROM [My Cube])

Brilliant! That worked. Thanks!

MDX query with filter clause

Hi,

I have the following MDX query which is supposed to return the number of records with Actual time > TargetTime

for each customer, location and editor. Actual Time and Target Time are measures on the Fact table defined in minutes and represented by integer values.

The hierarchy I have created is

Customer Name

Location Name

Editor Name

The MDX query I have written is:

Count(filter(Descendants([DimCustomer].[Customer_Hierarchy].CurrentMember,[DimCustomer].[Customer_Hierarchy].[EditorName]), [Measures].[ActualTime]>[Measures].[TargetTime] ))

But this query gives me the number of customers rather than the number of records with ActualTime > TargetTime

for each customer, location, editor.

Any help would be appreciated.

The best approach here in my opinion would be to create calculated column in DSV returns 1 when ActualTime > TargetTime and 0 otherwise, and then define a new measure on top of this column with Aggregation Type as SUM.|||

I have implemeneted a dsv for the above with a measure named turnaroundtime to get the count of records with ActualTime > TargetTime

I also need to get the count of documents for each customer -> location -> editor where actual time > target time and edit type = Voice

Documents is the fact table. For each document in the fact table, the edit type field specifies if its 'voice' or 'other'

I was trying to get the filter clause to work for this.

|||

Can you create another dimension for Edit Type? If you did this, then you'd be able to filter a query using the new dimension, selecting the member [Voice].

HTH,

Dave Fackler

|||

I created another dimension for edit type but not sure how to write the calculated member.

I need the count of documents where Edit type = Voice and ActualTime > TargetTime

documents is the fatc table.

How do I write the query to get the number of documents for each customer -> location->editor.

I am new to MDX and not able to figure it out

|||

Assuming you want this as an additional calculated measure (and not just doing the filtering within a given query), you could add a calculated member named something like [Measures].[VoiceTurnAroundTime] with a calculation of "([Measures].[TurnAroundTime], [Edit Type].[Voice]")...

HTH,

Dave Fackler

|||I tried that but that displays an error.|||Can you provide the exact calculation as you have it typed in and the error being displayed?

Dave F.|||It worked...thanks a lot.

MDX query to filter the cube

I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :

pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

Now

my requirement is to show a filtered cube, not the whole cube. I am

using SQL server 2000 Analysis Services to prepare and store the cube.

I

think an MDX query as commandText can do this. But I am not being able

to write the suitable MDX that can give a filtered cube which I can

use to populate pivot table?

I have used MDX query as "select

from <cube name> where <filter condition(s)>" . But it is

showing that no column found that excel can use.

What would be the currect MDX?

I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.

Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.

That should give you an idea how to control OWC.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.

There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:

Code Snippet

varArray = Array("Bikes")

pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray

MDX query to filter the cube

I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :

pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

Now my requirement is to show a filtered cube, not the whole cube. I am using SQL server 2000 Analysis Services to prepare and store the cube.

I think an MDX query as commandText can do this. But I am not being able to write the suitable MDX that can give a filtered cube which I can use to populate pivot table?

I have used MDX query as "select from <cube name> where <filter condition(s)>" . But it is showing that no column found that excel can use.

What would be the currect MDX?

I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.

Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.

That should give you an idea how to control OWC.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.

There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:

Code Snippet

varArray = Array("Bikes")

pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray

MDX query to filter out members starting with a particular alphabet

Hi,

I would like to come up with an MDX query such that the query works on a subset of the members present at a particular level of a dimension. A filter would do but the subset should have all the members whose name start with alphabet falling in the range 'A' - 'K'. Is there a way to have a regular expression in the filter of the MDX query.
Do help me out on this with your suggestions.

cheers,
Arun

I would you reccomend to use pure MDX. It is the quickest method. It use indexes on hierarchies. If you use VBA fubnctions or another stuff SSAS schuld mack full scan of hierarchy and the query would be slower.

Filter([Dimension].[Hierarchy].[Level].members, ([Dimension].[Hierarchy].CurrentMember.Name >= "A" and [Dimension].[Hierarchy].CurrentMember.Name < "L"))

|||Thanks a bunch Vladimir for the timeous reply
Is it also possible to filter the members by count ? I mean can I get the first 10 members at a level in one query followed by the next 10 in another query and so on.

Thanks in advance.

Arun
|||

Maybe like this:

BottomCount( TopCount( {YourSet}, X ), 10 )

Where you control X each time you query, e.g.

BottomCount( TopCount( {YourSet}, 10 ), 10 )

BottomCount( TopCount( {YourSet}, 20 ), 10 )

BottomCount( TopCount( {YourSet}, 30 ), 10 )

Best regards

- Jens

|||Hi Jensch,

Thanks a lot. It was quite handy. Much appreciated.

cheers,
Arun

MDX query to filter out members starting with a particular alphabet

Hi,

I would like to come up with an MDX query such that the query works on a subset of the members present at a particular level of a dimension. A filter would do but the subset should have all the members whose name start with alphabet falling in the range 'A' - 'K'. Is there a way to have a regular expression in the filter of the MDX query.
Do help me out on this with your suggestions.

cheers,
Arun

I would you reccomend to use pure MDX. It is the quickest method. It use indexes on hierarchies. If you use VBA fubnctions or another stuff SSAS schuld mack full scan of hierarchy and the query would be slower.

Filter([Dimension].[Hierarchy].[Level].members, ([Dimension].[Hierarchy].CurrentMember.Name >= "A" and [Dimension].[Hierarchy].CurrentMember.Name < "L"))

|||Thanks a bunch Vladimir for the timeous reply
Is it also possible to filter the members by count ? I mean can I get the first 10 members at a level in one query followed by the next 10 in another query and so on.

Thanks in advance.

Arun
|||

Maybe like this:

BottomCount( TopCount( {YourSet}, X ), 10 )

Where you control X each time you query, e.g.

BottomCount( TopCount( {YourSet}, 10 ), 10 )

BottomCount( TopCount( {YourSet}, 20 ), 10 )

BottomCount( TopCount( {YourSet}, 30 ), 10 )

Best regards

- Jens

|||Hi Jensch,

Thanks a lot. It was quite handy. Much appreciated.

cheers,
Arun

MDX query

I'm building a report from SSAS 2005 with three parameters.

start date

end date

project stream

I would filter only the non empty project stream in the actual cost fact table included in the time interval specified with the first two paramenters.

SSRS automatically compose an MDX query that I try to modify to get what I want:

WITHMEMBER [Measures].[ParameterCaption] AS'[PROJECT].[STREAM].CURRENTMEMBER.MEMBER_CAPTION'MEMBER [Measures].[ParameterValue] AS'[PROJECT].[STREAM].CURRENTMEMBER.UNIQUENAME'MEMBER [Measures].[ParameterLevel] AS'[PROJECT].[STREAM].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], [Measures].[EUR] } ONCOLUMNS ,

NONEMPTY [PROJECT].[STREAM].ALLMEMBERSONROWS

FROM [PMO_MDB]

WHERE { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END)}

In this way I get all project streams and I'm not able to filter away the empty cell (using the [Measures].[EUR] defined in the actual cost fact table). In fact SSRS put some intrisict member properties on columns as calculated measure !

There is another way to extract the same info but filtering away the empty cell ?

Cosimo

Take a look at the EXISTING function. I believe you could use it on your row axis to limit Project.Stream members to those associated with a fact entry.

Bryan

|||

Thanks Brjan ... I tried but I get the same result also using EXISTING !

I think the problem is that SSRS put on columns as a calculated measure some properties.

There is another way to extract such properies ?

I try the dimension properties clause ... but nothing happen !

Cosimo

|||

Sorry. I meant the EXISTS function. (The names are so similar I always screw them up.) Here is an example of what I'm describing:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

EXISTS(

[Product].[SubCategory].Members,

{ STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")},

'Reseller Sales'

) on 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

For comparison:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

[Product].[SubCategory].Memberson 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

Sorry for the confusion,

Bryan

Mdx query

Hello guys,

Assume I have a date dimention in a cube and I want to retrive data based on the StartDate and EndDate value. In short, I want to filter all records between StartDate value and EndDate value which is given by a user. Can any body give a general syntax or mdx query to achive this goal?

Sincerely,

Amde

Hello Amde

Assuming Analysis Services 2005:

SELECT {[Measures].[MyMeasures]} ON 0,

[MyDimension].[MyHierarchy].[MyLevel].members ON 1

FROM [MyCube]

WHERE {[Time].[Date].[<StartDate>] : [Time].[Date].[<EndDate>]}

|||

Thanks a lot michael,

But what if the the <StartDate> and <EndDate> values are parameter values that should be specified by a user? What will be the where condition in this case?

Sincerely,

Amde

|||The query itself needs to be the same. How would the user specify the parameters? Which client is the user using?|||

Hey michael,

That is a good question. I am going to write this query in the Report Designer; there will be report parameters <StartDate>and<EndDate> by which the user will specify and based on the parameter, I want to filter the record. I think this makes sense.

Sincerely,

Amde

|||

Ahhh... OK. Then I guess you just need to create two report parameters (for instance, named @.StartDate and @.EndDate). The values have to contain the unique member name for the dates. This could be something like [Time].[Date].&[20060518]. This value can the be passed to the MDX query. Now, I don't remember the exact syntax when you want to use report parameters in an MDX query, but I believe the WHERE statement should be something like this:

WHERE {StrToSet(@.StartDate.Value + ":" + @.EndDate.Value)}

|||

Hey Michael,

Thank you for sharing your idea, I know the syntax in reporting service; I will change the syntax and use it properly.

Sincerely,

Amde

MDX query

I have a dimension and a fact count.

How do i create a query to filter out the dimension using the filter function?

I tried something like :

SELECT {[Measures].[XX Count]} ON COLUMNS,

{ filter([RT].[RT].Members,[RT].[RT].CurrentMember < 10) } ON ROWS

FROM [YYYY]

but invalid.

for eg i have dimension RT with values 1,11,13,50 etc.... so in the row i would expect a row of RT with value 1 and its respective XXCount.

Thanks.

Regards

Alu

This is most likely caused by the fact that you have multiple measures in your cube and when you did not specify a particular measure in the filter clause the default one was used instead of the count measure. The measure on the columns defines the context for the value that is returned in the cells, but not necessarily for the value that is used to evaluate the filter condition. You should change your filter expression to explicitly mention the measure you want to use.

eg.

SELECT {[Measures].[XX Count]} ON COLUMNS,

{ filter([RT].[RT].Members,([RT].[RT].CurrentMember,[Measures].[XX Count] ) < 10) } ON ROWS

FROM [YYYY]

MDX performance

Hi,
the topic of counting customers has been discussed several times in this forum but I have still a question. I want to filter customers that hav ea certain amount of sales per period. Instead of using a Count(Filter(customer) approach, I created a calculated member that "filters" a distinct count measure (Customer Key Distinct Count). For leaf members the count is multiplied with 1 or 0 depending on sales, for nodes a rollup takes place. The result is correct, but performance is poor. Is it possible to tune the expression?

CREATE MEMBER CURRENTCUBE.[MEASURES].Test
AS iif (count([Customer].[Customer Geography].currentmember.Children) = 0
-- leaf level
,
([Measures].[Customer Key Distinct Count])*
iif(([Measures].[Internet Sales-Sales Amount],[Customer].[Customer Geography].currentmember, [Date].[Calendar Time].currentmember)>3400,1,0)

--else (nodes)
,RollupChildren([Customer].[Customer Geography].currentmember,"+")

),

I'm using SSAS 2005
Thanks,

Marcus

Hi Marcus,

The expression above seems to be filtering leaf nodes of the [Customer Geography] hierarchy - if that's similar to Adventure Works, why is [Customer Key Distinct Count] evaluated for each customer (it should be 1 anyway)? Could something simpler serve your purpose, like:

With

Member [Measures].[Test] as

Count(Filter(existing [Customer].[Customer].[Customer]

, [Measures].[Internet Sales Amount] > 3400))

Select

{[Measures].[Test]} on 0

from [Adventure Works]

Test
3509

|||Hi Deepak,
thank you very much for spending your time on this question. Indeed I used the Count(Filter) approach first but it yielded a poor performance. So I tried it with a (Disitnct) Count Measure.

Cheers,
Marcus

MDX Parent-Child Hierarchy ==> help

All,

I have a parent-child hierarchy and want to acheive the following through MDX in a single query

Filter for a member in parent-child hierarchy (any level ) - I can do that by using key Display the results at a fixed level (say Level 2, with 1 being the top level). The filter in the above point can be set from level 1 to level 5. Display always Level 1 and Level 2 in seperate columns in the report (with filters applied as specified in pt 1)

The foll query is in Adventure Works and says what I'm trying to acheive. 1. Filter for a member in parent-child, 2 & 3. Display Level 1 & 2 members. I know the below will not work but I have listed it to give an idea of what I'm trying to acheive.

Code Snippet

SELECT [Measures].[Amount] on 0

, {

[Organization].[Organizations].[Organization Level 01].Members,

[Organization].[Organizations].[Organization Level 02].Members

} on 1

FROM [Finance]

WHERE [Organization].[Organizations].&[6]

Please can someone help me with the query?

thanks...

Not knowing your application, it seems a little awkward to me, but if it meets your needs, go for it.

Still, one thing to consider is how a sub-select affects the cube space. The best way to explain this is to not and to simply show some data.

If you run your query above, you get a total of $12M for the North America. This is the North America total. However, is this what you really want? (Might be.)

In your sub-select, you attempt to limit on Organizations member with Key of 6. That is the Southeastern division member in Level 4 which has a total of only $216K. You can run the query below to see this.

By moving the criteria to the WHERE clause, we affect the values returned. Now, you can't obviously have the Organizations hierarchy in two places. But if you remember the leaf-level of this dimension is the Organization attribute hierarchy, you can reference the Southeastern division using that hierarchy.

So, the question is, are you wanting the $12M for North America or just wanting to see the Southeastern division's contribution of $216K?

Good luck,

Bryan

Code Snippet

WITH MEMBER Measures.OrgLvl1 AS

'[Organization].[Organizations].CurrentMember.Parent.Member_Value'

select

{Measures.OrgLvl1 ,[Measures].[Amount]} on 0,

[Organization].[Organizations].[Organization Level 02].Memberson 1

from Finance

WHERE [Organization].[Organization].&[6]

|||

Thanks Bryan. You are right. I need to use it in where clause using diff attribute.

Just noted one more thing. The parent value measure is diff in each of the above queries as the context of current member changes. I need the results of your corrected query with the parent as defined in my query (Parent should be AdventureWorks Cycle).

|||

Code Snippet

select

[Measures].[Amount] on 0,

EXISTS(

{

[Organization].[Organizations].[Organization Level 01].Members,

[Organization].[Organizations].[Organization Level 02].Members

},

[Organization].[Organizations].&[6]

)

on 1

from [Adventure Works]

|||

The problem is that I need the results in the following format. (one of the criteria that I had mentioned)

Col 1 (Org Level1 ) col 2 (Org Level 2) Measures.Amount

The above resultset filter for "[Organization].[Organizations].&[i/p]"

|||

I think I got it working using subqueries and calculated member. Pl advice if its not the way to do it or if there is a efficient way

Code Snippet

WITHMEMBER Measures.OrgLvl1 AS '[Organization].[Organizations].CurrentMember.Parent.Member_Value'

select {Measures.OrgLvl1 ,[Measures].[Amount]} on 0,

[Organization].[Organizations].[Organization Level 02].Memberson 1

from

(

Select [Organization].[Organizations].&[6] on 0

FROM [Finance])