Is this possible with a mdx-query

Hi all,

I'm stuck on the next issue:
I have the Measure "Charges" and a Dimension "ComDevice".
I have a pie-chart that has to show me the "Charges" per "Comdevice"(with labels that show me the name of the comdevice)

And now it comes, I don't want to see all the comdevices. I only want to see the comdevices that have the highest charges.
When I reached 80% of the charges, I want so show the remainded charges summed as 1 comdevice named "Remainder"
I already did this with an sql-stored procedure, but we are getting the report-data from a cube now and I'm very new to mdx

Does anyone know if this is possible with MDX and how to do this?

Thanx in advanceyes, you can.
use "TopPercent" function|||sorry for previous answer. (not read carefully)

you can use filter function to generate comdevice set that reached 80% charge, and create a calculate member to get reminder.|||Thanx for the reply and your tip,

I came up with some help at the next query.
-----------------------
With
Set [SimCardPieChart] as
TopPercent({[Buyer].[Company].members} ,80, [Measures].[InvoiceAmount])
Member [Buyer].[Company].[Remainder] as
Aggregate([Buyer].[Company].members - [SimCardPieChart])
select nonempty({[Measures].[InvoiceAmount]})on Columns,
{[SimCardPieChart],[Buyer].[Company].[Remainder]} on Rows
from ["Cube_Name"]
where ( [Seller].[Company].["Buyer"], [Invoice].[Period Code].["PeriodeCode"])

-----------------------

But now I'm getting the next values:
- 80% of the biggest buyers
- the the remaining summed as "Remainder"
- The Total of all the "Measure"

My question now is how to edit the query so that it would give only the toppercent(80%) and The Remainder. Without the total.

Thanx In advance.|||Hi all,

You all can Ignore the previous message. Reporting services ignores the grand total.
So I don't get it in my report.

regards.sql

Is this possible with a MDX-query

Hi all,

I'm stuck on the next issue:
I have the Measure "Charges" and a Dimension "ComDevice".
I have a pie-chart that has to show me the "Charges" per "Comdevice"(with labels that show me the name of the comdevice)

And now it comes, I don't want to see all the comdevices. I only want to see the comdevices that have the highest charges.
When I reached 80% of the charges, I want so show the remainded charges summed as 1 comdevice named "Remainder"
I already did this with an sql-stored procedure, but we are getting the report-data from a cube now and I'm very new to mdx

Does anyone know if this is possible with MDX and how to do this?

Thanx in advance

Here's an Adventure Works query which returns countries with at least 80% of sales, and the remainder:

>>

With
Set [Top80%Customers] as
TopPercent([Customer].[Customer Geography].[All Customers].Children,
80, [Measures].[Internet Sales Amount])
Member [Measures].[SalesPercent] as
[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount],
[Customer].[Customer Geography].[All Customers]),
FORMAT_STRING = "Percent"
Member [Customer].[Customer Geography].[All Customers].[Remainder] as
Aggregate([Customer].[Customer Geography].[All Customers].Children
- [Top80%Customers])
select {[Measures].[Internet Sales Amount],
[Measures].[SalesPercent]} on 0,
{[Top80%Customers],
[Customer].[Customer Geography].[All Customers].[Remainder],
[Customer].[Customer Geography].[All Customers]} on 1
from [Adventure Works]
-
Internet Sales Amount SalesPercent
United States $9,389,789.51 31.98%
Australia $9,061,000.58 30.86%
United Kingdom $3,391,712.21 11.55%
Germany $2,894,312.34 9.86%
Remainder $4,621,862.58 15.74%
All Customers $29,358,677.22 100.00%

>>