SSRS How to access the current value within a list control?
- by Dale Burrell
In SQL Server Reporting Services I have a report which has a list control which groups on currency. Within the list control I display the detailed rows of all records filtered to those with a value = £500. i.e. the top earners.
However for each row I need to calculate the percentage of its amount over the total of the entire dataset.
Because I am filtering it I can't use Sum(Fields!Amount.Value) as that only sums the data after filtering, so I am trying a conditional sum over the entire dataset, but am struggling with the correct condition e.g
=100.00*Fields!Amount.Value/Sum((IIf(Fields!Currency.Value = "£", Fields!Amount.Value, CDec(0))),"DataSet")
So where the hardcoded currency symbol is I need to access the current value of currency for the list control, but because my sum is scoped at dataset level any field access is dataset level. Ideally I'd like something like the following, otherwise any other ideas on how to solve this problem.
=100.00*Fields!Amount.Value/Sum((IIf(Fields!Currency.Value = myListControl.Value, Fields!Amount.Value, CDec(0))),"DataSet")
In fact, thinking about it, it would work if I just could access the row level data at that point, but how to do that when its at dataset scope within the sum statement?
Hope that makes sense, any help appreciated.
