How can I limit the amount of rows returned in my report?

Question

I am building a report in Tableau Desktop that shows user time logs. It only has three rows: LastName, StartTime, and EndTime.

The report is working, but it's very long because it shows each time value for each user...so Bob might have 90 different StartTime and EndTime entries.

Is there a way to only show the very first StartTime and EndTime for each user, but also add a button or control that, when clicked, expands to show ALL the times for that user?

Thanks!

Answer 1

First duplicate your worksheet (tab). Name one worksheet something like Summary and the other something like Detail — by right clicking on the tab to rename.

Then edit your Summary worksheet and right click on the StartTime pill on whatever shelf you’ve placed it on (probably the Rows shelf). From the context menu, select the MIN() aggregation function from the Measure submenu. This changes your viz from using StartTime as a dimension to using it as a Measure. By default, Tableau will make the MIN(StartTime) measure continuous, green colored, causing values to displayed along an axis. If you prefer to have a column view, simply use the right click menu again to change the field from continuous to discrete (blue).

Do the same for the StopTime pill.

Now you should have one row per user, showing simply the first start time and first stop time for that user. That should be a quick high level summary.

For extra credit, you could add fields to your summary to show the the number of sessions, Count(sessions), and the last start and last stop time. Or with a simple calculated field, the Avg session duration

For your detail sheet, you are going to want to filter it to a single user or a small group of users. There are several ways to do this, the simplest is to add LastName to the filter shelf and then click show Filter to make it interactive. Alternatively, you can read about Filter Actions and/or Viz in Tooltips — both of which allow you to select rows in one sheet, say your Summary sheet, and then automatically filter and display the corresponding detail sheet.

That way you can easily drill down to see more detail for selected items. These are very common and useful techniques in Tableau.

Answer 2

If you are using SQL Server I would suggest you make a stored procedure (with a parameter p). Something like

CREATE PROCEDURE [dbo].[ExtractTimes]

    @p                  INT

AS
BEGIN
    SET NOCOUNT ON

    ------------------------------------------------------
    -- OUTPUT
    ------------------------------------------------------
    IF @p=1
    BEGIN
        --------------------------------------------------------------------------------------------
        -- SELECT only top values [@p=1]
        --------------------------------------------------------------------------------------------
        SELECT LastName, StartTime, EndTime
        FROM (
            SELECT LastName, StartTime, EndTime, ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY StartTime DESC) AS Rn
            FROM TableName
        ) X
        WHERE X.Rn=1
    
    END
    ELSE IF @p=2
    BEGIN
        --------------------------------------------------------------------------------------------
        -- SELECT all rows [@p=2]
        --------------------------------------------------------------------------------------------
        SELECT LastName, StartTime, EndTime
        FROM TableName
    END

END

and execute it passing a parameter p selected by the user that can be 1 or 2 depending on if you want to see all the rows or just the last for each name.

Every time the user changes the parameter, Tableau will execute the SP again. Please don't forget to declare SET NOCOUNT ON or it won't work with Tableau.