Report Graphs

[dr4ke1990]

Morning all,



I currently have a database running to provide some indicate Capacity Management figures. The report includes Server Name & Disk name. What I would like to do is create a report to show the growth, or not as the case may be, of each disk in the given server.

The table below is an example; when I try to create a Graph I need all 4 to create the growth etc but Access only allows me 3! Am I being stupid?

Server Name	Disk Name	Space Used (MB)	Date
Server1	A	30	23/10/2013
Server1	B	31	23/10/2013
Server1	C	45	23/10/2013
Server2	A	09	23/10/2013
Server2	B	10	23/10/2013
Server3	A	12	23/10/2013
Server4	A	15	23/10/2013

[June7]

You want the data summarized by server/disk combined? You want to graph over time?

Maybe this is what you want as the RowSource of graph control for a line chart:

TRANSFORM Sum(Table1.[Space Used]) AS [SumSpaceUsed] SELECT (Format([Date Read],"mmm"" '""yy")) AS Expr1 FROM Table1 GROUP BY (Year([Date Read])*12+Month([Date Read])-1), (Format([Date Read],"mmm"" '""yy")) PIVOT [Server Name] & [Disk Name];


Advise no spaces or special characters/punctuation (underscore is exception) in names, nor reserved words as names - Date is a reserved word.

[dr4ke1990]

Thanks June,

That works perfectly; I need to go through and split into Queries so all Servers/Disks aren't in the same graph (a bit overkill) but this is very much what I wanted!!

Could I now ask for an explanation on the code? I've transferred it into my Database names (I've made it group by Day rather than month/year as well - I dont have a lot of data yet), and have split down the bits that I'm unsure of. I only ask for this because this doesn't seem possible when going through the 'wizard' - or did I miss something along the way?

New code:

 GROUP BY (Day([Date_Of_Report])*12+Month([Date_Of_Report])-1)

In my current setup this is the code used

 GROUP BY (Int([Date_Of_Report]))

so here somes the ignorant question - what does yours do that mine doesn't?

New code:

 (Format([Date_Of_Report],"DDDDD")) PIVOT [Computer Name] & [Disk ID];

In my current setup this is the code used. Is this the bit you can't do using the Wizard? Using SQL this is where you are able to chose an additional variable?

  PIVOT [DISK ID];

Regarding the naming convention; point taken about the spaces! I'll see if I can get the data out to fix that..

[June7]

I let the chart wizard build a line graph with whatever fields were available. Then I modified the RowSource query to use concatenated server/disk fields. I didn't even build the GROUP BY expression, Access did that for me.

[dr4ke1990]

Hi - Sorry this confuses me. How do you concatenate the server/disk fields?

I have just created another query to only give me the data which shows issues, and I've tried doing creating a graph by the SQL you gave me above and also trying to figure out the concatenate method; but the graph is showing the default East, West, North line graph which has nothing to do with my data whatsoever... I've reviewed everything I've done and it all looks ok so I don't know why it isn't picking up the data...

:-(

[June7]

It will show the default nonsense until you open the form or report in regular view and save it from that view. Then reopen in design view and should see your setup.

Concatenating two fields is just a calculation using the concatenation operator. Click the RowSource ellipses (...) to open the query designer to modify.

[dr4ke1990]

Sorry - I did try that but it's not working. To give a better view of how my Database is structured; I currently have a few queries working out dailyincreases in space & subsequently how many days left by dividing by the free space. I then split out the servers which have a potential issue (i.e. are decreasing in space) - what I'm trying to do is then link this back to my table with the data in it (Server Name, Disk ID, Percentage Used & Date) and pull out only the data for those which have an issue to create the graph.... so I don't show anything for those with no issues... When I try to export it to PDF, as I wanted to see if that would give me the right data, it is completely blank.

At one point I had an error coming up saying (Forms!FrontPage!SelectStart) is not a valid variable (I use a form input to decide what two dates to compare) - the names are correct as my queries are working and my data reports are fine.... I'm completely confused.

I've pasted the report SQL that the wizard is coming out with, just in case that highlights any issues.....

 TRANSFORM Sum([Percentage_Used]) AS [SumOfPercentage_Used] SELECT (Format([Date_Of_Report],"DDDDD")) FROM [ReportIssues]   GROUP BY (Int([Date_Of_Report])),(Format([Date_Of_Report],"DDDDD")) PIVOT [DISK ID];

[June7]

You tried what that isn't working - saving the form from regular Form view? Editing the RowSource?

I deal with graphing issues best when I can test with data. That's what I did in this case, built a table and form based on the data sample you posted. I built a graph on the form as described.

Are you trying to pdf the form? Use a report, works for me.

[dr4ke1990]

It's not working when I input the data using the wizard & then try to view the report. It doesn't work in either the report view or when pdf'd...

[June7]

What do you mean by 'input the data using the wizard'? What does wizard have to do with inputting data? As noted, I got a graph with the sample data you posted and you said that was what you needed and it worked. Afraid I can't analyse these issues from description, I have no idea what you are doing. I create pdf's from reports with graphs frequently.