Struggling with Charts in Reports

[platypusfeet]

I am trying to add charts to a report in Access 2016. The chart will be based on data sums and is placed in the report header.

My report is shows all of the "item" entries for a specific asset (which is selected through query via a combo box form upon open). One of the fields in the item table is risk level. Risks can be either 1, 2, or 3.

I know that I can display the total number of items using =Count(*) and I have text boxes that show the number of items with each risk level as =Count(IIf([RiskLevelID]=#,0)) where # is 1, 2, or 3 (I have one box for each).

I have also created a query that I can use to group items (the entries in tblRISKS) by AssetID into the categories by risk level. The SQL for the query is:

SELECT tblRISKS.AssetID, Count(IIf([RiskLevelID]=1,0)) AS CountLow, Count(IIf([RiskLevelID]=2,0)) AS CountMedium, Count(IIf([RiskLevelID]=3,0)) AS CountHigh
FROM tblRISKS
GROUP BY tblRISKS.AssetID;

I can create a chart using this query in a blank form and it works exactly as expected (sorting items by asset then showing how many fall into each risk level category).

I want to create an equivalent chart in my report that displays CountLow, CountMedium, and CountHigh for the asset that the report is about. Ideally this would be a pie chart.

I've gone through the chart wizard a few times using different variations of the query above, but it doesn't work. I am assuming the error is in the SELECT statement, but I am not sure how to correctly specify the SELECT statement that I need.

The report is based on a query that pulls from a number of tables (including tblRISKS). The input to the query is tblRISKS.AssetID (which, as mentioned, must be specified upon opening the report). I also have the AssetID on my report in a text box named "BoxAssetID".

I couldn't figure out if I could just semi-manually enter the data for the chart (for example by manually typing the Count(IIf()) statements into the chart datasheet or by setting the datasheet values equal to the Count(IIf()) boxes that are already on my form) as a way to avoid the chart wizard, but I had no luck.

I've been at it for a couple hours now and I feel like I'm missing something basic, but I've been having just no luck. Any advice would be greatly appreciated!

 

[June7]

I analyze chart issues best working with data. If you want to provide db for analysis, follow instructions at bottom of my post.

Does sound like you need a WHERE clause in the chart RowSource SQL.

Once chart is created by wizard, can manually change properties such as RowSource.

 

[platypusfeet]

Hi June7,

Thank you for your offer. Unfortunately, I can't share the database for confidentiality reasons. Is there any chance you have an example of the SQL for a similar-enough chart you've created before? Maybe just for any chart within a report that is based on a query?

Thank you again!

 

[isladogs]

There's no reason why a chart based on that SQL would work in aby form but not in a report. However if you are filtering the data by asset for your report then you need to modify the SQL accordingly.
In this case you need a HAVING clause based on the asset value selected n your form. Something like this perhaps

SELECT [tblRISKS].[AssetID], Count(IIf([RiskLevelID]=1,0)) AS CountLow, Count(IIf([RiskLevelID]=2,0)) AS CountMedium, Count(IIf([RiskLevelID]=3,0)) AS CountHigh
FROM tblRISKS
GROUP BY [tblRISKS].[AssetID]
HAVING ((([tblRISKS].[AssetID])=[Forms]![FormName].[txtAsset]));

 

[June7]

I have charts on reports that reference a textbox on the report to synchronize with the current record.

SELECT Size, Pass, Hi, Lo FROM GraphBMD WHERE LabNum=[tbxLabNum] ORDER BY Size;

 

[platypusfeet]

Isladogs and June7 - thank you both for your help!

I still can't actually create the chart within the report, but I found that if I create it with the correct WHERE clause in a different form I can then copy and paste it into my report and it works! Thank you!!

I know that's not the most satisfying way of accomplishing things, but I was able to fix my syntax with your help and it does work!

I have a very large report header (as large as I could make it) which has caused other bugs that I found other people reported but were never fixed (like, I can no longer move anything within the details section because it will pop up to the header...so I have to just move everything within the properties or by resizing). I am curious if maybe the issues I'm having with charts could be related to some underlying bugs in the report.

I'm just happy that I found a way to make progress again. Thanks again!! (And Happy New Year!)

 

[platypusfeet]

Just as a follow up: I created a copy of my report and made the report header smaller. It did help with the bug related to moving fields in the details section (there's still some weird jumping when I move things, but I can move them again), but doesn't seem to have really helped with the charts. The odd thing to me is that when I run through the chart wizard the preview of the chart is correct. However, it only ever displays as the default chart (in Design View) or blank (in Report View and Print Preview).

So I think it's fair to say I was probably wrong about the chart issue being related to the report header. :/

Edit: It seems that it also isn't possible in Access 2016 to create/edit/modify charts using VBA, based just on some googling. If I am wrong, please let me know because that would be great.

 

[isladogs]

You've already said the data is confidential.
However could you supply some example data for the tblRisks together with the relevant form and report so someone can test it and hopefully find a fix.
The data doesn't need to be real ... just realistic ... with all unnecessary fields removed or blanked

 

[platypusfeet]

Isladogs - I think that is a good idea.

I'm honestly just worried about the time it would take me to create something I could share. I'm not the best programmer...so the nuts and bolts of the database are probably a bit hairy. I'm on a bit of a deadline and I have some naysayers I need to convince, so I just need to get a proof of concept working. In the case that I can get everyone (or at least someone high enough up) on board, I'm hoping we can hire someone who can find a more robust solution to the troubles I'm having.

I'll get it going with the work-around of creating charts in forms and copy-pasting, but I'll try to come back and provide a working/testable sample data and forms when I have the chance. (Or if I stumble across a solution, I'll post as well).

Thanks again!

 

[isladogs]

Up to you but all we need are the items I mentioned previously.
I'd rather not have lots of items that aren't anything to do with this issue.

Perfectly happy to ignore all unrelated code (or you can remove it if you prefer)

Happy to help further but I don't think we can do any more unless you can upload something