Monday, March 26, 2012

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

No comments:

Post a Comment