I want to create a chart in SSRS where the user can input a time range and maybe selects the wanted TrackingIDs for display.
I have a table which has three main coloumns:
- ID,
- TrackingID and
- TrackingTime
For each ID are TrackingIDs saved with their corresponding TrackingTime. That means that there are multiple rows with the same ID but different TrackingIDs.
| ID | TrackingID | TrackingTime |....
|--------|----------------|-----------------------|
| 001 | 10 |2017-03-08 10:12:20.240|
| 003 | 50 |2017-03-08 12:30:23.240|
| 001 | 10 |2017-03-03 09:10:23.240|
| 002 | 10 |2017-03-06 10:12:23.240|
| 001 | 15 |2017-03-05 10:12:23.240|
| 001 | 20 |2017-03-08 17:12:23.240|
| 002 | 15 |2017-03-04 00:12:23.240|
| 003 | 10 |2017-03-06 01:18:23.240|
....
The user than receives a chart where the sum of each TrackingID is displayed over time. Out of this chart the user can read the change of TrackingIDs over time.
Important is that only the last given TrackingID for an ID is added to the sum of one kind of TrackingID.
I think I have to split up the time range in intervals. Look in those intervals for the last given TrackingID for an ID. Then I have to sum the TrackingIDs for each interval.
Example:
^ number of TrackingIDs (shown only for TrackingID 10)
|
| ID 001 substracted as it got newer TrackingID (eg.15)
| /
5| ID 002 added /
4| ID 001 added / /
3| / / /
2| / ____/____ /
1| ___/_____| |___/____
0|___| | |
----|---------|---------|---------------------> time [datetime]
| | |
t1 t2 t3
The line chart shows only the result for TrackingID 10 for ID 001 and 002.
At t1 the ID 001 has the TrackingID 10 as it's latest given TrackingID.
- AT t1 total of TrackingIDs = 1
At t2 the ID 002 has the TrackingID 10 as it's latest given TrackingID.
- AT t2 total of TrackingIDs = 2
At t3 the ID 001 has another TrackingID than 10 as it's latest given TrackingID.
- AT t3 total of TrackingIDs = 1
I'm sorry that I can't provide any further research I have done, as I just came up with nothing really.