Chart Report

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

ncrewAsked:
Who is Participating?
 
fontmasterConnect With a Mentor Commented:
You are missing months on the X-axis because the table containing the source data has no records containing dates of that month in any of its records.

I can only suggest that you create an interim table with 12 columns, one for each month.  Perhaps the expression for your stLinkCriteria could be separated into two steps.  The first would transfer the data for the selected server into that table.   Now you will have real zero values for some months, rather than missing data.  The rest of the stLink functionality would be used for the RowSource property in the "DoCmd.OpenReport" statement.

I'm not sufficiently knowledgeable about crosstab queries to determine if such a modification is reasonable.  But if it is, then when the report opens and performs its RowSource query against that interim table, the zero values would be assigned to X-Axis positions.
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: fontmaster  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
ncrewAuthor Commented:
While it was not the solution I was seeking, It was the best recieved.  Thank you fontmaster.
N.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.