February 27, 2011 at 5:49 pm
Ladies and Gents,
I am having some trouble getting something out of my cubes so i would like some help please on the syntax
I want to have 2 dimensions on a measure,
but i want to exclude certain tuples from one of the dimensions.
i think i need to do a crossjoin, and an except, but i am having trouble getting the syntax correct.
Any help is much appreciated. I have looked at books online, and can get the crossjoin going, but the except bit is causing me grief.
February 27, 2011 at 7:37 pm
What have you been trying ?
EXCEPT is conceptually quite simple - give me a set of ... everything in one set except for those members in a second set.
e.g. (from Books OnLine
//This query shows the quantity of orders for all products,
//with the exception of Components, which are not
//sold.
SELECT
[Date].[Month of Year].Children ON COLUMNS,
Except
([Product].[Product Categories].[All].Children ,
{[Product].[Product Categories].[Components]}
) ON ROWS
FROM
[Adventure Works]
WHERE
([Measures].[Order Quantity])
February 27, 2011 at 8:54 pm
happycat59 (2/27/2011)
What have you been trying ?EXCEPT is conceptually quite simple - give me a set of ... everything in one set except for those members in a second set.
e.g. (from Books OnLine
//This query shows the quantity of orders for all products,
//with the exception of Components, which are not
//sold.
SELECT
[Date].[Month of Year].Children ON COLUMNS,
Except
([Product].[Product Categories].[All].Children ,
{[Product].[Product Categories].[Components]}
) ON ROWS
FROM
[Adventure Works]
WHERE
([Measures].[Order Quantity])
GAR!.. brain cell loss moment. i was typing the syntax in incorrectly. It's the first time i've had a go at this, and i was using BOL, but i think i just needed to walk away from it for a bit, now bleedingly obvious what i did wrong. My tuples were not entered correctly.
Thanks for the reply, and my bad.!
cheers
Adam
March 1, 2011 at 1:11 am
Yeah - I think we all have days like that. We just hope that they don't happen too often
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy