How to extract the underlying MDX

19 views
Skip to first unread message

Rose

unread,
Jan 11, 2006, 7:57:21 PM1/11/06
to
Is there a way to see the underlying MDX when querying through the cube
browser, either through Management Studio or Analysis Project?

I tried with SQL Profiler, and am seeing something that is not too
useful:

SELECT
NON EMPTY [{E551D847-D2CF-4C00-81AE-A8BA98DF2363}Pivot54Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures]...
}
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Chris Webb

unread,
Jan 12, 2006, 11:39:07 AM1/12/06
to
What you're seeing is indeed the MDX generated by the cube browser -
unfortunately most ad hoc query tools don't generate very clear MDX. Are you
looking to learn MDX? You're probably better off buying a book - here's a
list of AS books which are available or will be soon:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!322.entry

Trying to be 100% impartial, the consensus is that for absolute MDX
beginners then 'Fast Track to MDX' is a good place to start; various other
general AS books will have an introductory chapter or two on MDX. The second
edition of 'MDX Solutions' (of which I'm a co-author), which will be
published fairly soon, is I believe the only book that will go in-depth on
AS2005 MDX.

HTH,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/

Rose

unread,
Jan 13, 2006, 12:22:46 AM1/13/06
to
Hi Chris,

I have bought 2 books on MDX:
'Fast Track to MDX - 2nd Edition' and 'MDX Solution - 1st edition'

As we need the knowledge last year, we could not wait for the 2nd
edition to be released. Do you know which distribution/publish give
better offers for the 2nd edition, e.g. free copy of ebook is included
for those order directly from them, etc..?


Most of my team members are from OLTP/ETL background and are very
comfortable with TSQL. We are struggling/suffering from MDX, as it
looks like TSQL but is actually very different. We are struggling with
even very simple things sometimes, e.g.
1. finding out if there's a ISNULL() equivalent in MDX that can help
us handle the invalid dimension dimension elegantly (I posted my
question here
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/181d185e26b46c8c/662620eeb057390a#662620eeb057390a),
and also
2. the equivalent for <>, e.g. in TSQL it will be WHERE UserID <> 100.
We found a solution using EXCEPT(set1, set2) but are not sure if
that's the right way..... you can imagine the frustrations.....

Chris Webb

unread,
Jan 13, 2006, 5:41:02 AM1/13/06
to
Don't worry, I think we all felt the same way when we started learning MDX!
But when it all clicks, you'll never want to go back to SQL...

I have no idea if any of the books I mentioned have offers for people who
already own the first edition, I'm afraid.

Regarding your questions:
1) Where is the list of valid values you're using for your report parameter
coming from? It sounds like you're getting the list from the relational
source - if you got them from the cube instead you wouldn't have this
problem. For example you could use an MDX query which returned all the
Product Category members on rows (it should be easy to do this in the MDX
query designer) and bind that to your parameter.

The error you're seeing is a Reporting Services error so you won't be able
to tweak your MDX query to handle it better (in fact, you'll find that AS2005
handles this particular problem quite elegantly - see
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!166.entry
); I don't know whether it's possible to get RS to display a custom error in
this case though.

2) The EXCEPT function is the right way to go if you want to exclude members
whose names you already know; something like the FILTER function can be used
to filter sets based on measure values.

HTH,

Chris

Rose

unread,
Jan 13, 2006, 12:14:49 PM1/13/06
to
1) The report parameter is coming from AS indeed, and Reporting
Services does auto-generate a MDX that retrieves a list of the valid
values as you described. All is well, except that the consumer
web-application can invoke a report with a URL access without first
validating if that's an valid Dimension member.

Yes, you are absolutely right, the error is from Reporting Services,
because the parameter value received is not in the available value
list!
Now, if I can have another parameter that is accepting the value and is
not bound to the available value list, and when processing a call with
an invalid value, the error displayed is now
"Query (1, 598) The restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated. "

Now, that's an AS error, because [Product].[Category].&[5] (a
non-existing Dimension Member) has been used to make such a query:

WITH MEMBER [Measures].[Profit] AS
'[Measures].[Sales Amount]-[Measures].[Standard Product Cost]'
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Total Product Cost],
[Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
[Sales Territory].[Sales Territory Group].[Sales Territory
Group].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY,
[Sales Territory].[Sales Territory Group].[Sales Territory Group].KEY
ON ROWS
FROM ( SELECT ( STRTOSET('[Product].[Category].&[5]', CONSTRAINED) ) ON
COLUMNS FROM [Adventure Works])
WHERE ( IIF( STRTOSET('[Product].[Category].&[5]', CONSTRAINED).Count =
1,
STRTOSET('[Product].[Category].&[5]', CONSTRAINED),
[Product].[Category].currentmember ) )


In your posting, 'IIF(VBA!ISERROR(STRTOVALUE("MEASURES.[THIS WONT
WORK]")), 1,0)' is able to catch an invalid MEASURE. I suspect maybe
the following can help me to detect invalid dimension members?
WITH MEMBER MEASURES.TEST AS
'IIF(VBA!ISERROR(STRTOSET("[Product].[Category].&[1]",
CONSTRAINED).COUNT),1,0)'
MEMBER MEASURES.TEST2 AS
'IIF(VBA!ISERROR(STRTOSET("[Product].[Category].&[5]",
CONSTRAINED).COUNT),1,0)'
SELECT {MEASURES.TEST, MEASURES.TEST2} ON 0 FROM [Adventure Works]

Then, if I have prepared a 'DEFAULT' Dimension Member
([Product].[Category].&[-999] in the example below) to handle all the
invalid calls, then I may be able to tweak the MDX to the following?

WITH MEMBER [Measures].[Profit] AS '[Measures].[Sales
Amount]-[Measures].[Standard Product Cost]'
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Total Product Cost], [Measures].[Internet Order
Quantity] } ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY, [Sales
Territory].[Sales Territory Group].[Sales Territory Group].KEY ON ROWS
FROM ( SELECT ( IIF( STRTOSET(@ProductCategory).Count = 1,
STRTOSET(@ProductCategory, CONSTRAINED), [Product].[Category].&[-999] )
) ON COLUMNS
FROM [Adventure Works]) WHERE ( IIF( STRTOSET(@ProductCategory).Count =
1, STRTOSET(@ProductCategory, CONSTRAINED),
[Product].[Category].currentmember ) )

2. Chris, what's the function of the CONSTRAINED flag?
I found that if I include this CONSTRAINED flag into IIF(
STRTOSET(@ProductCategory, CONSTRAINED).Count = 1, the query above
stops working.

Chris Webb

unread,
Jan 16, 2006, 5:57:04 AM1/16/06
to
The CONSTRAINED flag is a security feature, and from what I understand it was
introduced in AS2005 MDX to stop MDX injection attacks (which I guess might
now be technically possible). What it does is restrict the StrToXXX family of
functions so that they no longer accept any MDX expression which evaluates to
a set - you have to use explicitly stated members. So, for example, StrToSet
has to take a string containing a list of unique names of members, and in
your example the fact that one of the members doesn't exist, which might
ordinarily be ignored, is now treated as a possible security problem and
instead raised as an error.

Of course as you recognise the real problem is that your app can invoke a
report without checking that the parameter relates to a real dimension
member. You probably ought to fix this rather than using StrToSet without
CONSTRAINED; at least, I don't want to be held responsible for any future
problems if you did use StrToSet without CONSTRAINED!

Rose

unread,
Jan 16, 2006, 10:58:47 AM1/16/06
to
Got your point!

OK, lets get the consuming application to first query AS to verify if
the member exists, would something like this be the correct kind of
query to be used?

WITH MEMBER [Measures].[X]
AS
STRTOSET('[Product].[Category].&[1000]', CONSTRAINED).Count
SELECT [Measures].[X] ON 0
FROM [Adventure Works]

My testing shows the count=1 is returned if the member is valid,
otherwise #ERROR is returned.

Chris Webb

unread,
Jan 16, 2006, 11:22:03 AM1/16/06
to
Yes, that seems ok if you're happy you can trap the error in your code.

Rose

unread,
Jan 20, 2006, 5:49:35 AM1/20/06
to
As there is no way to see the underlying MDX generated....:(, what
shall I do to get AS to return the data that is same when the "Show
Empty Cells" is enabled in a cube browser?

For example, "Show Empty Cells" is enabled when filtering by Country:
Country City Sales
UK London $100,000.00
UK Liverpool (blank)
....

When it is disabled, only UK->London is return because the data is not
blank.

I tried with including/excluding "NON EMPTY" in the MDX, there is no
difference :(

Chris Webb

unread,
Jan 20, 2006, 6:08:02 AM1/20/06
to
I'm a bit confused about what you're asking here, can you explain further
with an example query?


"Rose" wrote:

> As there is no way to see the underlying MDX generated....:(, what
> shall I do to get AS to return the data that is same when the "Show
> Empty Cells" is enabled in a cube browser?
>
> For example, "Show Empty Cells" is enabled when filtering by Country:
> Country City Sales
> UK London $100,000.00
> UK Liverpool (blank)

> .....

Rose

unread,
Jan 20, 2006, 11:44:41 AM1/20/06
to
I need to return in one dataset the T-SQL-like UNION effect from more
than 1 dimensions, e.g.

[Measures].[Applicable to Dimension A
only] [Measures].[Applicable to Dimension B only]
[Measures].[Applicable to Dimension C only] [Measures].[Applicable to
All]
DimA (null) (null) some-value
0 or (null)
0 or (null)
some-value
DimA DimB (null) some-value
some-value
0 or (null)
some-value
DimA DimB DimC some-value
some-value
some-value some-value

Does that make better sense to you?

It is very simple in T-SQL :( but it is frustrating to try to do it in
MDX, especially MS Reporting Services does not seem to be a good UDM
browser, and I always end up getting the unrelated Dim members in my ON
ROWS and that is leading to very poor performance for the reports, and
I have to resort to writing the MDX myself trying to achieve the effect
that is required.

Rose

unread,
Jan 20, 2006, 9:54:25 PM1/20/06
to
Please allow me to be more specific step-by-step, using Adventure Works
as example as follows:

1. I added 2 new Product Categories (Category A, Category B)
2. I then added 1 new Product Subcategory (Subcategory X), associated
to Category A
3. When I browse the Product Categories hierarchy in the Product
dimension, everything looks great - the empty hierarchy levels do
appear even they arent associated with any Product yet
4. I then run this MDX, hoping to see my blank hierarchy levels
appearing in the return ROWS
5. But only the Category with fact data is returned (in this case
{[Product].[Category].&[2]} )

Select {[Measures].[Reseller Sales Amount]} on columns,
{descendants([Product].[Product Categories].defaultmember, 0,
self_and_after) *
[Product].[Subcategory].[Subcategory] *
[Product].[Product Key].[Product Key]
} on rows
from [Adventure Works]
WHERE ({[Product].[Category].&[2], [Product].[Category].&[5],
[Product].[Category].&[6]})


How do I get AS to return ON ROWS the empty categories too
{[Product].[Category].&[5], [Product].[Category].&[6]}?

Displaying 0 or NULL on the empty fact cells ON COLUMNS is perfectly
fine, because all I need for these hierarchies is actually the
dimensional attributes, e.g. I can have measures defined just for the
categories such as "Category Created Date", "Number of Product
Categories", "Number of Product Subcategories" and the expected output
looks like

[Reseller Sales Amount] [Category
Count] [Subcategory Count] [Active Product Count]
"Category A" , "Subcategory X", (null), "2006-Jan-20"
$ 0.00 1
1 0
"Category B" , (null) , (null), "2006-Jan-20"
$ 0.00 1
0 0
"Components" , "Bottom Brackets" , "BB-7421", "2004-Aug-20"
$12,244.93 1
1 50

Chris Webb

unread,
Jan 23, 2006, 6:16:05 AM1/23/06
to
Hi Rose,

Thanks for the detailed example - I can see what's going on now.

The problem you're seeing is because you have crossjoined only the leaf
level of both the Subcategory and Product Key attributes in your query.
Because Category A, Category B and Subcategory X don't have a related member
on the leaf level of Product Key, and because Category B doesn't have a
related member on the leaf level of Subcategory, when you do this AS
automatically filters out these members. This behaviour is called
'autoexists' - for example, Category B doesn't 'exist' in the dimension
structure with any other member apart from the All Members on the other
attribute hierarchies. So in order to get your new members to appear you need
to make sure they are crossjoined with the All Members of the other attribute
hierarchies in your query. Here's a slightly modified version of your
Adventure Works query which shows this:

Select {[Measures].[Reseller Sales Amount]} on columns,
{descendants([Product].[Product Categories].defaultmember, 0,
self_and_after) *

[Product].[Subcategory].MEMBERS *
[Product].[Product Key].MEMBERS

} on rows
from [Adventure Works]
WHERE ({[Product].[Category].&[2], [Product].[Category].&[5],
[Product].[Category].&[6]})

Of course you probably don't want to include all the unnecessary All
Members, so you will want to construct your query so that they're only there
when they're needed, for example something like:

Select {[Measures].[Reseller Sales Amount]} on columns,

{[Product].[Category].&[5]
*
[Product].[Subcategory].[Subcategory].MEMBERS
*
[Product].[Product Key].[All Products]}
+
{([Product].[Category].&[6], [Product].[Subcategory].[All Products],
[Product].[Product Key].[All Products])}
+
{[Product].[Category].&[2] *
[Product].[Subcategory].[Subcategory].MEMBERS *
[Product].[Product Key].[Product Key].MEMBERS }


on rows
from [Adventure Works]

HTH,

Chris

Rose

unread,
Jan 23, 2006, 11:22:12 AM1/23/06
to
Hi Chris,

This is definitely closer to what I need. And I have tried with this
query

Select {[Measures].[Internet Sales Amount]} on columns,


{descendants([Product].[Product Categories].defaultmember,

1, self_and_after) *


[Product].[Subcategory].MEMBERS *
[Product].[Product Key].MEMBERS
} on rows
from [Adventure Works]

WHERE ({[Product].[Category].&[5],


[Product].[Category].&[6]})

The result is
Internet Sales
Amount
Category A All Products All Products (null)
Category A Subcategory X All Products (null)
Subcategory X All Products All Products (null)
Subcategory X Subcategory X All Products (null)
Category B All Products All Products (null)

1. Is there any way to auto detect the missing levels (the holes) in
the hierarchy and force the "All Products" to be returned as (null)
instead?

2. I am not very successful in using the second way that you suggested
- to construct the query so that the All Members are included only when
they are needed, simply because the query will be used in Reporting
Services and needs to be dynamically constructed given a known
dimension member to be the slicer (such as [Product].[Category].&[5] in
this example). When the slicer member is passed in as a parameter, it
is not known if there will be or will not be 'holes' in the hierarchy
levels.

3. There are more than 20 dimension attributes that I need to produce
in the actual query that I need in my project. 20 attributes = 4
attributes per Dimension and there are 5 dimensions that form the
entity hierarchy. In the Adventure Works example, the hierarchy is
formed by only 3 dimensions - Product, Subcategory and Category.
Imagine if there are 2 more levels, and from each level there are 4
attributes that I need to report - stuff like CategoryCreatedDate,
CategoryLastModifiedDate, CategoryName, CategoryID (the IDs from OLTP
that is different from the surrogate keys). When I replace all the
crossjoins with the .MEMBERS that you mentioned, I am getting the error
"Query optimizer generated too many subcubes in the query plan."
I guess I must have done something very wrong in the design....

I read it from somewhere that .PROPERTIES("<name>") can actually be
used to return some of these 20 attributes but I also read from Mosha's
blog
http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx
that this usage should be avoided as much as possible.

What is actually the proper way to achieve the reporting needs to
return high number of dimension/hierarchy attributes?

Chris Webb

unread,
Jan 23, 2006, 12:18:03 PM1/23/06
to
Hi Rose,

To answer your questions:
1) No, as far as I know there isn't a way to do this automatically; you'll
need to do this in your MDX...
2)...and here's an example of what I think the best way to do it would be.
Using the Adventure Works scenario:

Select {[Measures].[Internet Sales Amount]} on columns,
{

GENERATE(


descendants([Product].[Product Categories].defaultmember,1, self_and_after)

,
{[Product].[Product Categories].CURRENTMEMBER}
*
IIF(
COUNT(EXISTS([Product].[Subcategory].[Subcategory].MEMBERS,
[Product].[Product Categories].CURRENTMEMBER) AS MYSUBCATS)=0,


[Product].[Subcategory].[All Products],

[MYSUBCATS])
*
IIF(
COUNT(EXISTS([Product].[Product Key].[Product Key].MEMBERS,
[Product].[Product Categories].CURRENTMEMBER) AS MYPRODKEYS)=0,
[Product].[Product Key].[All Products],
[MYPRODKEYS])
)


} on rows
from [Adventure Works]

WHERE ({[Product].[Category].&[4],[Product].[Category].&[5],


[Product].[Category].&[6]})

Basically, I'm using GENERATE to iterate through the set of members on
[Product Categories], and for each member test to see if there are any
members on SubCategory or Product Key that exist with it. If there aren't
then I'm returning the All Member; if there are, then I return the members
that do exist.

3) Sounds like you've found a new bug! I would try reporting this to PSS but
I don't think you'll get it fixed in the short term. It sounds like the
problem might be something to do with the way Reporting Services constructs
its queries (see this thread for more details:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=210628&SiteID=1 ), but
since none of the query examples you've posted in this thread use subcubes
I'm not sure exactly what's going on. Can you post a repro on Adventure
Works? I'll do some more research into this too.

The reason that Mosha says you shouldn't use PROPERTIES in these
circumstances is that it's going to be much less efficient than crossjoining
the attributes. However if you run into bugs when doing this you'll probably
find that PROPERTIES does work fairly well, and isn't that much slower.

Chris

Rose

unread,
Jan 27, 2006, 10:01:33 AM1/27/06
to
Hi Chris,

2) Using GENERATE works for us. Thanks so much for the help. Now, it
is also returning a lot of .[All] members where there are no ' holes'
in the hierarchy. I am thinking that maybe nested IIF should help to
resolve that?
However, I am concerned if that will make the query very inefficient,
as we have a hierarchy of 5-level.

3) The error was returned by AS as that happens when I ran the MDX
directly on AS. I have reworked my cube to move some of these
dimension attributes into Measure Groups.
I also tried to simulate it using Adventure Works by adding 2 more
levels on top of the Product Category, ie. Product Classification and
Product Subclassification. But I am not getting much results.

Rose

Rose

unread,
Jan 29, 2006, 9:33:13 AM1/29/06
to
Hi Chris,

2) Using the GENERATE() you suggested produced rows like this
Internet Sales Amount
Category A Subcategory X All Products (null)
Category A Subcategory Y All Products (null)


Subcategory X Subcategory X All Products (null)

Subcategory Y Subcategory Y All Products (null)
Category B Subcategory Z All Products (null)
Subcategory Z Subcategory Z All Products (null)

Where rows 3,4 and 6 are not desired as the Subcategory level is also
repeated in the Category level.

I have tried with this MDX:
SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS,
{ GENERATE(DESCENDANTS([Product].[Product Categories].DefaultMember, 1,
SELF_AND_AFTER),
{[Product].[Category].CURRENTMEMBER} *
[Product].[Subcategory].CURRENTMEMBER *
[Product].[Product].CURRENTMEMBER) } ON ROWS
FROM [Adventure Works]
WHERE {[Product].[Product Categories].[Category].&[5],
[Product].[Product Categories].[Category].&[6]}

and it is giving the following results:


Internet Sales Amount
Category A All Products All Products (null)
Category A Subcategory X All Products (null)

Category A Subcategory Y All Products (null)


Category B All Products All Products (null)

Category B Subcategory Z All Products (null)

Rows 1 and 4 are not desired, and I am stucked with how to get rid of
them. Could you offer any suggestion please?

Thanks in advance.

darrengosbell

unread,
Jan 30, 2006, 6:43:44 AM1/30/06
to
If I understand what you are after, you can fix this by changing the
parameters on the descendants function. If all you want are the
descendants at a particular level in the hierarchy you can specify the
level you want.


SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS,
{ GENERATE(DESCENDANTS([Product].[Product Categories].DefaultMember,

[Product].[Product Categories].[Subcategory]),


{[Product].[Category].CURRENTMEMBER} *
[Product].[Subcategory].CURRENTMEMBER *
[Product].[Product].CURRENTMEMBER) } ON ROWS
FROM [Adventure Works]
WHERE {[Product].[Product Categories].[Category].&[5],
[Product].[Product Categories].[Category].&[6]}


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1138545193....@g49g2000cwa.googlegroups.com>,
rose...@gmail.com says...

Rose

unread,
Jan 31, 2006, 1:33:04 AM1/31/06
to
I have tried changing the parameter values. Defaultmember with
Distance=1 and SELF_AND_AFTER seems to work for me (more than what I
need in fact). The other combinations return 'less' than what I need.

Take the above example again:


Internet
Sales Amount
Category A All Products All Products (null)
Category A Subcategory X All Products (null)
Category A Subcategory Y All Products (null)
Category B All Products All Products (null)
Category B Subcategory Z All Products (null)

Category C All Products All Products (null)

Subcategory X and Subcategory Y are true descendants of Category A,
although non of them is associated with any Product. As a result,
Category A gives 3 rows in the results.
Subcategory Z is associated with Category B, and again with no
associated Product. As a result, Category B is returning 2 rows.
Category C is a standalone ancestor with no true descendants yet. As a
result, only 1 row is returned.

The desired result is to return the true descendants on rows, AND if
true descendants are not existing, still return a row for the ancestor
(in this case, the 'gaps'/'holes' were filled with [All Products]
members).


Internet
Sales Amount
Category A Subcategory X [All Products] (null)
Category A Subcategory Y [All Products] (null)

Category B Subcategory Z [All Products] (null)

Category C [All Products [All Products] (null)
Category D Subcategory 1 [All Products] (null)
Category D Subcategory 2 Product 1 (null)

darrengosbell

unread,
Feb 5, 2006, 5:56:59 AM2/5/06
to
Sounds like you are dealing with a ragged heirarchy. I don't have one
handy that I can experiment with at the moment, but have you tried
putting a filter over the sets with too many members?

I am thinking of something along the lines of the following pseudo code:

Filter( <..existing set..>
, NOT [Product].[Subcategory].CURRENTMEMBER IS [Product].[Subcategory].
[All Subcategory] OR [Product].
[Subcategory].currentmember.parent.children.count > 0)

Not the most elegant expression, but it might get you closer to the
result you require.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1138689184.8...@g47g2000cwa.googlegroups.com>,
rose...@gmail.com says...

Jørgen Guldmann

unread,
Aug 10, 2006, 5:07:44 AM8/10/06
to
Well i usually get the mdx through a pivot table in excel. but bewarned
the quality of the autogenerated MDX is often quite worthless.

mix something like this into a macro...

Dim pt As PivotTable
Dim pcell As PivotCell
Dim ic As String
Set pcell = ActiveCell.PivotCell

Set pt = pcell.PivotTable
If Not pt.PivotCache.IsConnected Then
pt.PivotCache.MakeConnection
End If
ic = InputBox("Show result in", "|spreadsheetname|", "A1")
Range(ic).FormulaR1C1 = pt.MDX ' <-- MAGIC HAPPENS HERE


*** Sent via Developersdex http://www.developersdex.com ***

Reply all
Reply to author
Forward
0 new messages