locked
locate a row based on a column value RRS feed

  • Question

  • User529902532 posted

    Hi,

    I need to aggregate a column (Count)  by looking at another column (Percentage).

    Percentage       Count

    50%                 15

    60%                 30

    70%                 20

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

    70% (Max)        20

    As the aggregates, I look for the max of Percentage and trying to get the Count value on that row.

    Is there a way to do this?

    Thanks.

    Tuesday, July 5, 2011 3:12 PM

Answers

  • User1471008070 posted

    Okay, now you can get the max value, you still want to get the acount value which belongs to the max value, right? If so, I think you can create a dataset which is used to retrieve acount value corresponding the max value. Then create a parameter with hiden property whose value come from this dataset, then at the footer of table, you can utilize this parameter to display the acount value 20.

    If you have any question about the steps, please feel free to let us know.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 11, 2011 10:57 PM

All replies

  • User191633014 posted

    use:

    declare @tbl as table(Percentage int,  "Count" int)
    insert into @tbl 
    select 50,15 union all
    select 60,30 union all
    select 70,20
    
    select [count] 
    from @tbl 
    where Percentage = (select max(Percentage) from @tbl)
    Tuesday, July 5, 2011 3:25 PM
  • User529902532 posted

    Thanks for the reply.

    But I have an mdx query, not a sql.

    And I wanted to know if this is possible in tablix stucture.

    Regards.

    Tuesday, July 5, 2011 3:57 PM
  • User1471008070 posted

    Hi,

    Based on your information, I would recommend you achieve the percentage and Max value on report level, you can add another column to calculate the percentage the expression like this sample =Fields!OrderQuantity.Value/Fields!SalesAmount.Value

    If you want to get the max value, you can type in the expression =MAX(Fields!OrderQuantity.Value/Fields!SalesAmount.Value) at the footer row of the tablix

    If I misunderstand you, please feel free to let me know.

    Thursday, July 7, 2011 5:23 AM
  • User529902532 posted

    I guess I couldn't describe what I need exactly.

    All I need to do is, get the row with maximum percentage and get some other column values from that row.

    In the example above, I can find the maximum of percentage with =Max(Fields!Percentage) which is 70%

    But in the footer, I also have to display the Count value that is on the same row with that max percentage which is 20.

    I don't know how to get that value.

    Thanks for your help.

    Thursday, July 7, 2011 11:14 AM
  • User1471008070 posted

    Okay, now you can get the max value, you still want to get the acount value which belongs to the max value, right? If so, I think you can create a dataset which is used to retrieve acount value corresponding the max value. Then create a parameter with hiden property whose value come from this dataset, then at the footer of table, you can utilize this parameter to display the acount value 20.

    If you have any question about the steps, please feel free to let us know.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 11, 2011 10:57 PM