MS SQL Server OLAP - how to list by rows instead of columns?

Author

how to list by rows instead of columns?

Rich

2005-12-26, 8:23 pm

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

Jéjé

2005-12-26, 8:23 pm

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

"Rich" <Rich@discussions.microsoft.com> wrote in message
news:DF6C9AE1-A636-411D-854E- E867CEA17DDE@microso
ft.com...
> 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
>
>
>
>

Rich

2005-12-26, 8:23 pm

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:
> select
> [Office].[business].Members on columns,
> measures.members on columns
> from Investments
>
>
> "Rich" <Rich@discussions.microsoft.com> wrote in message
> news:DF6C9AE1-A636-411D-854E- E867CEA17DDE@microso
ft.com...
>
>
>

Faraz

2005-12-27, 3:23 am

try this one

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

It will give you the desired result.

Faraz

Jéjé

2005-12-27, 3:23 am

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

"Faraz" <ahmed.faraz1@gmail.com> wrote in message
news:1135660880.813009.165750@o13g2000cwo.googlegroups.com...
> try this one
>
> SELECT Measures.Members ON COLUMNS,
> [Office].[business].Members ON ROWS
> FROM Investments
>
>
> It will give you the desired result.
>
> Faraz
>

Sponsored Links