Solved

Chart Report

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Restoring a form to its default settings 3 27
backup programme - VBA 3 24
Help with DoEvents 8 26
aggregate query? 20 48
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

770 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