Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Chart Report

Posted on 2002-06-17
3
Medium Priority
?
385 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 400 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

670 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