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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment