Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Multiple Charts per Report

    I have a query that has three columns: Distributor,Month,Sales. It contains the monthly sales for each distributor. I have a bar chart that shows each month along with the amount sales. Problem is my query contains all the distributors but I only want to view one distributor on the chart at a time.

    I have been messing with this and the only thing I can see is to change the query to only contain the currently viewed distributor.

    Does anyone know of a better way?
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am not sure if a graph works any differently from any other control, but I would suggest you design your report to use the query for all distributors. Then when the user clicks the button to open the report use this code:

    DoCmd.OpenReport "YourReportName",,,"DistributorID=" & txtDistributor

    YourReportName should be the name of your report.

    This will open the report for only that one distributor. It is equivalent to setting the Filter property in the report and the FilterOn property.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so do each barchart as separate sub report
    create appropriate report headers using grouping and insert each chart triggered on your preferred group


    btw be prepared for a long wait if there are many charts to produce...it can get incredibly slow if you include a large number of charts in a single report
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    Thanks for the replies. I found a way, but it is almost quitting time so I'm waiting until tomorrow to fine tune it.

    In short I used one subform(with the chart) in the groupfooter of the report. Then I used a query based on a form textbox as the recordsource of the subform. When the group changed on the report I changed the text box and requeried and refreshed.

    It was a bit more involved than I initially thought. Once in print preview mode the filter property cannot be accessed, neither can the record source of the subform, which would have made things easier. Anyway I'll expand on my solution further tomorrow.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  5. #5
    Join Date
    Jan 2004
    Posts
    145
    Ok here is what I did. I have temp table, form, query and report. I created a temp table to hold all the data (much faster). On the form there is an unbound text box that will be used in the criteria of the query to specify which distributor to show. The form uses the query as it's recordSource. The query uses the temp table as it's recordsource. The form is defaulted to be viewed as a pivotChart.

    The report also uses the temp table as a recordsource. It is then grouped by distributor. I put the form in a subform in the group footer (or header) and hid the details. On each format of the group header I set the form text box to the current distributor.

    I hope this is clear enough. I'd be glad to clarify if anyone is ever in need of something like this.

    Note: there is some code behind the form to setup the chart data and appearance.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

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