Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"gr
Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).
Hi Maria,
Nice to see a well articulated problem for a change 
What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.
You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:
In a textbox inside the list I would expect to see 1. This is because you are groupping by this field
In a textbox outside the list I'd expect to see 23
|||Adam - thank you so much for responding.
I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.
-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.
-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.
It is very very strange as it defies all logic for me!
|||Can you elaborate further please. Can you please post the following:
the Group expression for your list control
the filter applied to the list
the textbox expression that displays the incorrect result
|||Hi Adam,
(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value
(2) The filter applied to the list is the following:
=sum(Fields!CMCM.Value) <= =-3.0
(3) The textbox expression for the field that displays the incorrect result:
=CountDistinct(Fields!ACCT_UNIT.Value,"list4")
Thanks!
Maria
|||
Ok I figured something out...
If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.
|||Why not define it at both levels?|||I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.
I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?
