Posted on 2002-06-17
I need help with the source query for a chart.
I want to see a bar chart representing the percentage of uptime each server had in the last rolling 12 month period.
For example for the current 12 month period, the X axis would be Jun-01, July-01, etc. up to May-02. If the report were run next month the X axis would show July-01 through Jun-02. (All 12 months)
The Y axis would be the percentage.
The bars would be the different servers.
The way my source query is written now only shows the months on the X axis that have data, so if there was no data for Jun-01, the X axis would start with Jul-01. This makes the users think that the report is only pulling 11 months. I need it to display all 12 months even if there is no data.
Below is the SQL that is written behind a command button for the chart source. (It's not the entire code, just the relevant parts.)
SelCriteria = "Between DateAdd('m',-12,Now()) And Now())"
selection1 is where the user selects which server to view
stLinkCriteria = "TRANSFORM Avg(qry_AvailabilityChart.RA) AS AvgOfRA SELECT qry_AvailabilityChart.AsOfDate FROM qry_AvailabilityChart WHERE (((qry_AvailabilityChart.AsOfDate) " & SelCriteria & " AND ((qry_AvailabilityChart.Region)='" & selection1 & "')) GROUP BY qry_AvailabilityChart.AsOfDate PIVOT qry_AvailabilityChart.ServerName;"
DoCmd.OpenReport stDocName, acViewDesign
Reports![rpt_AvailabilitybyRegionBarChart]![Availability Bar Graph].RowSource = stLinkCriteria