I have the following Dimensions in the cube:

- Plant
- Industry
- Customer
- Product
- Time
- UnitOfMeasure

and the following Measures:

- Quantity
- Sales
- COGS


I want a MDX Query which displays data in the following format:


================================================== ============
Time Plant Industry Product Quantity Sales Cogs
================================================== ============

The Cube name is Sales
Can you please provide the MDX query that can generate the above
output.

Thanks
Karen

You didn't mention what members of each dimension are to be included in
the query, but something like this:

{[Measures].[Quantity],
[Measures].[Sales],
[Measures].[COGS]} on columns,
Non Empty {[Time].Members
* [Plant].Members
* [Industry].Members
* [Product].Members} on rows
from [Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Many Thanks Deepak

Your help is deeply appreciated.

In Product I have three Levels - Hier01, Hier02 and Hier03 how do I get
all the three levels in this MDX query

Thanks
Karen

Hi Karen,

Which MDX client are you using - many of the tools will provide
information about the upper levels of a member. For example, Reporting
Services uses a "flattened" rowset, in which each level of a dimension
is represented as a field.

Another technique is to create calculated text measures for each level,
for which a label is needed.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak

Thanks for your time.

I am just using Excel 97 and VBA+ADO with MDX in it.
Appreciate the syntax how I can get the Product Levels


Also, when I use the following MDX:

Select
{[Measures].[Quantity],
[Measures].[Sales],
[Measures].[COGS]} on columns,
Non Empty {[Customer].Members,
*[Name].Members} on rows
from [Sales]


takes really long time and comes back with overflow errors. I am bit
surprised
Name is the customer name I have customer# in Customer and the
corresponding
Customer Name in Name dimension. I can't believe Analysis Services is
struggling
to respond for such a simple query which displays the customer# and the
customer Name
next to each other in Access this same query takes less than 10 seconds
and
analysis Services cannot appear to even handle it.

Appreciate your help.

Thanks
Karen

Hi Deepak

All I have is about 750 customers all I want to do is display customer
and name next to each other.
When I drop customer in Excel and drop a name on rows the query runs
for ages.

I am surprised at such a big limitation.

Thanks
Karen

Hi Karen,

From your description, maybe the problem is that the [Customer] and
[Name] dimensions are both referring to the same customer; so
{[Customer].Members * [Name].Members}
has > 500K combinations, of which only 750 are non-empty.
So, using NonEmptyCrossJoin() could improve performance:

{[Measures].[Quantity],
[Measures].[Sales],
[Measures].[COGS]} on columns,
NonEmptyCrossJoin([Customer].Members,
[Name].Members) on rows
from [Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak

Many thanks for your help.

It works like a charm now with this syntax.

Select
{[Measures].[Quantity],
[Measures].[Sales],
[Measures].[COGS]} on columns,
NonEmptyCrossJoin([Customer].M*embers, [Name]. Members) on rows
from [Sales]


But the only issue I have is along the row first I see
one row for All Customers and the Names of All customers occur against
it.

Secondly, for each customer Number I get two customer Name descriptions
one example is as follows:

Customer Name
=================================
000000401 All Names
Meredith Paints


It is bit puzzling how to suppress it all I want it the following
output

Customer Name
=================================
000000401 XYZ Co
000000435 ABC Co


I do not want the All Customer to be displayed and similarly
I do not want the All Names to be displayed.

Kindly please clarify how it can be fixed.

Thanks
Karen

Hi Karen,

You should be able to eliminate the [All] members by only using members
from a single level of each dimension. Depending on your level names,
something like:

[Measures].[Sales],
[Measures].[COGS]} on columns,
NonEmptyCrossJoin([Customer].[CustLevel].M*embers,
[Name].[NameLevel]. Members) on rows
from [Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Probably have more sense to use only one customer's dimension with a member
Key (like customer's id) and a member name (customer's name)... or even a
"Properties" for the customer's name.

Regards
Rodrigo

"Deepak Puri" wrote: