Last post Jul 11, 2011 10:57 PM by You-Hu Fu
Jul 05, 2011 03:12 PM|aykutucar|LINK
I need to aggregate a column (Count) by looking at another column (Percentage).
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?
Jul 05, 2011 03:25 PM|Steelymar|LINK
declare @tbl as table(Percentage int, "Count" int)
insert into @tbl
select 50,15 union all
select 60,30 union all
where Percentage = (select max(Percentage) from @tbl)
Jul 05, 2011 03:57 PM|aykutucar|LINK
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.
Jul 07, 2011 05:23 AM|You-Hu Fu|LINK
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.
Jul 07, 2011 11:14 AM|aykutucar|LINK
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.
Jul 11, 2011 10:57 PM|You-Hu Fu|LINK
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.