quite new to MDX and have a small problem.

i have a time dimension which has year, quarter, month, day etc all cleaned
up so as only to show used dates

levels like this

2003 etc
q1, q2, q3 etc
jan, feb, march etc

problem is i want an mdx query that will display the nested levels on one
axis so that when i issue a query it returns the results with the nested
levels above

e.g.
2004
q1 q2 q3
q4
jan feb march april may june july august

count 21 16 98 33

problem is i cant work out how to get mdx to display the levels of a single
dimension on one axis.. what im looking for is functionality like that in
analysis manager where you can drill into the higher levels and it displays
the lower levels nested above the results..... is my only option to split the
time dimension into other dimensions based upon year, quarter, month etc and
to crossjoin them on columns ???

thanks for any help i get

I'm not sure what do you mean by "mdx query that will display".

MDX is a query language that returns cellsets or flattened rowsets.
The way you display them is a client-issue. For instance, the MDX sample can
not display nested members but the Analysis Manager browser can. However both
may be using the same MDX query to populate the UI components.
Regards,
Brian
www.geocities.com/brianaltmann/olap.html

"Matt" wrote:

i suppose what i mean is that im looking for functionality like CROSSJOIN
that can be used on a single dimension so rather than having to produce
elaborate MDX in order to return only the years and relevant months then
"Process" them in the client app, the results i require will be available

i have the core mdx i require

select


CROSSJOIN
(
{[Time].[All Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter 2]},
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY
CROSSJOIN
(
{[dim_Team].[Team Name].members},
CROSSJOIN
(
{[dim_emp].[Employee Name].members},
{[Customer].[Company Name].Members}

)) ON ROWS
FROM TMS_SALESNOINV

this returns the exact results i want EXCEPT the year which i would like to
display above the quarter labels on the columns... like i say im a bit of a
beginner... im using sql reporting services by the way

thanks

Matt



"Brian Altmann" wrote:

You want to display different levels from one dimension on an axis in the
hierarchical structure. Therefore no crossjoin is necessary, just a set that
contains the different members.

with
set [quarters] as
' {[Time].[All Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter
2]}'

set [years] as
'{[Time].[All Time].[2003], [Time].[All Time].[2004]}'

set [myTime] as
' hierarchize ( {[quarters], [years] }) '

select

CROSSJOIN
(
{ [myTime] },
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY
CROSSJOIN
(
{[dim_Team].[Team Name].members},
CROSSJOIN
(
{[dim_emp].[Employee Name].members},
{[Customer].[Company Name].Members}

)) ON ROWS
FROM TMS_SALESNOINV

cheers,
Matthias

"Matt" <Matt (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news:8E7EE139-8469-444A-89F6-1CF3780351CA (AT) microsoft (DOT) com...

Since you are working with Reporting Services, what you are getting from your
MDX queries are flattened rowsets (a two-dimensional representation of a
cellset).
If you really want to understand how to manipulate the query in order to
achieve the exact results you need, you might want to take a look at the
"Flattening Algorithm" topic in the OLE DB Programmer's Reference BOL.
Short, but not pretty. It will certainly save a lot of experimenting, though.
Regards,
Brian

"Matt" wrote:

Sorry Brian, but i think you did not answer Matt's question quite right.

I have the same problem. With the query that you are suggesting, the data structure that you get looks like this:

2002 q1 January February 2003

val val val val val

but what Matt and I are looking for is something like this:
2002
q1 q2
january february January February
val val val val

thanks

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....rver-olap/4381
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....bf04fb3e0e7151
*****************************************