SSRS : Filter not working on grouped column

Question

I have created an SSRS Report which is grouped on Date and Time column. Date as Parent Group and Time as Child Group. Also I have extracted date and time from a certain column in my select statement as follows :-

CONVERT(VARCHAR(10),DetectionTime,103) AS  Detection_Date,
CONVERT(TIME(0), DetectionTime) AS Detection_Time

Further I created parameter and found out unique values from the given fields using the following code from this link http://mohamedakb.blogspot.com/2011/08/how-to-get-list-distinct-values-from.html and followed steps as mentioned to remove duplicates from given parameter:-

Public Shared Function RemoveDuplicates(parameter As Parameter) As String()

Dim items As Object() = parameter.Value
System.Array.Sort(items)
Dim k As Integer = 0

For i As Integer = 0 To items.Length - 1
If i > 0 AndAlso items(i).Equals(items(i - 1)) Then
Continue For
End If

items(k) = items(i)
k += 1
Next
Dim unique As [String]() = New [String](k - 1) {}
System.Array.Copy(items, 0, unique, 0, k)

Return unique
End Function

Now as I am trying to apply filter on the date column its not working like no output is being displayed. Also I tried to use other columns apart from grouping ones the filter value is getting applied for those columns but not for grouping columns.Is there any other way or am I missing something ?

@RossBush Yes I have one dummy parameter which draws value from dataset and another main parameter which filters the value using code mentioned above. — Batman Rises
Has the date column that is being filtered been reformatted for display purposes? If that converts the date to a text value, there may be differences in either the conversion formatting, or the conversion date values. if items(i).Equals(items(i - 1)) is comparing non-rounded times, you could easily have no items removed from the array. So, what is the data type of the elements of items()? Are the times in the items rounded? — Laughing Vergil
@LaughingVergil orginal date column is also a text value. I am just using convert function to separate out date and time from this text value like 2017-07-11 07:59:40:935. — Batman Rises
You say its ok, however, when you run 2017-07-11 through your CONVERT function the date value returned,11/07/2017, and does not match the format you indicated above. — Ross Bush

Eneerge Eneerge · Accepted Answer · 2017-10-24T18:58:27

Old question, but I had an issue where filters were not working. I found that by adding the column that I was filtering by to the grouping resolved the issue.

EG: I was trying to filter so that only items that were HasElement = 'Y' were pulled into the result. I had to add "HasElement" to the grouping. Once I did this the filter worked properly.

In addition, when I attempted to "total" the filtered values in a column, the totals did not work. I was totaling in a column each row. To get the totals, I created a new column grouping that was grouped by "1" and by the hasElement column. Now the totals can be calculated properly.

Here's an image that may explain it better.

SSRS : Filter not working on grouped column

1 Answers