Re: How to Add member to MDX query

#1

I think this is what you want:

With
Member [Site].[All Site] as '[All Site] '
SELECT
{Measures.NullColumn} ON COLUMNS,
{ [Site].[All Site], [Site].[Sitid].Members) } DIMENSION Properties
Member_Unique_Name ON ROWS
FROM
Session

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

Quote:

I'm still confused. Running this query gives me a NULL row for All Sites.
It does not give me a unique name of [Site].[All Site], which is what I
want.

"Brian Altmann" wrote:

You could retrieve member unique names using:

WITH MEMBER Measures.NullColumn AS 'Null'
SELECT
{Measures.NullColumn} ON COLUMNS,
{ [Site].[All Site], [Site].[Sitid].Members) } DIMENSION Properties
Member_Unique_Name ON ROWS
FROM
Session

and using the unique name instead of the id as parameter.
HTH,
Brian

"Shelley" wrote:

Yes, but that gives me a null record, and therefore my parameter list
for
Site has NULL. I want it to say "All" or "All Site". This also
breaks my
query because in my query I do the following. It fails because
Parameters!Site.Value is null. I guess I need it to say "All Site"

="SELECT Descendants([ClickCost].[All ClickCost],
[ClickCost].[keyword],
LEAVES) on AXIS(1),
{ [Measures].[Sessessionid], [Measures].[Sesad Cost],
[Measures].[Clkid] }
ON AXIS(0),
{ [Time].[" & Parameters!Year.Value & "].[" & Parameters!Month.Value &
"] }
on AXIS(2),
{ [Site].[" & Parameters!Site.Value & "] } on AXIS(3)
FROM SessionClickData"

"Brian Altmann" wrote:

You mean, as in:

WITH MEMBER Measures.NullColumn AS 'Null'
SELECT
{Measures.NullColumn} ON COLUMNS,
{ [Site].[All Site], Descendants([Site].[All Site],
[Site].[Sitid],
LEAVES) } ON ROWS
FROM
Session

BTW, assuming Site.Sitid to be your leaf level, you could use:

WITH MEMBER Measures.NullColumn AS 'Null'
SELECT
{Measures.NullColumn} ON COLUMNS,
{ [Site].[All Site], [Site].[Sitid].Members) } ON ROWS
FROM
Session

Finally, if you are buliding these queries to retrieve dimension
metadata,
it is best to use OLEDB for OLAP schema rowsets, which are also
exposed
through ADO MD, ADO MD.Net and the XML/A Discover method.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Shelley" wrote:

I have a query which retrieves the list of site names and
corresponding IDs.
It is below. I want to actually display to the user, an option to
choose All
Sites. So, another element appended to the top of the list, where
the site
name is All and the ID is 0. Any ideas on how to do this? It's
easy in SQL,
but not so easy in MDX.

WITH MEMBER Measures.NullColumn AS 'Null'
SELECT
{Measures.NullColumn} ON COLUMNS,
{Descendants([Site].[All Site], [Site].[Sitid], LEAVES) } ON
ROWS
FROM
Session

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: How to Add member to MDX query

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: How to Add member to MDX query - 08-18-2004 , 01:35 PM