Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Unanswered: Pass Through Query (SPT) with Pivotchart (OWC10)

    Hi All,

    I'm attempting to use a Pass-Through Query (SPT) to get records back from Oracle to then use

    in a pivotchart (OWC10). I'm only persisting because the SPT is lightning quick and anyone

    using Access as a front-end to another database over a network should try it.

    Anyhow, the SPT is fine, when I loop through the ADO recordset it brings back and write the

    field values to a table, it shows the expected results.

    I use the execute method of the connection...

    eg rs_Chrt = sptConn.Execute(chrtSQL, intCount, 1)

    This brings back a Read Only, Forward Only recordset which I thought would be fine for a

    pivotchart. I set this recordset as the datasource for the chartspace object for but when I

    try and setdata, it bombs out with an unknown automation error.

    I've also tried using the rs.open method with a client side cursor and I get a bit further,

    however it doesn't chart the values "CASES" properly - see below:

    With oChSpace
    .HasMultipleCharts = False
    .PlotAllAggregates = chPlotAggregatesSeries
    .SetData chDimValues, chDataBound, "CASES"
    .SetData chDimSeriesNames, chDataBound, "STATE"
    .SetData chDimCategories, chDataBound, "MYWEEK"
    End With

    I even thought about using this recordset to make a table but all the examples of doing this

    involve looping though the recordset, which takes forever and defeats the whole purpose of

    using the SPT.

    Any help would be greatly appreciated. My database is used by epidemiologists to investigate

    communicable disease outbreaks in Australia, so it is a worthy cause, and who knows, maybe

    you'll catch a disease in our part of the world one day!

    Many thanks

    surv

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think it's crapping out because you can't set the recordsource to a disconnected value; especially because the recordset must be open while the object needs it! (I think )

    I suggest you stick the values in a temporary table; no loops involved, just two commands
    1) empty the table
    2) INSERT your recordset into it

    Then you can close your disconnected recordset object etc and use your temporary table as the source.


    ...I think
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Recordsets are cursors - you have to iterate them. George - point two requires iterating the recordset right?

    You can't use a recordset as the record source for an object that evaluates the data as a set (reports, charts etc).

    Have you tried creating a persisted pass through query (i.e. one you can see in the database window) and set that as the chart record source?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, I must re-phrase...

    2) Execute an INSERT statement on the destination table from the source table.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so my follow up question this time is: Why bother?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To resolve the above issue..?

    Because it is an external data source; I reckon the control is having a hissy fit. If you dump it locally, which I know is not ideal, the problem should no longer persist.

    Unless you can set the source of the chart to an Array I can't think of another way of getting round the problem atm.

    So hush you
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2007
    Posts
    7
    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.

    Thanks again!
    surv

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is the problem now then with creating a chart unrelated to the source of the data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2007
    Posts
    7
    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?

  10. #10
    Join Date
    Jul 2007
    Posts
    7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •