June 12, 2015 at 4:59 pm
Can I add a roll-up aggregete in MDX query (not the customized roll-up in cube design).
For example, using Adventure Works DW 2008R2 database, I ran the following query
select [Measures].[Internet Sales Amount] on columns,
[Product].[Product Line].Children on rows
from [Adventure Works]
and have the result
//Internet Sales Amount
//Accessory$604,053.30
//Components(null)
//Mountain$10,251,183.52
//Road$14,624,108.58
//Touring$3,879,331.82
I want to roll up the numbers to Accessory and NonAccessory. The only way I have is:
With MEMBER Measures.Accessory AS
sum([Product].[Product Line].&, [Measures].[Internet Sales Amount])
Member Measures.NonAccessory AS
sum(Except(
[Product].[Product Line].Children
, [Product].[Product Line].&
), [Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount], Measures.Accessory, Measures.NonAccessory} on columns
from [Adventure Works]
and the result is
//Internet Sales AmountAccessoryNonAccessory
//$29,358,677.22$604,053.30$28,754,623.92
Is it possible to have a result like
//MainProductLine Internet Sales Amount
//AccessoryAccessory $604,053.30
//ComponentsNonAccessory(null)
//MountainNonAccessory$10,251,183.52
//RoadNonAccessory$14,624,108.58
//TouringNonAccessory$3,879,331.82
In other words, is it possible to add a rollup on the fly without changing the cube design?
June 15, 2015 at 5:28 am
This'll do it:
WITH MEMBER [Accessories]
AS
CASE WHEN [Product].[Product Line].CURRENTMEMBER.MEMBERVALUE = 'Accessory' THEN "Accessory"
ELSE "Non Accessory"
END
SELECT
{[Accessories],[Measures].[Internet Sales Amount]} ON 0,
[Product].[Product Line].[Product Line] ON 1
FROM
[Adventure Works]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply