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!
>Solution :
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.