RE: dispaying nested members from same dimension on one axis?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Matt (Matt_at_discussions.microsoft.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 11:55:03 -0700

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:

> 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:
>
> > 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

Relevant Pages