April 20, 2005 at 2:10 am
hi all,
how can i get the result set like this from analysis services sales cube through mdx query...
Year on rows, prod category on columns with grand totals
storesales is the measure on rows
Year Food Drink All
1997 2500 3800 6300
1998 4200 2600 6800
All 6700 6400 13100
Nsr
April 20, 2005 at 4:53 am
This is one option
WITH
MEMBER [Product].[All Products].[All] AS '[Product].[All Products].[Drink] + [Product].[All Products].[Food]'
MEMBER [Time].[All] AS '[Time].[1997] + [Time].[1998]'
SELECT
{[Product].[All Products].[Drink], [Product].[All Products].[Food], [Product].[All Products].[All]} ON 0,
{[Time].[1997], [Time].[1998], [Time].[All]} ON 1
FROM
[Sales]
WHERE
[Measures].[Store Sales]
Steve.
April 20, 2005 at 6:42 am
Hi steve,
Thanq for the reply..thats great.. i just need some clarity over here...
1. AS '[Product].[All Products].[Drink]+[Product].[All Products].[Food]'
For this is there any alternative to get the total of the Entire heirarchy
like sum([Product].[All Products])
2. Similarly on Time also as Sum([Time])
3. WHERE [Measures].[Store Sales] - what exactly it does ?
Nsr
April 20, 2005 at 7:44 am
No prob.
If you have an 'All' member in a dim you shouldn't need to do the calculated member (ie use the SUM), you can just use the total in the same set, like {[Product].[Food], ....., [Product].[All Products]} On Rows
Same with the time, I used the calc members only because 1) you had a subset of the prod dim on the cols, and 2 I have 4+ years in my Time dim in Foodmart.
You can use the where clause for your measures if you are presenting only the one measure. Otherwise, you'd normally use a crossjoin (or nonemptycrossjoin) to 'nest' them within eiter your rows or columns (whichever you choose). Sortof like
crossjoin({[Measures].[A], [Measures].}, {[Products].[All Product].CHILDREN}) alternate syntax (i think Msft AS supported only ) is {{[Measures].[A], [Measures].} * {[Products].[All Product].CHILDREN}} i.e. * = crossjoin
Cheers,
Steve.
April 20, 2005 at 7:59 am
Thanq steve ..i got clear with the topic..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply