Greetings,

I am just starting out with MDX Queries. I am practicing with a DB from a
CD that came with a this book I am using "Sql Server Olap" Developer's Guide
(note: I am not real crazy about this book - the author assumes a lot of
detail stuff is already known - so I appologize in advance if some of my
questions seem kind of lame). The database is called "FundAcctSample", and
the cube I am practicing with is called "Investments and contains these
dimensions:

Account
Fund
FundAge
Manager
Measures
Office
Time

For the query in question I will be using the "Office" dimension which
contains these members:
(All)
Region
Accounting
Business

I will be querying the "Business" member which contains these members:
Conneticut
Vermont
Massachusetts
New York

The following MDX query produces 1 row of columns like this:

select
[Office].[business].Members on columns
from Investments

Results
Conneticut Vermont Massachussets New York
$1,740.321.00 $1,407.349.08 $584.393.24

For the sake of learning MDX Queries I want to list the result above as
rows. I tried this which did not work:

select
[Office].[business].Members on rows
from Investments

I am guessing that the "on rows" clause must come after "on columns". Could
someone explain? Is there a way to list my results above in one column and
several rows instead of one row and several columns?

Thanks,
Rich

try this:
select
[Office].[business].Members on columns,
measures.members on columns
from Investments


"Rich" <Rich (AT) discussions (DOT) microsoft.com> wrote

Thanks. I try

select
[Office].[business].Members on columns,
measures.members on columns
from Investments

But I get an error message

"Unable to open cellset
Axis name is unrecognizedor duplicated, or creates a sequence gap, 'columns'."

so I switched it to

select
[Office].[business].Members on columns,
measures.members on rows
from Investments

which produced this result set (with this same exact spacing)

Conneticut Vermont Massachusets
New York
Amount $1,740,32.00 $1,407,349.08
$584,393.24
Sh Num 90,722.92 68,520.01
28,634.98


where the measures "Amount" and "Sh Num" are columns in the Fact Table
"Investments" and each business is aggregated (summed) on "Amount" and
"Sh_Num". I was able to produce the same numbers in query analyzer. I can
sort of see how MDX queries are a lot easier to use (once you get the hang of
it) rather than writing out the same tsql in query analyzer.

here is my tsql equivalent (well, the same numbers but not the same shape)

select t2.business, sum(t1.amount) 'amount', sum(t1.sh_num) 'sh num' from
investments t1 join offices t2 on
t1.office_num = t2.office_num
group by t2.business

produces:

Business Amount Sh Num
Connecticut 1740321.0000 90722.9200
New York 584393.2400 28634.9800
Vermont 1407349.0800 68520.0100

Well, it is a start. Thank you for your help.

Rich

"Jéjé" wrote:

try this one

SELECT Measures.Members ON COLUMNS,
[Office].[business].Members ON ROWS
FROM Investments


It will give you the desired result.

Faraz

yep, sorry for the mistake, its "on ROWS" instead-of "on columns".


"Faraz" <ahmed.faraz1 (AT) gmail (DOT) com> wrote