I am trying to create a row that contains the sum of all the values in
my query. My query looks like this

with

//Columns
set[main] as
'{[Measures].[cGrossSales],
[Measures].[cPYGrossSales]}'

//lists all customers
set [rowfilter] as
'FILTER({[Customer].[Chain].[lCustNbr].Members},not isempty(
[Measures].[cGrossSales]))'

select
{[main]}
on columns,

{[rowfilter]}
on rows

FROM CusItmWk_Dev

WHERE([Time_Partial].[All Time_Partial].[2006].[Qtr2 - 2006].[Period 05
- 2006].[Week 18 - 2006],
[Customer].[Type].[sType].&[VHVW])



------------
My result looks like this
cGrossSales cPYGrossSales
customer1 500 500
customer2 500 500


What I would like to do is Sum all of the customers values into a row
like thisthe row header is not so important as the values)

cGrossSales cPYGrossSales
customer1 500 500
customer2 500 500
total 1000 1000


How can I accomplish this? I do not completely understand MDX yet, but
I am trying very hard.

Try VisualTotals() - like in this Adventure Works query:

VisualTotals({[Customer].[Customer].[All Customers],
[Customer].[Customer].&[18740], [Customer].[Customer].&[26837]},
"Partial Total - *")
select {[Measures].[Internet Order Quantity]} on 0,
[CustSet] on 1
from [Adventure Works]
-------------------------------------------------------
Internet Order Quantity
Partial Total - All Customers 5
Adam Hill 3
Albert Martin 2

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

You could do this by creating a calculated member that aggregates the
'rowfilter' set and then union that member onto set on the row axis.

eg.


//Columns
set[main] as
'{[Measures].[cGrossSales],
[Measures].[cPYGrossSales]}'

//lists all customers
set [rowfilter] as
'FILTER({[Customer].[Chain].[lCustNbr].Members},not isempty(
[Measures].[cGrossSales]))'

// calculate total customers
member [Customer].[Chain].[total] as 'AGGREGATE(rowfilter)'

select
{[main]} on columns,

UNION({[rowfilter]},{[Customer].[Chain].[total]})on rows

FROM CusItmWk_Dev

WHERE([Time_Partial].[All Time_Partial].[2006].[Qtr2 - 2006].[Period 05
- 2006].[Week 18 - 2006],
[Customer].[Type].[sType].&[VHVW])

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell


In article <1147443569.831871.227870 (AT) d71g2000cwd (DOT) googlegroups.com>,
rush_edward (AT) eddierush (DOT) net says...

Thanks guys for all of your input. Here is what I did.
....
set[rowfilter]
as
'(FILTER({[Customer].[Chain].[lCustNbr].Members}, not isempty(
[Measures].[cGrossSales])))'
member [Customer].[Chain].[Total] as 'sum({rowfilter})'
....
{[rowfilter], [Customer].[Chain].[Total]} ON ROWS