Chart in Report

  • Thread starter Thread starter David
  • Start date Start date
D

David

I have a report to write containing a chart. I have never
done a chart before.
However, the report I'm creating is similar to an existing
report and I have copied and pasted that one and am
modifying it.
I'm looking at the properties for the chart (a pie chart)
and I see a very complex SELECT statement in the 'Row
Source' property.
Can someone explain what this select statement is supposed
to do? I would think it would somehow provide the amounts
for the various pie slices.
The chart is in a footer so it prints multiple times at
control breaks.
Here is the SQL:
SELECT IIf([Category]="Priority" Or [Category]="General",
[Category] & " Projects",[Category]) AS ISR_Category, Sum
(tblActivity.Activity_Hours) AS SumOfActivity_Hours
FROM tblInformationServiceRequests LEFT JOIN (tblActivity
LEFT JOIN tblEngineerDepartments ON tblActivity.Engineer =
tblEngineerDepartments.Engineer) ON
tblInformationServiceRequests.Request_Num =
tblActivity.Request_Num
WHERE (((tblActivity.Activity_Date) Between BeginDate()
And EndDate()) AND ((IIf(ISDepartmentSelect()=False,True,
[tblEngineerDepartments].[IS_Department]=ISDepartmentValue
()))=True))
GROUP BY IIf([Category]="Priority" Or [Category]="General",
[Category] & " Projects",[Category]);
 
Here it is broken down using easier to read basic code:
===========================================
SELECT IIf([Category]="Priority" Or [Category]="General",
[Category] & " Projects",[Category]) AS ISR_Category,
----------------
ISR_Category:
If [Category] ]= "Priority" or [Category]="General" Then
[Category] & " Projects"
Else
[Category]
End if
===========================================
Sum(tblActivity.Activity_Hours) AS SumOfActivity_Hours
----------------
SumOfActivity_Hours:
Sum(tblActivity.Activity_Hours)
[Note, you are grouping on the ISR_Category field, and for each
separate item it groups by you are adding the values in this field]
===========================================
FROM tblInformationServiceRequests LEFT JOIN (tblActivity
LEFT JOIN tblEngineerDepartments ON tblActivity.Engineer =
tblEngineerDepartments.Engineer) ON
tblInformationServiceRequests.Request_Num =
tblActivity.Request_Num

Your tables are joined on Engineer and Request_Num
===========================================
WHERE (((tblActivity.Activity_Date) Between BeginDate()
And EndDate())
----------------
Dates must fall in this range
===========================================
AND ((IIf(ISDepartmentSelect()=False,True,
[tblEngineerDepartments].[IS_Department]=ISDepartmentValue
()))=True))
----------------
AND
IIf ISDepartmentSelect() = False Then
True
Else
[tblEngineerDepartments].[IS_Department]=[ISDepartmentValue]()
(This will return true if they are the same, otherwise false)
End if
The result of this If statement must match True
===========================================
GROUP BY IIf([Category]="Priority" Or [Category]="General",
[Category] & " Projects",[Category]);

You are grouping by your first field i.e.: ISR_Category
===========================================

Only two fields are reported from this query: ISR_Category and
SumOfActivity_Hours. This is what your chart is based on.

If you put your SQL code in a new query, then look at it in the Query
Designer (query grid), it becomes a lot more obvious what it is doing. Look
at the datasheet view to see the results of the query.

HTH
Mich


David said:
I have a report to write containing a chart. I have never
done a chart before.
However, the report I'm creating is similar to an existing
report and I have copied and pasted that one and am
modifying it.
I'm looking at the properties for the chart (a pie chart)
and I see a very complex SELECT statement in the 'Row
Source' property.
Can someone explain what this select statement is supposed
to do? I would think it would somehow provide the amounts
for the various pie slices.
The chart is in a footer so it prints multiple times at
control breaks.
Here is the SQL:
SELECT IIf([Category]="Priority" Or [Category]="General",
[Category] & " Projects",[Category]) AS ISR_Category, Sum
(tblActivity.Activity_Hours) AS SumOfActivity_Hours
FROM tblInformationServiceRequests LEFT JOIN (tblActivity
LEFT JOIN tblEngineerDepartments ON tblActivity.Engineer =
tblEngineerDepartments.Engineer) ON
tblInformationServiceRequests.Request_Num =
tblActivity.Request_Num
WHERE (((tblActivity.Activity_Date) Between BeginDate()
And EndDate()) AND ((IIf(ISDepartmentSelect()=False,True,
[tblEngineerDepartments].[IS_Department]=ISDepartmentValue
()))=True))
GROUP BY IIf([Category]="Priority" Or [Category]="General",
[Category] & " Projects",[Category]);
Click to expand...
 
Back
Top