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%

>>

Is this possible in MDX queries

Hi

The cube i have conatins the ProductName , Time as dim and Sales as measure. I need to query from the cube the product name and sales for multiple date ranges. As an example, The I/P shall be in the format

ProductName : A,B

DateRange : 1/1/2004:2/1/2004,1/1/2005:2/1/2005,and so on.

The number of date ranges is not fixed and also is the productname but the date ranges are common across both the Products. I have to query for the above I/P and use SSRS to get the O/P.

Thanking you in advance

regards

saishyam

I am not sure exactly what you are looking for, but here is an example using Adventure Works that list products with sales by total for different date ranges:

WITH

SET [Selected Products] AS

{[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children}

SET [Date Range 2002] AS

{[Date].[Date].[January 1, 2002]:[Date].[Date].[February 1, 2002]}

MEMBER [Date].[Date].[Date Range 2002 Total] AS

Aggregate([Date Range 2002])

SET [Date Range 2003] AS

{[Date].[Date].[January 1, 2003]:[Date].[Date].[February 1, 2003]}

MEMBER [Date].[Date].[Date Range 2003 Total] AS

Aggregate([Date Range 2003])

SET [All Date Range Totals] AS

{[Date].[Date].[Date Range 2002 Total],

[Date].[Date].[Date Range 2003 Total]}

SELECT

{[All Date Range Totals]} ON COLUMNS,

NonEmpty({[Selected Products]},{[All Date Range Totals]}) ON ROWS

FROM

[Adventure Works]

WHERE

([Measures].[Internet Sales Amount])

You can substitute another measure for "Internet Sales Amount" by changing the WHERE clause and get the totals for a different measure.

HTH,

Steve

|||

Hi Steve

Thank you for the response.

The query that you have mentioned conatins the date ranges fixed, in my case the number of date ranges is not fixed. So how will i know , how many Sets i need to create? Is there any way to do that. I hope now my quesition is more clear.

regards

Saishyam

|||

You don't have to define the sets explicitly, you should be able to just do this:

{ { Date1member : Date2member}, { Date3member : Date4member } }

Alternatively, MDX has a function called Union.

sql