Filtering Chart

[cc143most]

I have a report that includes a subreport, which is a chart. The main report is grouped by DEPT and I need the chart to show the info for that dept only. I have read the previous threads on accomplishing this and have even studied http://www.databasejournal.com/featu...7-Database.htm.



My problem is that I just do not see where I have RowSource listed as an option on my Property Sheet. Am I blind or is there something else I am missing?

As always, thank you for your help!

[June7]

Charts do have a RowSource property on the Data tab. All of my graphs use a simple SELECT query. Example for column chart:

SELECT DropNum, CBR FROM [dcpfiledata] WHERE CBR<9999 AND HoleNum=[tbxHoleNum];

The first field will be X axis values and other fields will be Y axis values.

Charts also have Master/Child links properties. I have only got these to work once and can't remember how, perhaps it was a crosstab query.

Is it really necessary to put the graph in a subreport? Maybe just in a header/footer of main report.

[cc143most]

Thank you for your quick response!

I was finally able to find the RowSource when I put my chart in the detail section. However, I still could not get it to group by Dept; it only repeated the chart. When I move the chart back to the "Dept Header", the RowSource disappears. This is what the RowSource statement reads (before I tried to add the WHERE statement): TRANSFORM Sum([Index]) AS [SumOfIndex] SELECT [Staff Name] FROM [Final Table] GROUP BY [Staff Name] PIVOT [Index];

I think the chart has to be a subreport as it's fields are pulled from a different table than the main report. If there's an easier way, PLEASE enlighten me.

Thank you again!

[June7]

The chart has its own RowSource for data and this can be independent of the data on the report. I do this and none of my charts are in subreports. However, there is a common key field in both data sources so I can filter the chart based on value in the report.

The RowSource property of the chart should be available no matter what section of report it is placed in.

If you want to provide db for analysis, follow instructions at bottom of my post.

[cc143most]

When I added the chart to my report directly, I get a blank chart (this is why I thought I had to do a subreport). It will take some time to delete the info, but I can send a copy of my db without any data (I will put in a few lines of fake data). I have never used charts and am a complete loss!

Thank you so much for all you do!

[cc143most]

June7,

As I was preparing my db for posting, my chart started working and filtering my data correctly. I have no idea why, since the only difference between the db was the amount of data. I am going to work on it a little more and see if I can figure out what is going on. I have until next weekend to get this thing right.

If I don't get it in the next couple of days, I'll post it (although I don't know what you can tell me when it's working).

Thank you so much for your assistance!

[cc143most]

After days and days of struggling, my chart is finally sorting by dept and matching with the detail below. However, when I go into the design view, the chart elements are no longer showing - all I have is a big white box. Do you know why this might happen? I need to change the color of my bars and the font in the axis. The chart was there when it wasn't working correctly and now it is not.

Also, I have column headings that I would normally put in the Page Header section to print at the top of each page. Since my chart is in the Group Header, I moved the column headings to Group Header, below the chart. This way they don't print above the chart on the first page of each Group. This, of course, means that my headings don't print on the subsequent pages. Do you have a suggestion for correcting this?

When all is said and done, I wanted to email this report to different personnel based on the Grouping (Dept). Am I reading right that I can't do that from one report? I actually have to run multiple reports? Aaaarrrrggggghhhhh!

I barely struggle through SQL for creating my queries and I don't have a clue regarding VBA. Be easy with me!

[June7]

Yes, graphing in Access is a challenge. Not sure why the elements are not showing in design view. Try saving the report from Layout and Preview, that usually works for me so that when I view in Design I see my elements instead of the default nonsense when the chart was created.

Try setting the GroupHeader RepeatSection property to Yes. Wait, that would repeat the chart on each page. You might just have to limit this report to output only one department at a time and put the chart in the report header or footer. By limiting the report to specific department, the grouping is not needed. However, just don't know enough about your data. You will have to do multiple report outputs anyway if you want each person/department to see only the data that pertains to them.

[cc143most]

June7,

Thank you for the advice on saving my report with a graph. Saving the report in Layout worked!

I am now in the midst of creating separate reports for each department, filtering the report - thank goodness I don't have to create multiple queries!

Thank you again for all of your help!

[June7]

You don't need to create separate reports for each department. Just one report with a filter applied for each output. Multiple emails of filtered report is common topic. Search the forum.

[cc143most]

I am so confused! Everything I looked at referenced VBA, which I do not know. I do not understand where the VBA code goes in my report. I am also having some other issues now -- my query is not including my "End Date" range - it ends the day before; and, now that I have separated my chart from the "detail" report, it is not grouping by dept even though I have the report grouped by dept and have put the chart in the group header.

Should I start a new thread before uploading my db?

[June7]

Just attach to post in this thread. Follow instructions at bottom of my post.

The code (macro or VBA) does not go in report, it goes in command button Click event. The button would be on a form. I use VBA, would be like:

Sub Command1_Click()
DoCmd.OpenReport "your report name", , , "Dept='" & Me.tbxDept & "'"
'code here to send email
End Sub

Additional code automation could open filtered report, send email, close report, repeat for each department. Review https://www.accessforums.net/program...ook-21903.html

[cc143most]

Every day, my friend receives an email of raw data (300+ lines) in Excel. She wants to analyze this data twice a month and send reports to each department supervisor. One report will be a graph with the summary data that can be printed. The other is detail data that is too large to print; but, can be utilized by the supervisors for more in-depth reviews as necessary.

I have the details of each of my tables/queries in my db. Note that I created Final_Qry to make a table (Final_Tbl) ONLY because, when I tried to use Final_Qry to create my graph, the calculated field, Index, did not show up as an available field for my graph.

Here are my issues:

1) I noticed that Master_Qry is not including my end date parameter; it ends the day before my end date.
2) I had the graph printing for each separate department when it was part of the Detail Report. Now that I have created a separate report for it (I will be adding more info on this report), it is not separating by Dept. I don't understand why.
3) I have NO CLUE how to run the same report, filtering by dept, separately to be emailed to each dept supervisor, other than to use the report filter (which requires multiple copies of the same report). She will be sending the report to all dept supervisors each week, and I would like to have a single "button push".

I appreciate your patience with me.....I cannot believe the knowledge I have lost in the last 2 years!!! What I created then was so much more complicated than this (but never required used of VBA). I feel so lost!

DB to Post.zip

[June7]

Issues I found:

1. The chart is not separating by Dept because there is no WHERE clause in the graph's RowSource. Put a textbox named tbxDept in Dept Header section bound to Dept field then chart RowSource can be:
SELECT [Staff Name], Index FROM Final_Tbl WHERE Dept=[tbxDept];

2. Should not have to create a table, use query. Final_Qry should just be a SELECT not MAKE TABLE. Then base the chart and its Report on Final_Qry and export the query to Excel. Okay, that sounded good, but I get an error when I try to use Final_Qry as report data source. The report doesn't recognize Dept field as a valid ControlSource. Very weird. But I don't use crosstab queries much, maybe has something to do with that. Might have to stick with the MAKE TABLE after all.

3. The Detail Report has criteria in its Filter property. Remove it because Master_Qry has filter criteria.

4. Query is not including the QEnd date because of time component. Include a calculated field in Master_Qry to extract the date part and apply date range criteria to that field. I see that you did this in other queries. Don't use Date as a field name, it is a reserved word, use SchedDate. If you want to be able to return records if one or both ends of date range not provided, use this criteria:
Between Nz([Forms]![Selection Form].[QStart],#1/1/2000#) And Nz([Forms]![Selection Form].[QEnd],#12/31/2900#)

5. Use Master_Qry instead of Master table in other queries and don't have to replicate the date range criteria.

6. Run Detail Report button opens Master_Qry and the report. Why open the query?

7. Final_Qry joining the two crosstab queries is flawed because 4 employees are in 2 departments.

8. Report has two textboxes with ControlSource that refer to controls of form. Missing = sign:
=Forms![Selection form].QStart
=Forms![Selection form].QEnd

When you feel ready to tackle the email procedure, review the link I provided and if you have issues with attempt to code, start a thread for that new topic.

[cc143most]

June7, you are WONDERFUL! Thank you for the time you took to study the db.

1. I had tried this before, but was using the Dept textbox I had in the Page Header and it just wasn't working. After some experimenting, this did!

2. I don't understand, either. But, it works when creating the table......I'll keep it that way.

3. Not sure where this came from. My original did not have any filter criteria. I removed it.

4. I had the calculated field originally, which explains why I just noticed that the end date was not included. I have fixed it again.

5. Brilliant! I knew I was missing something with all of the queries, but couldn't see the forest for the trees.

6. LOL. I can't find the right Macro for rerunning the query without opening it....(tried ReQuery, but it didn't work).

7. It is absolutely possible for an employee to be in more than one dept. Some RN's are moved around, based on need. I looked at the raw data coming in, before I changed the names for the uploaded db. There are, in fact, 4 employees who worked in multiple departments.

8. Don't know when I removed the equal signs, but they are back.

I am passing you a virtual hug ! I just cannot even begin to express what it means to have people such as yourself out there for help! Thank you again! I am off to study filtering reports! Have a wonderful weekend!