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
<tbody>
</tbody>
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
| Username | Change Time | Status |
| bob | 2018-05-01 09:00:01.000 | IN |
| bob | 2018-05-01 10:00:03.000 | OUT |
| bob | 2018-05-01 11:05:04.123 | IN |
| bob | 2018-05-01 17:05:05.111 | OUT |
| sue | 2018-05-01 08:59:00.001 | IN |
| sue | 2018-05-01 17:00:00.000 | OUT |
| sue | 2018-05-02 09:00:01.000 | IN |
| sue | 2018-05-02 17:05:00.000 | OUT |
<tbody>
</tbody>
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
