Hi Guys,
I'm having a bit of a brain strain with this one so I thought I would try and get some help to point me in the right direction.
I have a SQL Database with a table containing a data with user IN and OUT times
My data looks like this in an SQL Query
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Username[/TD]
[TD]Change Time[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 09:00:01.000[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 10:00:03.000[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 11:05:04.123[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 17:05:05.111[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-01 08:59:00.001[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-01 17:00:00.000[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-02 09:00:01.000[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-02 17:05:00.000[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm trying to create a Graph or Chart in Excel (2010) that will display users down the left side and dates / times across the bottom.
A bar for each user running left to right will start at the time when the user is set to IN and finish when the user is set to OUT a bit like a Gantt chart.
I created an SQL view that selects the data I want in SQL Server.
I have created a Data Source in Excel that connects to that view.
Here is where I'm not sure of the best approach.
I have been mucking around with Pivot tables and charts but it keeps trying to summarize my data which I don't want.
I have simply imported the Data as a Table and this worked and when I click Refresh all I see the updates so that's working.
The data is no longer sorted in the order of my SQL Query. Looks to me like it is purely sorted by Date/Time.
Once I have the chart working I'd like to be able to vary the start and finish date and turn users on or off.
This way a manager can zoom in on just the data they want to see.
How can I achieve this in Excel.
Thanks in advance
David
I'm having a bit of a brain strain with this one so I thought I would try and get some help to point me in the right direction.
I have a SQL Database with a table containing a data with user IN and OUT times
My data looks like this in an SQL Query
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Username[/TD]
[TD]Change Time[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 09:00:01.000[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 10:00:03.000[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 11:05:04.123[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD]2018-05-01 17:05:05.111[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-01 08:59:00.001[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-01 17:00:00.000[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-02 09:00:01.000[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD]2018-05-02 17:05:00.000[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I'm trying to create a Graph or Chart in Excel (2010) that will display users down the left side and dates / times across the bottom.
A bar for each user running left to right will start at the time when the user is set to IN and finish when the user is set to OUT a bit like a Gantt chart.
I created an SQL view that selects the data I want in SQL Server.
I have created a Data Source in Excel that connects to that view.
Here is where I'm not sure of the best approach.
I have been mucking around with Pivot tables and charts but it keeps trying to summarize my data which I don't want.
I have simply imported the Data as a Table and this worked and when I click Refresh all I see the updates so that's working.
The data is no longer sorted in the order of my SQL Query. Looks to me like it is purely sorted by Date/Time.
Once I have the chart working I'd like to be able to vary the start and finish date and turn users on or off.
This way a manager can zoom in on just the data they want to see.
How can I achieve this in Excel.
Thanks in advance
David
