Re: Error when using calculated members in filters area

#1

The two filters are different in BIDS.
The filter into the pivot table area generates a WHERE clause in the
MDX query.
The filter outside the pivot table area (where you can filter a
dimension selection Dimension/Hierarchy/Operator/Filter Expression)
generates a SUBCUBE statement.

I am not sure if this is a limitation of the SUBCUBE MDX statement or
of the BIDS user interface.
Anyway, Excel 2007 works well and it uses the WHERE condition in
generated MDX query when you select the calculated member into the
Report Filter area,

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

SHW wrote:

Quote:

In AdventureWorks database, I cretaed a calculated member as following:

CREATE MEMBER CURRENTCUBE.[Product].[Product
Categories].[Subcategory].&[26].Product1
AS [Product].[Product Categories].[Product].&[483],
VISIBLE = 1 ;

Product1 is equal to Accessories-->Bike Racks-->Hitch Rack - 4-Bike. When
use the cube browser tab to look at data, I use the top most filter section
to filter on product categories hierarchy. If I choose [Hitch Rack - 4-Bike],
I see the numbers for this product. But if I choose Product1 from the list, I
get the following message:

"Unable to apply filter. A set has been encounterd that cannot contain
calculated members."

This happens for all calulated members regardless of hierarchy and dimension
used. However, if you use the filter in the lower part of the screen, it
works fine.

If I write a query using SQL Server Management Studio, the numbers come back
alright.

select [Measures].[Reseller Extended Amount] on columns
from [Adventure Works]
where ([Product].[Product Categories].[Subcategory].&[26].[Product1])

This query works fine.

It is just the BIDS cube browser and Excel that throw the error.
Unfortunately my front end tool is Excel and I have to get this to work with
Excel.

Any ideas?

Thanks,

SHW

#2

I tried this with Excel 2007 RTM and I get a WHERE condition (I traced
it with Profiler).
Now it would be interesting to know when Excel 2007 uses WHERE and when
it uses SUBCUBE.
I did a fast search and I haven't found an answer to this question...
Did someone have an explanation?

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

SHW wrote:

Quote:

Thanks for your reply. Excel 2007 should be using the SUBCUBE statement since
I get the exact same error message. If you write a subcube statement and put
a calculated member in the where clause you get the exact same message.

Did you try this in Excel? You mentioned that it works in Excel.

Thanks,

SHW

"Marco Russo" wrote:

The two filters are different in BIDS.
The filter into the pivot table area generates a WHERE clause in the
MDX query.
The filter outside the pivot table area (where you can filter a
dimension selection Dimension/Hierarchy/Operator/Filter Expression)
generates a SUBCUBE statement.

I am not sure if this is a limitation of the SUBCUBE MDX statement or
of the BIDS user interface.
Anyway, Excel 2007 works well and it uses the WHERE condition in
generated MDX query when you select the calculated member into the
Report Filter area,

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

SHW wrote:
In AdventureWorks database, I cretaed a calculated member as following:

CREATE MEMBER CURRENTCUBE.[Product].[Product
Categories].[Subcategory].&[26].Product1
AS [Product].[Product Categories].[Product].&[483],
VISIBLE = 1 ;

Product1 is equal to Accessories-->Bike Racks-->Hitch Rack - 4-Bike. When
use the cube browser tab to look at data, I use the top most filter section
to filter on product categories hierarchy. If I choose [Hitch Rack - 4-Bike],
I see the numbers for this product. But if I choose Product1 from the list, I
get the following message:

"Unable to apply filter. A set has been encounterd that cannot contain
calculated members."

This happens for all calulated members regardless of hierarchy and dimension
used. However, if you use the filter in the lower part of the screen, it
works fine.

If I write a query using SQL Server Management Studio, the numbers come back
alright.

select [Measures].[Reseller Extended Amount] on columns
from [Adventure Works]
where ([Product].[Product Categories].[Subcategory].&[26].[Product1])

This query works fine.

It is just the BIDS cube browser and Excel that throw the error.
Unfortunately my front end tool is Excel and I have to get this to work with
Excel.

Any ideas?

Thanks,

SHW

#3

I have this doubt: early betas of Excel 2007 used WHERE... then in Beta
2 timeframe they used SUBCUBE... but may be they changed another time
to WHERE in RTM.
I have no platform with Excel 2007 Beta 2 to test... what exact version
of Excel are you using to test?

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Marco Russo

Marco Russo wrote:

Quote:

I tried this with Excel 2007 RTM and I get a WHERE condition (I traced
it with Profiler).
Now it would be interesting to know when Excel 2007 uses WHERE and when
it uses SUBCUBE.
I did a fast search and I haven't found an answer to this question...
Did someone have an explanation?

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

SHW wrote:
Thanks for your reply. Excel 2007 should be using the SUBCUBE statement since
I get the exact same error message. If you write a subcube statement and put
a calculated member in the where clause you get the exact same message.

Did you try this in Excel? You mentioned that it works in Excel.

Thanks,

SHW

"Marco Russo" wrote:

The two filters are different in BIDS.
The filter into the pivot table area generates a WHERE clause in the
MDX query.
The filter outside the pivot table area (where you can filter a
dimension selection Dimension/Hierarchy/Operator/Filter Expression)
generates a SUBCUBE statement.

I am not sure if this is a limitation of the SUBCUBE MDX statement or
of the BIDS user interface.
Anyway, Excel 2007 works well and it uses the WHERE condition in
generated MDX query when you select the calculated member into the
Report Filter area,

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

SHW wrote:
In AdventureWorks database, I cretaed a calculated member as following:

CREATE MEMBER CURRENTCUBE.[Product].[Product
Categories].[Subcategory].&[26].Product1
AS [Product].[Product Categories].[Product].&[483],
VISIBLE = 1 ;

Product1 is equal to Accessories-->Bike Racks-->Hitch Rack - 4-Bike. When
use the cube browser tab to look at data, I use the top most filter section
to filter on product categories hierarchy. If I choose [Hitch Rack - 4-Bike],
I see the numbers for this product. But if I choose Product1 from the list, I
get the following message:

"Unable to apply filter. A set has been encounterd that cannot contain
calculated members."

This happens for all calulated members regardless of hierarchy and dimension
used. However, if you use the filter in the lower part of the screen, it
works fine.

If I write a query using SQL Server Management Studio, the numbers come back
alright.

select [Measures].[Reseller Extended Amount] on columns
from [Adventure Works]
where ([Product].[Product Categories].[Subcategory].&[26].[Product1])

This query works fine.

It is just the BIDS cube browser and Excel that throw the error.
Unfortunately my front end tool is Excel and I have to get this to work with
Excel.

Any ideas?

Thanks,

SHW

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

Re: Error when using calculated members in filters area

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

Re: Error when using calculated members in filters area - 12-07-2006 , 02:55 AM

Re: Error when using calculated members in filters area - 12-08-2006 , 10:30 AM

Re: Error when using calculated members in filters area - 12-08-2006 , 10:33 AM