Get Existing Function to work to get Visual Totals

Question

with member [Date].[Quarter].[total]

as

Sum(Existing Except([Date].[Quarter].members,[Date].[Quarter].[Q2]))

select [Measures].[x] on 0,

{Except([Date].[Quarter].children,,[Date].[Quarter].[Q2]),[Date].[Quarter].[total]} on 1

from (select [Date].[Year].[2010] on 0 from [Accounting])

Wanted to display

Q1

Q3

Q4

Total

But the total displayed is for all members except Q2. I had used existing in the with member so that it takes only the ones which are there in the rows but it does not quite do so.

I also tried using linkMember

 

Sum(Existing Except(linkMember([Date].[Year].[2010],[Date].[Quarter]),[Date].[Quarter].[Q2])) but even that does not work. How do I get existing to work so that it takes the sum only the visible rows?

Answer 1

you do not need existing in your example

just use any of the queries below:

this query is similar to yours, but is based on adventure works
as you can see you do not need the EXISTING there
also take care wheter you use ".members" on a hierarchy or on a level!

WITH
MEMBER [Date].[Calendar Quarter of Year].[total] AS ( 
Aggregate(Except([Date].[Calendar Quarter of Year].[Calendar Quarter of Year].members, 
		[Date].[Calendar Quarter of Year].&[CY Q2])))
 
SELECT [Measures].[Internet Sales Amount] on 0,
{Except([Date].[Calendar Quarter of Year].[Calendar Quarter of Year].members,
		[Date].[Calendar Quarter of Year].&[CY Q2]),
	[Date].[Calendar Quarter of Year].[total]} on 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2007]

or this one using subselects to achive Visual Totals:

SELECT [Measures].[Internet Sales Amount] on 0,
Hierarchize([Date].[Calendar Quarter of Year].members, POST) on 1
FROM (
	SELECT Except([Date].[Calendar Quarter of Year].[Calendar Quarter of Year].members, 
		[Date].[Calendar Quarter of Year].&[CY Q2]) ON 0 
	FROM [Adventure Works])
WHERE [Date].[Calendar Year].&[2007]

or this one using VisualTotals-Function:

SELECT [Measures].[Internet Sales Amount] on 0,
Hierarchize(
	VisualTotals(Except([Date].[Calendar Quarter of Year].members, 
		[Date].[Calendar Quarter of Year].&[CY Q2]), "Total - *"), POST) on 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2007]

hth,
gerhard

---

- www.pmOne.com -

Answer 2

Thanks Gerhard, the last one worked using VisualTotals worked.