+ Reply to Thread
Results 1 to 8 of 8

Help with Charting My Data --

  1. #1
    DavidM
    Guest

    Help with Charting My Data --

    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



  2. #2
    John Mansfield
    Guest

    RE: Help with Charting My Data --

    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.

    ----
    Regards,
    John Mansfield
    http://www.pdbook.com


    "DavidM" wrote:

    > 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
    >
    >
    >


  3. #3
    DavidM
    Guest

    Re: Help with Charting My Data --

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >
    > ----
    > Regards,
    > John Mansfield
    > http://www.pdbook.com
    >
    >
    > "DavidM" wrote:
    >
    >> 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
    >>
    >>
    >>




  4. #4
    John Mansfield
    Guest

    Re: Help with Charting My Data --

    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/Cha...ex.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.

    ----
    Regards,
    John Mansfield
    http://www.pdbook.com

    "DavidM" wrote:

    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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.
    > >
    > > ----
    > > Regards,
    > > John Mansfield
    > > http://www.pdbook.com
    > >
    > >
    > > "DavidM" wrote:
    > >
    > >> 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
    > >>
    > >>
    > >>

    >
    >
    >


  5. #5
    Jon Peltier
    Guest

    Re: Help with Charting My Data --

    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/
    _______

    DavidM wrote:

    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >
    >>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.
    >>
    >>----
    >>Regards,
    >>John Mansfield
    >>http://www.pdbook.com
    >>
    >>
    >>"DavidM" wrote:
    >>
    >>
    >>>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
    >>>
    >>>
    >>>

    >
    >
    >



  6. #6

    Re: Help with Charting My Data --

    David,

    If you wan't your boos to have data "on the fly", you might wanna have a
    look at this link: http://www.primeconsulting.com/faqs/faq4650.html

    Michael


    "Jon Peltier" wrote:

    > 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/
    > _______
    >
    > DavidM wrote:
    >
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >
    > >>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.
    > >>
    > >>----
    > >>Regards,
    > >>John Mansfield
    > >>http://www.pdbook.com
    > >>
    > >>
    > >>"DavidM" wrote:
    > >>
    > >>
    > >>>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
    > >>>
    > >>>
    > >>>

    > >
    > >
    > >

    >
    >


  7. #7
    DavidM
    Guest

    Re: Help with Charting My Data --

    Hi all --

    I have most of my Excel project working. The only thing I'm struggling with
    now is trying to get Excel to prompt for for parameter input when running a
    query.

    I designed my query using SQL Server QA and saved to a file. I then go to
    Excel and copy/paste my SQL into the MS Query tool. It prompts me that I
    will lose GUI functionality from the editor.

    This is fine.

    I guess, in return, I also lose the ability to use in the value field [Enter
    Year] for it to prompt for parameters to run the query. Anyone know of any
    way around this?

    Also, I'm not sure why when I paste my query and save... and return to
    Excel.... and then click the icon to edit my query... and it returns back
    to MS Query tool, and the SQL button loses my original SQL. In order for me
    to edit, I have to copy/paste it back into the box. Very annoying.

    I'm using Excel 2000.

    Does any of this work better with Office XP?


    "[email protected]" <[email protected]>
    wrote in message news:[email protected]...
    > David,
    >
    > If you wan't your boos to have data "on the fly", you might wanna have a
    > look at this link: http://www.primeconsulting.com/faqs/faq4650.html
    >
    > Michael
    >
    >
    > "Jon Peltier" wrote:
    >
    >> 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/
    >> _______
    >>
    >> DavidM wrote:
    >>
    >> > 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" <[email protected]> wrote in
    >> > message
    >> > news:[email protected]...
    >> >
    >> >>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.
    >> >>
    >> >>----
    >> >>Regards,
    >> >>John Mansfield
    >> >>http://www.pdbook.com
    >> >>
    >> >>
    >> >>"DavidM" wrote:
    >> >>
    >> >>
    >> >>>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
    >> >>>
    >> >>>
    >> >>>
    >> >
    >> >
    >> >

    >>
    >>




  8. #8
    onedaywhen
    Guest

    Re: Help with Charting My Data --

    DavidM wrote:
    > I designed my query using SQL Server QA
    > I then go to
    > Excel and copy/paste my SQL into the MS Query tool. It prompts me

    that I
    > will lose GUI functionality from the editor.
    > I guess, in return, I also lose the ability to use in the value field

    [Enter
    > Year] for it to prompt for parameters to run the query.


    Correct.

    > Anyone know of any
    > way around this?


    Re-write the query using MS Query's GUI tools <g>. I can't see how you
    can have it both ways i.e. T-SQL syntax generally and MS Query's syntax
    just for parameters, the two don't mix. The obvious workaround is to
    get the user input (e.g. via a userform dialog) and dynamically change
    the SQL/stored proc call each time using VBA code (see
    http://www.*****-clicks.com/excel/Ex....htm#ChangeSQL).
    Jamie.

    --


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1