none
Hiding all but one row in a group of a group

    Question

  • 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)

    Makes sense? 

    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?



    Friday, July 26, 2013 8:44 PM

All replies

  • 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:

    =iif(Fields!previous_status.Value<>Fields!new_status.Value,1,0)


    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:

    =count(Fields!location_id.Value)/countdistinct(Fields!door_id.Value)

    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).



    Ryan D

    Saturday, July 27, 2013 1:23 AM
  • 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. 



    Wednesday, July 31, 2013 6:45 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. 

    Wednesday, July 31, 2013 7:48 PM
  • bump
    Thursday, August 01, 2013 1:22 PM
  • bump
    Monday, August 05, 2013 4:13 PM
  • bump
    Tuesday, August 06, 2013 1:41 PM