Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21

    Unanswered: Chart in subreport help

    I've been wrestling with this off and on for a few days and came close but it's still not right. I'll try to explain it as best I can.

    I created a report with a pie chart on the first page that is a summary of issues for all of the types of users we track. Those users are: employees, customers, third-party, vendor and offshore. The first chart shows total issues for all of these people. The pies in the chart are the priority of issues. Those issues are: Code Red, Priority 1, Priority 2 and Priority 3. Here's the kicker, my director wants the slice that represents Code Reds to always be red and only that slice. It's entirely possible that a user group has no Code Reds. Access charts assign colors by series, not the value of the data so I created a temp table with all of the Priorities that will be set to nulls and then updated with the counts for the priorities as I do the groups. The charts are all in subreports. The main report has the details of the issues. The first subreport, the overall pie chart looks fine. My problem is with the subreports with user group pie charts. Where do I initialize my temp table to 0's and then update it with values for that group? I've tried various incarnations of delete, update and append queries out the OnPrint and OnFormat events of the user group section in the report but to no avail. I'm not really looking for coding or querying help. I'm just having a hard time figuring out the logic and sequence of queries or where to put them. If temp tables to insure that the red slices are only Code Reds aren't the way to go, I'm OK with scrapping that idea and doing something else.

    Thanks for any help.

    John

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not sure I really understand but since no one's responded, I thought I'd try and take a stab at answering in some way. It may spark some answers from others.

    Again, I don't think I comprehend the problem but a point on the subject of colors - I'm wondering if you had a "color" table with the colorvalues and groups which could be matched against the data groups and updated with your temp table you base the colors for the pie chart off of (if that makes any sense). So Group 1 in your temp table also has a colorvalue field. Not sure if that helps or would even work but it might be worth a shot for designating colors with groups.

    "Where do I initialize my temp table to 0's and then update it with values for that group?"

    This is confusing to me as I don't really grasp the question. I'm guessing when a button is clicked on the form, you run all your queries to make the temp tables, append or update data and then open the report with the pie charts. What I did was I made all the temp tables when the user clicked the button and then I opened the report or excel pie chart in the same code as the buttons click event. Or on the button's click event I made the temp tables, exported temp tables to spreadsheets (or updated data in the excel table), and then opened the excel pie charts (which were linked to the spreadsheet).

    Thus, I utilized two different methods when I created Pie charts - 1 way was to update/create an excel spreadsheet of which a pre-designed Excel Pie chart was based off of (i.e. Excel Pie Chart linked to the Excel Spreadsheet) and then I open the excel pie chart from Access. And the other way was to embed the Excel sheet into an Access report. Each had it's own advantages and drawbacks. Updating the Excel sheet with values or creating a new one where the pie chart was linked to allowed me to set the Excel Pie chart up with a certain look (which Access also allowed me to do to a point) but was more difficult to translate some values to. The Access embed Excel Pie-chart Report was a little easier to get the values into but gave me more problems on getting the look of the pie chart right (i.e. lines to the slices with labels kept overlapping.)

    I hope this helps a little?? Maybe it will spark some feedback from others as I hate to see a question go unanswered in this forum.
    Last edited by pkstormy; 06-10-07 at 00:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21
    Paul,

    Sorry for not getting back sooner. I was out sick for a few days. Your suggestion just might help. I didn't try embedding an Excel pie chart to the Access report. I know that you have a lot more control with Excel charts so I'll try that. I also appreciate the fact that you hate to see questions go unanswered! Thanks!

    John

  4. #4
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21

    Question

    Quote Originally Posted by pkstormy
    I'm wondering if you had a "color" table with the colorvalues and groups which could be matched against the data groups and updated with your temp table you base the colors for the pie chart off of (if that makes any sense). So Group 1 in your temp table also has a colorvalue field. Not sure if that helps or would even work but it might be worth a shot for designating colors with groups.
    You might be on to something there, Paul. But can you programmatically manipulate the colors in an Access pie chart? I thought when you opened it up for editing, you were actually opening another application, Microsoft Graph, or something like that.

Posting Permissions

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