Solved

Chart Report

Posted on 2002-06-17
3
365 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

710 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