Creating a new chart from 1 sheet of Data

[mutley1]

Hi experts,

I am probably having a blonde moment but I have a few columns of data that I want to put into 1 graph and cant work it out. from the below example, I get the first db name / dates and sizes, then the chart continues with the second. What I want to do is get 2 lines (one for each DB over the same dates. The X axis does the first, then the second DB, so looking at it frmo left to right, it does the bbb database and then the aaa database, but i want to see them both on the same dates and how they are against each other. I would like 2 lines without having to transpose. any help appreciated.

Database	Date	Size MB
bbb	16/08/2008	43333.63
bbb	25/08/2008	43658.58
bbb	30/08/2008	43834.81
bbb	06/09/2008	44029.74
aaa	16/08/2008	33333.63
aaa	25/08/2008	33658.58
aaa	30/08/2008	33834.81
aaa	06/09/2008	34029.74

Thanks,

M

 

[SkipVought]

What application?

If Excel, did you try using the chart wizard?

What seems to be the problem?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Assuming that it is Excel...

You must transform the data.

Use MS Query on a separate sheet. Assume Data on Sheet1 and Query on Sheet2. Your SQL Transform...
[tt]
Transform Sum([Size MB])

Select Date

From [Sheet1$]

Group By Date

Pivot Database
[/tt]
Then plot, using the chart wizard (2 series-one for each Database)

Did the entire thing in about 1 minute.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mutley1]

I am using Excel, but cant seem to work out how to get the change of DB in that column to show as a seperate line in the chart. Been a long time since i used Excel and even with the wizard I cant seem to get 2 plots for the seperate DB's unless I transpose and have the data reading horizontally ass opposed to vertically.

Cheers,

M.

 

[mutley1]

Thanks Skip - think I'll go with spitting it when it is output from SQL with a select so it writes each DB to a seperate sheet. I have about 500 dates per database so a transform will go a little to far for 20 databases X 500 on 1 sheet tranformed.

Thanks for the input though - just checking I wasn't going mad and that I should have to do something to produce a proper graph.

Ta,

M.

 

[SkipVought]

Consider ONE CHART with a Combobox control to select a database. Could be as simple as a Data > Validation LIST in one cell.

First make a list of unique databases for the Combobox.

Then Query your data with the Combobox selection as a Query Parameter. The Chart Source Data would be your QueryTable resultset.

Quite simple and much more elegant AND maintainable than 20 separate charts (YUK!)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mutley1]

I know what you mean - thing is with the 20 DB's, some are hovering around 1MB in size and others up to 50GB so why the boss wants all on 1 chart is a bit baffling to me. won't even see the 1MB databases apart from a blur ar the bottom of the screen. I'll just pick the top 5 and give him that report.......if doesn't like it, then hey....have to negotiate!

Thanks for your advice Skip.

Cheers,

M.