What I had originally wanted was to
see not only the job start times but also
the duration and any jobs that overlap in
time. Since the data field for the Range
chart is simply the start and end time
difference, the graphical representation
of the overlapping jobs is handled automatically
by the report. Figure 2 shows
the data series properties, top and bottom
values, which are the start and end times
of the jobs from the Jobs data set. The
Category field is simply the job name, referenced by [name]. The Server field will
be placed in the Series section of the chart.
I also added 3D elements to the chart, by
right-clicking the range bars in Design view
and selecting 3D Effects.
You might also notice in Figure 2 that
the Tooltip is an expression, indicated by the
value <Expr>. Since I wanted this overlay
report to be interactive and also to conserve
report real estate, I placed some of the details
in the Tooltip item, which will be displayed
when you hover the mouse over it. Listing
4 shows the Tooltip expression used to display
report information. To view the tooltip
Visual Basic code in the report, simply click
the Expression button (the ƒx button) at the
right. I use the Chr function to insert a carriage
return—the equivalent of Chr(13)—in
the tooltip.
The final view, Figure 3 shows the report
after it was executed with two server values
and the run date 9/21/2008. You can see that
two jobs overlap between the two servers.
Hovering the mouse over a job shows the job
name, run date, and the actual duration. I also
determine from the tooltip that both jobs have
UNC in their title, indicating that a network
resource is being used and possible resource
contention that I might want to investigate
further.
Adding Value
to DBA Repository
If you’ve already downloaded and implemented
my DBA Repository solution (see
the Learning Path box online for a complete
list of DBA Repository articles), all you need
to do to implement the overlay report is to
download, load, and deploy the Overlay.rdl
file. The overlay report has already helped
me immensely in my daily tasks, and I hope it
adds value to your own reporting capability.
I’ll keep expanding this solution and plan to
post updates to it if readers are interested.
As always, I welcome your feedback and
enhancements.