Query and cfchart question

[trojnfn]

I have this simple query :

SELECT top 10
  SupplierName, supplierNumber
  SUM(TOTALS) AS TOTAL
  FROM tableName

It will give me a simple output like:

company1     12345     20

company2     98881     5

company3     76512     18

What I need to do is plot the query in pie chart, with a drilldown report for each pie slice (supplier name) :

<cfchart
         format="flash"
   chartwidth="350"
   chartheight="450"
   title='"Top 10 Supplier Volume "'
         pieslicestyle="sliced"
         labelformat="number"
         show3d="yes"
   url="../reports/supplierVolumeReport.cfm?supplierName=$itemlabel$&supplierNumber=#qryBuyerVolume.supplier#">
      <cfchartSeries type="pie"
            query="queryName"
            itemcolumn="supplierName"
            valuecolumn="Total"           
            datalabelstyle="value"
            colorlist="##CE1126,##3399CC,##CC5500,##444444,##00CC33,##7C96A1,##DAD9A0">
      </cfchartseries>
      </cfchart>

Everything works fine. But when I go to the drilldown report, I cannot really search by supplierName because some companies might have the same name, but the supplier numbers are unique, so I have to search by supplier number. That is why I am passing that value also.

But when I click on pie slice for company 3, it passes the supplier name company 3, but the supplier number is always the first one, 12345, regardless of what slice I click on. I have cfoutput in the report and it shows company 3 and 12345.

How do I get the corresponding supplier number for the pie slice supplier name that I click on ? I need the corresponding supplier number so I can use it to search the query in the drilldown report.

I tried to combine the name and number in the query and have the chart display 12345 - Company 1 in the legend, but they do not want the number to display, just the name in the legend.

[Dan_Bracuk]

On your graph, pass the company name and total as a list with a delimter not likely to appear in the company name.  Like this:

supplierName=$itemlabel$¿$value$

On your drill down page, start with a query to get the supplier number.

select suppliernumber, count(*)

from yourtable

where suppliername = ListFirst(url.suppliername, "¿")

group by suppliernumber

having count(*) = ListLast(url.suppliername, "¿")