I have a very specific situation where i have a table that has information about how long a Docking Door has been in a "status". Each row returned from my query has information about the specifications of the status, such as the previous status, new status, time in status, id, etc.
In the report, the tablix is grouped on the location id for the warehouse it takes place, then we do a maximum, average, and minimum number of "status changes". So a count of the ID, filtered for the Max 1, Min 1, and average has no filter. Average is calculated by doing a count(id)/countdistinct(doorid)
For max and minimum groups, they have their own column group grouped by the doorid because the max/avg/min need to be calculated per door, so that
Door 1 10 status changes (max)
door 2 5 status changes
door 3 2 status changes (min)
So then, I run into the following issue: I occasionally ran into the issue if my Doors shared the same number of status updates for max or min, it would create a new column and repeat Maximum Changes... so the report would look like
Maximum changes Maximum changes Average changes Minimum changes
10 10 5.67 2
This is not ideal. So I came up with the solution to change the visibility to
=Previous(Count(Fields!TurnTime.Value)) = Count(Fields!TurnTime.Value)
where true = hidden
this works... until we only have two doors worth of status changes, and then i have no maximum/minimum changes if they share a value.
Odd scenario, but i have no idea how to proceed. RowCount wont work. Any help?
- Edited by TheTurdFerguson Friday, July 26, 2013 9:01 PM
A few questions:
What are the exact row columns you have? For example, which ID fields? DoorID, etc. I'm not sure whether your "id" = LocationID, StatusID, or some other ID.
I believe you are making this too complicated. Try adding the following test dataset:
IF OBJECT_ID('tempdb..#sample_data') IS NOT NULL DROP TABLE #sample_data create table #sample_data ( previous_status varchar(25), new_status varchar(25), status_duration int, location_id int, door_id int ) insert #sample_data values('open','closed',5,1,1),('closed','closed',7,2,1),('closed','open',15,1,1),('open','open',6,2,2),('open','closed',3,1,3), ('open','closed',25,2,3),('open','closed',30,2,3),('closed','open',6,2,3),('closed','open',15,1,3),('open','closed',0,1,4),('closed','open',8,1,4) select * from #sample_data order by location_id, door_id
Then add this Calculated Field in SSRS:
and label this status_change_flag
Create a tablix with a row group on location_id. You don't need any column groupings. Instead, in the tablix column, input this expression:
I don't understand what you want to do for the Max and Min. You have some details missing. Should this be Max/day? Would you please provide sample data?
When I created the above, here is what I got. You will not run into multiple instances of the same data between doors if you follow my approach.
(I just did a SUM on the status_change_flag for the Max column).
Max, min, and average for the doors are the number of times a status change has happened on that door. There are date/time parameters to help you narrow this down.
So if i had three appointments for Door 1, and each had 2 status changes, my statuschange count is 6.
if door 2 only had 3 status changes overall, and those are my two doors, i wanna get 6 as my max, and 3 as my minimum.
- Edited by TheTurdFerguson Wednesday, July 31, 2013 7:48 PM
In addition, your fix for a flag doesnt work, and here's why.
We need a count of each status change with each row's door and location ids in the query as i stated.
My filter i am using is
Previous(Count(Fields!TurnTime.Value)) = Count(Fields!TurnTime.Value)
turntime is a calculated field i use for timetracking. im counting each iteration, so i need to know each iteration as it occurs.
Your calculated field for a status change flag is no different from counting my appointmentid's essentially, since when you change a status, the previous will never be the same as the one before it as you cant go from Active to Active in our system, except in the first row, so that's out the window.