SSRS: Create Chart

ssrs

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.

Best Answer

Solutions for these kind of things are usually guided by questions like:

  • The actual volumes of data you'll need to process;

  • The number of events and changes you're tracking over time - thus guiding exactly the kind of output you want - e.g. a square plot as per your example of there being always 2 IDs counted between t2 and t3; or a more sawtooth line plot connecting a 2 at t2 with a direct diagonal to t3 where it dropped down to 1.

  • Are you using T-SQL?

Anyway, here's one way I might do it, dependent on that kind of question.

You can use a lead() window function to work out the times between which a given ID was at a given trackingID status, thus:

SELECT
  ID,
  TrackingID,
  TrackingTime AS TrackingTimeStart,
  LEAD(TrackingTime) OVER (PARTITION BY ID ORDER BY TrackingTime) AS TrackingTimeEnd
FROM [Your Table]

Gives:

ID   TrackingID TrackingTimeStart       TrackingTimeEnd
---- ---------- ----------------------- -----------------------
001  10         2017-03-03 09:10:23.240 2017-03-05 10:12:23.240
001  15         2017-03-05 10:12:23.240 2017-03-08 10:12:20.240
001  10         2017-03-08 10:12:20.240 2017-03-08 17:12:23.240
001  20         2017-03-08 17:12:23.240 NULL
002  15         2017-03-04 00:12:23.240 2017-03-06 10:12:23.240
002  10         2017-03-06 10:12:23.240 NULL
003  10         2017-03-06 01:18:23.240 2017-03-08 12:30:23.240
003  50         2017-03-08 12:30:23.240 NULL

(8 row(s) affected)

Then use the results from that with another window function to count up the running total of open events at each given date, thus:

WITH [Start and Ends] AS (
  SELECT
    ID,
    TrackingID,
    TrackingTime AS TrackingTimeStart,
    LEAD(TrackingTime) OVER (PARTITION BY ID ORDER BY TrackingTime) AS TrackingTimeEnd
  FROM [Your Table]
)

SELECT
  TrackingID,
  EventDate,
  sum(EventCount) OVER (PARTITION BY TrackingID ORDER BY EventDate) AS RunningCount
FROM (
  SELECT
    [TrackingID],
    TrackingTimeStart AS EventDate,
    +1 AS EventCount
  FROM
    [Start and Ends]
  UNION ALL
  SELECT
    [TrackingID],
    TrackingTimeEnd AS EventDate,
    -1 AS EventCount
  FROM
    [Start and Ends]
  WHERE
    TrackingTimeEnd is not null
) x

Plotting the output from that for TrackingID 010 gives you:

SSRS screenshot

Is one possible solution. You could even plot each trackingID as a separate series line on the same chart if you wanted. I'm not showing that here, as the other tracking IDs don't have so much data as for 010 in your sample dataset.

I've left you with hopefully some answers, but maybe some further problems for you to solve. Such as if you have a volume of historic data and you want to allow the user to pick a time frame, you need to calculate the starting y value for the line by using that history in some way.

Related Question