Thanks Georgev and Pootle Flump, I also thought the control was being reckless. So I created the persistent SPT you mentioned and it brings back everything I want when I run it. My SQL looks like this: SELECT TO_CHAR(DIAGNOSIS_DATE,'WW') AS MYWEEK, TO_CHAR(DIAGNOSIS_DATE, 'YYYY') AS MYYEAR, COUNT(SDW_NNDSS.NID) AS CASES, STATE FROM NDW.SDW_NNDSS
WHERE DISEASE_CODE='002' AND DIAGNOSIS_DATE BETWEEN TO_DATE('01/01/2007','DD/MM/YYYY') AND TO_DATE('31/12/2007','DD/MM/YYYY')
GROUP BY TO_CHAR(DIAGNOSIS_DATE, 'YYYY'), TO_CHAR(DIAGNOSIS_DATE,'WW'), STATE
ORDER BY MYYEAR, MYWEEK, STATE
(basically I get back wk1-52 and a count of a disease for each state and territory - there are only 8 here. So I get wk1 SA 107; wk1 NSW 412; and so on). But when I set this qry as the recordsource for a chart (both normal and pivot charts), it displays the same crud I get from using a recordset as the datasource. The crud is interesting and suggests something... I get 53 weeks on my x-axis (that's good), I get the states as a series (fine) but instead of getting a count of disease in the data area, I get one unit per state. So if all states and territories had a case(s) that week, I get a stacked column = 8 on the y-axis, and not the sum of all diseases for all states and territories.
So, my query results look very different to what gets displayed. It isn't just OWC either, the 'vanilla' stacked column chart does exactly the same.
I don't know what is going on. I'll try any ideas you think might work... I've spent to much time on this to abandon it without anything to show. Anyone else, pls jump in.
I can't be sure. Both charts don't like my datasource even though it looks 'fit' in the persistent query. If I loop through the recordset and write it row by row to a table, it also looks fine. Maybe you're right, iteration is the key, damn slow key at that. I'd write my rs to a table and use that for the charts if there was a quick way of doing that but it seems like I have to step row by row through my lightning quick recordset to make it the datasource of anything. Is there another way of speeding up my poor old pivot chart?
I'm closing in on this sucker... apparently there is a way to append your recordset to a table in one fell swoop, executing "INSERT INTO myTable (Column1, Column2, ...ColumnX) Values (objRS.Fields("Field1"), objRS.Fields("Field2"), objRS.Fields("FieldX")) where objRS is the recordset returned by your pass-through query. However, I haven't quite worked out how to execute this string, maybe with a local db connection as it is a local table ?? Hey, feel free to jump in guys (and gals) and point the way if you can.