Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: 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

  2. #2
    Join Date
    May 2006
    Posts
    16
    yes, you can.
    use "TopPercent" function

  3. #3
    Join Date
    May 2006
    Posts
    16
    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.

  4. #4
    Join Date
    Nov 2005
    Posts
    3
    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.
    Last edited by DoubleMo7; 09-29-06 at 06:31.

  5. #5
    Join Date
    Nov 2005
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •