Solved

Chart Report

Posted on 2002-06-17
3
378 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:ncrew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
fontmaster earned 200 total points
ID: 7091467
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7242229

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
 

Author Comment

by:ncrew
ID: 7243176
While it was not the solution I was seeking, It was the best recieved.  Thank you fontmaster.
N.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question