Tour de France 2011 – Analysis using ADF DVT Graphs – Part 4 – Spark Charts

The final episode in a series on ADF DVT applied to the Tour de France 2011 results. In this series, I have used many of the ADF DVT Graph components. In this article, I will use the Spark Chart component to integrate condensed data visualizations inside a table with ‘regular’ data.

The result is a table that lists the top 10 of the final overall ranking of the Tour de France, with for each ride in the top 10 two spark charts:

• the positition in the overall standings at the end of each stage
• the gap with Cadel Evans at the end of each stage (needless to say this chart is fairly pointless for Cadel Evans himself)

The resulting page with the table including the two spark charts looks like this:

Note that the Spark Chart for overall rank displays the #1 position at the top and lower rankings towards the bottom.

We can see from the table how for example the number 10 – Peraud – gradually worked his way up in the ranking – while just as gradually getting further behind Cadel Evans. We also see how Voeckler came to the top somewhere midway and then ever so slightly had to let go of that top position. Only the first three days and the last three days saw him behind Evans.

The table is created from a fairly straightforward ViewObject based on the following SQL query:

with final_standing as
( select r.name
  ,      r.team
  ,      r.ridernumber
  ,      r.id rider_id
  ,      s.rank as ranking
  ,      s.gap
  ,      s.timelag
  from   standing s
         join
         rider r
         on (s.rider_id = r.id)
         join
         stage e
         on (s.stage_id = e.id)
         join
         tour t
         on (e.tour_id = t.id)
  where  e.stagesequence = 21
  and    t.year = 2011
  and    s.type_of_standing = 'ITG'
  order
  by     ranking
  )
,  top10 as
( select *
  from   final_standing
  where  rownum < 11
)
select *
from   top10

A second query is used for returning the rider details per stage.

with stage_standings as
( select s.rank as ranking
  ,      s.gap
  ,      s.timelag
  ,      ( select s2.timelag
           from   standing s2
                  join
                  rider r
                  on (s2.rider_id = r.id)
           where  s2.stage_id = s.stage_id
           and    r.ridernumber = 141 -- Cadel Evans
           and    s2.type_of_standing = 'ITG'
         ) cadels_lag
  ,      s.rider_id
  ,      e.stagesequence
  from   standing s
         join
         stage e
         on (s.stage_id = e.id)
         join
         tour t
         on (e.tour_id = t.id)
  where  t.year = 2011
  and    s.type_of_standing = 'ITG'
  order
  by     ranking
  )
select ranking
,      stagesequence
,      gap
,      rider_id
,      3600*extract( hour from (timelag - cadels_lag)) + 60* extract (minute from (timelag - cadels_lag)) + extract (second from (timelag - cadels_lag)) lag_behind_cadel
from   stage_standings

A ViewObject is created for this query too – called RidersRankingPerStageWithCadelLag.

Then, a ViewLink is created between Top10Riders and RidersRankingPerStageWithCadelLag.

The ViewObjects are added to the Application Module’s Data Model:

Now I originally thought I was done. I thought that I could create the Spark Chart based on the details collection available from VO RidersRankingPerStageWithCadelLag through the ViewLink.

Configuring the table:

And running the page:

So now it is only a matter of adding the Spark Chart based on the Details Collection and done. Or so I thought:

select the sparkchart:

and configure it

However: no such luck. The table renders the spark chart for every row – while the details in VO RidersRankingPerStageWithCadelLag are returned only for the current row in the Top10Riders ViewObject. The spark chart would look the same in every row – and be correct for only one of them.

I took another approach – and I am not sure whether it is the best one. However, it works.

I have created a transient attribute for the rankings – of type Object:

I have next had the ViewRowImpl class generated and then I implemented the getter accessor for the Rankings attribute. It uses the ViewLink accessor to create a List of all Ranking values returned from the Detail ViewObject:

This makes the attribute Rankings available to me to use inside the Table to base the Spark Chart on:

Provided of course the table binding is edited correctly:

Now we can run the page and see our little spark chart:

Adding the bar chart for the gap with Cadel Evans at the end of each stage takes an almost similar series of steps:

Add a transient attribute to return the set of lags

Configure the transient attribute with type Object:

Create the getter implementation for this attribute:

Refresh the Data Control:

Update the Table Binding:

Add the column definition with Spark Chart inside, based on the CadelLags attribute:

And run the page:

Note how the green bar indicates the most positive situation a rider has had vs. Cadel Evans. For Andy Schleck, that situation occurred right before the end of the Tour, in the three but last stage. It did not last long however.

Resources

Download JDeveloper 11.1.2 application:TourDeFranceWithSparkCharts .