Help with Charting My Data --

[DavidM]

Hello - I have a linked Excel chart to SQL Server that runs a query
displaying various downtime information for our servers. I would like to
display a chart that shows the percentage of "Uptime" for each server per
month/year.

The percentage is calculated by the fields (TotalMinsDown /
TotalExpectedMinsUp) * 100.

I have the data presented below in Excel -- how to I chart? I can't seem to
get it to come out at all like I want it.

I would like to graph this by Month Year.

I assume I need some sort of field to create the percentage prior to
graphing.

I have my query saved within Excel and my Excel file is saved. Can I easily
re-run the query each time I want new information rather than rebuilding?

Any help would be appreciated:

DB Year ProcessMonth TotalExpectedMinsUp TotalMinsDown
SYS 2005 JAN 1320 0
SYS2 2005 JAN 1080 0
SYS3 2005 JAN 1125 0
SYS4 2005 JAN 1200 1200
SYS5 2005 JAN 1320 0
SYS6 2005 JAN 991 0
SYS7 2005 JAN 1320 0
SYS8 2005 JAN 1260 0
SYS 2004 JUL 8490 10
SYS2 2004 JUL 7860 0
SYS3 2004 JUL 8055 0
SYS4 2004 JUL 8640 8640
SYS5 2004 JUL 8220 1
SYS6 2004 JUL 7861 0
SYS7 2004 JUL 8220 0
SYS8 2004 JUL 8310 55
SYS 2004 AUG 23460 8
SYS2 2004 AUG 22020 0
SYS3 2004 AUG 22455 174
SYS4 2004 AUG 24210 24210
SYS5 2004 AUG 22920 34
SYS6 2004 AUG 21665 97
SYS7 2004 AUG 23100 109
SYS8 2004 AUG 23190 227
SYS 2004 SEP 22905 218
SYS2 2004 SEP 21480 154
SYS3 2004 SEP 21900 235
SYS4 2004 SEP 23610 23610
SYS5 2004 SEP 22320 147
SYS6 2004 SEP 21244 432
SYS7 2004 SEP 22440 167
SYS8 2004 SEP 22590 163
SYS 2004 OCT 23430 5
SYS2 2004 OCT 21780 8
SYS3 2004 OCT 22305 29
SYS4 2004 OCT 23955 19319
SYS5 2004 OCT 22890 40
SYS6 2004 OCT 21485 61
SYS7 2004 OCT 22980 17
SYS8 2004 OCT 23070 607
SYS 2004 NOV 22890 0
SYS2 2004 NOV 21480 0
SYS3 2004 NOV 21900 43
SYS4 2004 NOV 23610 19406
SYS5 2004 NOV 22260 0
SYS6 2004 NOV 21244 50
SYS7 2004 NOV 22440 31
SYS8 2004 NOV 22590 24
SYS 2004 DEC 23715 191
SYS2 2004 DEC 22260 166
SYS3 2004 DEC 22740 275
SYS4 2004 DEC 24465 20286
SYS5 2004 DEC 23130 192
SYS6 2004 DEC 22054 216
SYS7 2004 DEC 23220 39
SYS8 2004 DEC 23400 188

 

[John Mansfield]

David,

You could do this in a number of ways. Here are are couple of suggestions:

(1) Discard July 2004 and January 2005 because they are not complete
months. Starting in Cell A1, set your data up like below. The % uptime is
calculated as

1 - (Down Time / EU Time)

Col A Col B Col C Col D Col E
DB Month % Uptime EU Time Down Time
SYS1 08/04 100% 23,460 8
09/04 99% 22,905 218
10/04 100% 23,430 5
11/04 100% 22,890 0
12/04 99% 23,715 191

SYS2 08/04 100% 22,020 0
09/04 99% 21,480 154
10/04 100% 21,780 8
11/04 100% 21,480 0
12/04 99% 22,260 166

SYS3 08/04 99% 22,455 174
09/04 99% 21,900 235
10/04 100% 22,305 29
11/04 100% 21,900 43
12/04 99% 22,740 275

SYS4 08/04 0% 24,210 24,210
09/04 0% 23,610 23,610
10/04 19% 23,955 19,319
11/04 18% 23,610 19,406
12/04 17% 24,465 20,286

SYS5 08/04 100% 22,920 34
09/04 99% 22,320 147
10/04 100% 22,890 40
11/04 100% 22,260 0
12/04 99% 23,130 192

SYS6 08/04 100% 21,665 97
09/04 98% 21,244 432
10/04 100% 21,485 61
11/04 100% 21,244 50
12/04 99% 22,054 216

SYS7 08/04 100% 23,100 109
09/04 99% 22,440 167
10/04 100% 22,980 17
11/04 100% 22,440 31
12/04 100% 23,220 39

SYS8 08/04 99% 23,190 227
09/04 99% 22,590 163
10/04 97% 23,070 607
11/04 100% 22,590 24
12/04 99% 23,400 188

Due to the difference in down time for system 4 vs. the rest of the group,
you might want to set up individual charts for each system using the Column -
Clustered Column Chart option of the chart wizard. Use columns A - C as your
data source (do not use columns D and E - they are for calculation purposes
only). You can also adjust the X-axis scaling to make the percentage change
variation stand out more.

(2) You can use a pivot chart and manipulate the chart data and formatting
based on setting your data up like this:

DB Month Up Down % Up
SYS1 08/04 23,460 8 100.0%
SYS1 09/04 22,905 218 99.0%
SYS1 10/04 23,430 5 100.0%
SYS1 11/04 22,890 0 100.0%
SYS1 12/04 23,715 191 99.2%
SYS2 08/04 22,020 0 100.0%
SYS2 09/04 21,480 154 99.3%
SYS2 10/04 21,780 8 100.0%
SYS2 11/04 21,480 0 100.0%
SYS2 12/04 22,260 166 99.3%
SYS3 08/04 22,455 174 99.2%
SYS3 09/04 21,900 235 98.9%
SYS3 10/04 22,305 29 99.9%
SYS3 11/04 21,900 43 99.8%
SYS3 12/04 22,740 275 98.8%
SYS4 08/04 24,210 24,210 0.0%
SYS4 09/04 23,610 23,610 0.0%
SYS4 10/04 23,955 19,319 19.4%
SYS4 11/04 23,610 19,406 17.8%
SYS4 12/04 24,465 20,286 17.1%
SYS5 08/04 22,920 34 99.9%
SYS5 09/04 22,320 147 99.3%
SYS5 10/04 22,890 40 99.8%
SYS5 11/04 22,260 0 100.0%
SYS5 12/04 23,130 192 99.2%
SYS6 08/04 21,665 97 99.6%
SYS6 09/04 21,244 432 98.0%
SYS6 10/04 21,485 61 99.7%
SYS6 11/04 21,244 50 99.8%
SYS6 12/04 22,054 216 99.0%
SYS7 08/04 23,100 109 99.5%
SYS7 09/04 22,440 167 99.3%
SYS7 10/04 22,980 17 99.9%
SYS7 11/04 22,440 31 99.9%
SYS7 12/04 23,220 39 99.8%
SYS8 08/04 23,190 227 99.0%
SYS8 09/04 22,590 163 99.3%
SYS8 10/04 23,070 607 97.4%
SYS8 11/04 22,590 24 99.9%
SYS8 12/04 23,400 188 99.2%

There are any number of options that you can choose with the pivot chart tool.

 

[DavidM]

Thanks for the quick reply, John. I've printed out your message and will
look into your suggestions.

Let me ask you another related question --

I currently have link to SQL Server to display the information in Excel in
raw format. Can Excel happily read the data and chart it or should I modify
my query to only display the exact information that I'm trying to chart?

Also, I noticed when I make a chart, I have to drag the columns/rows that I
want charted. Since the amount of data/rows changes, is this the only way I
can select all the data with charts?

I guess what I'm trying to achieve is having a linked excel file with a
query that my boss can open up and execute and it will display a graph. I
really don't want anyone having to select rows of data, etc.

Does any of this make sense?

I messed around with the Pivot table option in Excel. It looks really cool.
I'm just not sure if I need to display the data dynamically using the
controls. I jsut need a chart by Month, Quarter, and Year.

Basically, I would like to see the uptime percentage for all our systems.
Our target is 99.8% uptime total per month.

 

[John Mansfield]

David,

Excel can handle all that you're asking. It can automatically load
"dynamic" data (data originating from changing row and columns) into a chart
as well as read it directly from an SQL server link to a database. In order
to make Excel handle your requirements, the workbook in which your chart or
charts resides will probably need to be automated via the use of macros.

John Peltier's site might give you some ideas on how you could automate the
process:

http://www.peltiertech.com/Excel/Charts/index.html#hdrVBA

You might be able to post back here or in the Programming Forum to get help
with specifics of what you're trying to achieve.

 

[Jon Peltier]

David -

Pivot tables are the way to go with your data. John's rearrangement probably came
from a PT, though he didn't say so. Unfortunately, pivot charts are something of a
downer; 99.9% of the charts I make from pivot tables are standard charts.

Pivot tables can use external data as their source, but I'm not sure if they'll use
SQL. No matter, Excel will update that readily enough, and the output table is ideal
for the pivot table. Leave the query as it is, or for sure you'll need something
later that you decide to remove now.

The way many of my projects work is via VBA to (a) update the data, (b) construct
one or more pivot tables, (c) build the appropriate charts, and (d) export them into
Word or PowerPoint reports. Doing it in code makes it easier and faster than
dragging columns around.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______