Dynamic form to create pie chart - problem reprentingthe data not selected by user

[sviolet]

I have created a pie chart based on what a user select on a form. For example,
a user can select from a list of species they want to view a pie chart for.
(they can select 3 species or 20, its up to them)

The purpose of the pie chart is to illustrate the percent of a species based
on the total species.

Everything works fine except the pie chart created does not taken into account
the species the user
did not select .

What I need is something like an "other species" (ie: the species the user DID
NOT select") slice. Does this make sense?

The percentage values used to create the query are already created in a MS
Acess database.

For example, the user select Bird 'a' and Bird 'b'. Bird 'a' is 3% and Bird
'b' is 4%. I need a slice that represents 'other species' 84%.

My code:

<cfquery name="MakePieChart" datasource="bsc">
Select *
From Calculated
Where Year IN (#ListQualify(form.Year, "'", ",", "All")#)
And SiteCode IN (#ListQualify(form.SiteCode, "'", ",", "All")#)
And Speciesdesc IN (#ListQualify(form.SpeciesDesc, "'", ",", "All")#)
</cfquery>

<cfchart show3d="yes" scalefrom="1" showxgridlines="yes" chartwidth="367"
chartheight="300" fontsize="6" showlegend="yes">
<cfchartseries type="pie" query="MakePieChart" valuecolumn="Percent"
itemcolumn="Speciesdesc">
</cfchart>
<cfoutput>
<font face="MS Sans Serif"><font size="3">Percentage of selected species
observed during <b>#MakePieChart.Year#</b> at <b>#MakePieChart.SiteName#
(Site:#MakePieChart.SiteCode#)</b><br></font>
</cfoutput>

Any ideas?

Also, it is better to create calculated values in your database of in Cold
Fusion?

thanks for your time

[MikerRoo]

The answer to your first question depends on how you got that 84% for "other
species". Did you mean 93% (100 - 3 - 4)?
If not, please explain and supply the schema for Calculated.

Do all of the Percent columns, in calculated, add up to 100%?

For Q2 (should calculated values be in DB or couldfusion?):
This always depends on the situation. Here are some best practices:

A) If that calc value can be known when the data is inserted and does not
depend on any other data (that can change), store it in the table.

B) If the calc value can be determined by the existing data and query
parameters, calculate it in the query or Stored procedure that fetches the data.

C) If the calc value depends on data that is not in the DB and that isn't part
of the query parameters. Use coldfusion.

D) In general, use the approach that operates on the smallest dataset or is
fastest.

-- MikeR

[sviolet]

The answer to your first question depends on how you got that 84% for "other
species". Did you mean 93% (100 - 3 - 4)?
Yes, my mistake, should be 93%

Do all of the Percent columns, in calculated, add up to 100%? Yes they should.

The equation for calculating percent is: number of selected bird species (ie:
bluebird) / total number of birds found at selected Site for a selected year *
100

User selects the Site, the Year and the specices to be included in the pie
chart. I am not sure how to best represent the "other species" that they did
not select.

Thanks for all you help

[MikerRoo]

OK, The query below should add an "All others" pie slice to your chart.
Alternatively, you can use the QueryAddRow() and QuerySetCell() functions.

Regards,
-- MikeR



<!--- Get pie chart data....
This query gets the percent of sightings, for the selected species, during
the selected year(s), and only at the selected site(s).

An "All others" row slice is added to make the pie chart total 100%.

Note: This query returns columns named "percent" and "year". These are
common names and also reserved words in SQL. These columns should be
renamed.
Perhaps "SightingPercent" and "SightingYear".

Note2: SQL Server syntax used. Adjust for other RDBMS.
--->

<cfquery name="MakePieChart" datasource="bsc">
SELECT -- Note select * can't be used for union Q's (avoid it anyway).
Speciesdesc,
[Percent],
SiteName,
[Year],
SiteCode
FROM
Calculated
WHERE
Year IN (#ListQualify(form.Year, "'", ",", "All")#)
AND
SiteCode IN (#ListQualify(form.SiteCode, "'", ",", "All")#)
AND
Speciesdesc IN (#ListQualify(form.SpeciesDesc, "'", ",", "All")#)

UNION SELECT
'All Others' AS Speciesdesc,
SUM ([Percent]) AS [Percent],
'Aggregate Site' AS SiteName, -- This value will be ignored.
Max ([Year]) AS [Year], -- This value will be ignored.

*** USE THIS if Sitecode is numeric. ***
COUNT (SiteCode) AS SiteCode -- This value will be ignored.

*** OTHERWISE, USE THIS if Sitecode is string. ***
'Aggregate Sitecode' AS SiteCode -- This value will be ignored.
FROM
Calculated
WHERE
Year IN (#ListQualify(form.Year, "'", ",", "All")#)
AND
SiteCode IN (#ListQualify(form.SiteCode, "'", ",", "All")#)
AND
Speciesdesc NOT IN (#ListQualify(form.SpeciesDesc, "'", ",", "All")#)

ORDER BY
[Percent] DESC
</cfquery>

[sviolet]

Thanks Mike, you're a genius, I can't thank you enough!!!!!

I modified your code slightly (because I was getting the error message
"Circular Reference Caused by Alias") I also changed Percent to BirdPercent and
Year to BirdYear)

****************************************
<cfquery name="MakePieChart" datasource="bsc">
SELECT
Speciesdesc,
BirdPercent,
SiteName,
BirdYear,
SiteCode
FROM
Calculated
WHERE
BirdYear IN (#ListQualify(form.BirdYear, "'", ",", "All")#)
AND
SiteCode IN (#ListQualify(form.SiteCode, "'", ",", "All")#)
AND
Speciesdesc IN (#ListQualify(form.SpeciesDesc, "'", ",", "All")#)

UNION SELECT
'All Others' AS Speciesdesc2,
SUM (BirdPercent) AS BirdPercent2,
'Aggregate Site' AS SiteName2,
Max (BirdYear) AS BirdYear2,
'Aggregate Sitecode' AS SiteCode2

FROM
Calculated
WHERE
BirdYear IN (#ListQualify(form.BirdYear, "'", ",", "All")#)
AND
SiteCode IN (#ListQualify(form.SiteCode, "'", ",", "All")#)
AND
Speciesdesc NOT IN (#ListQualify(form.SpeciesDesc, "'", ",", "All")#)

ORDER BY
BirdPercent
</cfquery>

[MikerRoo]

That's weird that you had to make those changes. What DB system are you using?

Anyway, glad to help.
-- MikeR

[sviolet]

I am using MS Access

Thanks for all your help!