February 16, 2015 at 9:03 am
Hi All,
I have a the below query that is being generated by Excel which includes a subcube:
SELECT
NON EMPTY [Channel].[Channel Group - Channel].[Channel Group] ON COLUMNS ,
NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Number - Product].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM (
SELECT ({[Product].[Product Number - Product].[Product Number].&[ABC206], [Product].[Product Number - Product].[Product Number].&[ABC205]})
ON COLUMNS
FROM [Products])
As you can see Excel has created a sub cube to filter the set down to just the two product I want.. Any ideas?
However when I calculated measure to get the count of products that I've selected:
CREATE MEMBER CURRENTCUBE [Measures].[Selected Product] AS
Descendants(Existing [Product].[Product Number - Product].CurrentMember, [Product].[ProductNumber - Product].[ProductNumber]).Count
,VISIBLE = 1;
The result is the total count of all products (there are 1106) instead of only the two in the subcube generated by Excel...
It's been driving me insane so any help is much appreciated!!
Cheers,
Jim.
February 16, 2015 at 9:25 am
JimbobsQL (2/16/2015)
Hi All,I have a the below query that is being generated by Excel which includes a subcube:
SELECT
NON EMPTY [Channel].[Channel Group - Channel].[Channel Group] ON COLUMNS ,
NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Number - Product].[All]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS
FROM (
SELECT ({[Product].[Product Number - Product].[Product Number].&[ABC206], [Product].[Product Number - Product].[Product Number].&[ABC205]})
ON COLUMNS
FROM [Products])
As you can see Excel has created a sub cube to filter the set down to just the two product I want.. Any ideas?
However when I calculated measure to get the count of products that I've selected:
CREATE MEMBER CURRENTCUBE [Measures].[Selected Product] AS
Descendants(Existing [Product].[Product Number - Product].CurrentMember, [Product].[ProductNumber - Product].[ProductNumber]).Count
,VISIBLE = 1;
The result is the total count of all products (there are 1106) instead of only the two in the subcube generated by Excel...
It's been driving me insane so any help is much appreciated!!
Cheers,
Jim.
I believe the problem here is the fact that the "[All]" member is selected on the axis. The calculated measure corresponding to that level would be the total of all products.
February 16, 2015 at 9:29 am
Hi Martin,
Thanks for the reply. That [All] member was put on by Excel as that reference only includes both of the product members in the sub cube.
The calculation I'm doing in the calculated member isn't able to crack into the subcube to see which members have been selected. I think this is where the problem lies but I don't know how to find those members..
Cheers,
Jim.
February 16, 2015 at 9:35 am
JimbobsQL (2/16/2015)
Hi Martin,Thanks for the reply. That [All] member was put on by Excel as that reference only includes both of the product members in the sub cube.
The calculation I'm doing in the calculated member isn't able to crack into the subcube to see which members have been selected. I think this is where the problem lies but I don't know how to find those members..
Cheers,
Jim.
Yeah, pretty interesting that Excel decides to generate the query like that. What happens if you add another dimension (from another dimension) to the query?
February 16, 2015 at 10:00 am
February 17, 2015 at 2:02 am
Ok,
So the answer was that I hadn't created the Named Calculations in the DSV. Once I created the 'real' measures I needed then my original SCOPE statement worked a treat. Looks like I just hadn't read the instructions!!!
It was thanks to Chris Webb's blog found here[/url] that helped resolve the issue.
Cheers,
Jim.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply