Introducing new and modern chart types

[Michal_Bar]

When talking with Access customers, we often hear about the key role that Access reports play for decision makers looking to draw powerful insights out of their business’s data. The desire for Access to provide a fresher, more modern data visualizations – as well as a streamlined process for creating those visualizations – has been prominent in these conversations as well as in other channels such as User Voice. 

Today, the Access team is excited to unveil our response to these concerns: a new set of cleaner, more descriptive Access charts that provide more ways to visualize your data and present it in a new and modern way.

Say hello to the new and improved Column, Line, Bar, Pie, and Combo charts! Each of these chart types can easily be added to a form or report to visualize your data and help you make more informed decisions.

Column Chart

Line Chart

Bar Chart

Pie Chart

Combo Chart

Each chart can be customized to fit your specific needs using the new and intuitive Chart Settings pane and the familiar Property Sheet. Use these features to change the style and layout of the chart, add chart elements like legends and data labels, and fine-tune the fonts and colors.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates.

Modern and Classic Side By Side

We realize that many existing customer solutions rely on the current (aka classic) charts, and those solutions will continue to work – you’ll just have more options to choose from. Access developers can use the new charts alongside the classic charts within the same solution, and transition to the new charts at the pace they desire.

Feedback

We hope you’ll give the new charts a try – we’d love your feedback! We’re always looking for more ways to improve.

You can leave comments here, or use the Send-A-Smile tool in Access to let us know what you think of the new chart experience.  

[Dario Gori]

It's important to see the renewed commitment by Microsoft in support of Access desktop platform.
Ms Access is by far the best FE tool for RAD development and new features can only improve it ...
Thanks in particular to Michal for the commitment and enthusiasm to share all the news with the community !

[James Muka]

Excellent!

When exactly will the new charts be available?

[Karl_Donaubauer]

@James Muka  AFAIK as of today there is still no "exactly". This is what can be said according to MS sources:

Modern charts are coming "very soon" in O365 and they will also be in Access 2019, which is announced for the second half of this year, i.e. not quite so soon. There aren't specific dates for both yet. Meanwhile you can study the initial documentation. 

Karl
********
http://www.AccessDevCon.com
http://www.donkarl.com

[James Muka]

Thanks for the link Karl!

[Darrell DeRochier]

The modern charts don't appear to have the granular controls of the classic charts, such as the ability so specify the scale of the major and minor units on an axis. Is this correct? Or am I just not finding the new controls for managing axis scale?

[Michal_Bar]

Hi Darrell, there are two ways to control the settings of a modern chart - the Chart Settings pane and the property sheet.

Please try out both, and also check out this support article for more info.

Hope that helps,

Michal [MSFT]

[Paul Reeves]

Hi Michal, Could you take a look at the data label formatting on the stacked charts please? When there are large and small datasets, the data labels on Column stacked are forced to overlap and are unreadable. It's a little better in Bar mode, but the labels are drawn very close to the end, meaning its still unreadable. Example Colum https://postimg.cc/BL6677bc Example Column https://postimg.cc/HrMTScmx The text colour isn't consistently applied between black and white either in the orientations as you can see, and ideally the font and size would be exposed to the legibility can be increased. Thanks, Paul

[Gilberto Mendes]

After some time away from access o got a new project and was surprised to see the new charts! Thank God! The classic/activex had about 20 years and color and resize restrictions.

How can I define the sort order? I tried to change the order on the query but it didn't work.

[Edit] Found it like 30 seconds after posting my question. I changed the value axis to none instead of sum and worked nicely.

[Jeff Khoubesser]

It's definitely exciting to see modern charts added to Access.  But, after playing around with the new control a bit in Access 2019, it appears they're not as robust as the old pivot charts yet.  Hopefully they prove to be popular and the team can enhance them in the future 🙂

I included an image below of a chart I'd like to recreate with the new chart control.  It illustrates two of the most important things I'd like to accomplish:

1.  Show tool tips as the user hovers over data points

2.  Vary the fill color for different points in the same series  

Regarding #2, in this particular example, analysts use the chart as a guide to determine the number of quarters that will be used to generate a trend line for future prediction.  They move the slider bar to select the number of quarters and the chart dynamically fades out the unselected quarters and redraws the new trend line.  With the old pivot charts, we would simply modify the fill color of an element of the points collection.  I don't see that capability in the object model for the new chart object.  I can get the same final look by dynamically modifying the chart's row source to include multiple series that look like a single series.  But requerying the row source in the new chart object is laggy and visibly redraws the entire chart, causing it to disappear and reappear.  I can get a smoother effect by playing with Echo but the result is still not clean.

[James Muka]

I'm loving the new modern charts!

Very easy to use and very easy to work with in VBA.

It's making the whole interface on my application look modern.

Great job Access Team!

[Plent860]

Hi Michal, @Michal_Bar

In Access 2016 I'm missing the option in de ribbon to insert a combo chart. How can I install or activate this option?

I found out that it should be possible, see example: https://resources.techcommunity.microsoft.com/create-a-combo-chart-in-microsoft-access-2016/

But I do not see this option:

Regards, Ronald

[Tom_Buckley]

The bottom Axis of the line graph does not format or sort properly, at least not with currency values I'm trying to render, and there are no controls that enable me to format the bottom Axis properly. When I am able to force a Currency value to render, it sorts values alphabetically, but when the Axis values render as unformatted numbers (without any comma separators) it sorts properly, but does not render my secondary value. For now, the graphs are simply un-usable for my client.

[Shane Groff]

@Plent860 It looks like you don't have O365 (or Office 2019).  This feature is not available in Office 2016.  The link you provided does not work for me, so I can't check whether it correctly states the versions of Office for which the feature is available.

Shane Groff

Access Engineering

[Shane Groff]

@Tom_Buckley 

Hi Tom,

In some cases you can achieve the results you want by bypassing some of the automatic machinery and just do what you want manually, using a query.

For example if I have a table called CurrencyData, with fields CurrencyAxis and Values, that hold a currency value to be used on the x-axis, and a corresponding value on the y-axis respectively, I can create a query like this:

SELECT Format([CurrencyAxis],"Currency") AS Expr1, CurrencyData.Values
FROM CurrencyData
ORDER BY CurrencyData.CurrencyAxis;

This will generate values formatted as currency to use on the x-axis, but will sort them by the actual currency amount (rather than lexically).

Then when you generate the chart, choose Expr1 as the 'axis' field, and Values as the 'values' field, and then change the aggregate on Values from Sum to None.  If you now look at the 'Transformed Row Source' property, you will see it has "SELECT [Expr1], [Values] FROM [Query1]", this allows you to get the formatting and sorting you want using the query and the chart just uses the data as you've supplied it.

If you do need aggregates using this approach, you would just do them yourself in the query, rather than relying on the chart to do them for you.

This might still not be exactly what you want, since the chart is now just treating the x-axis values as text, it doesn't adjust the data points based on the value, it just treats them as a sequence (so if you have $10, $100, $110, $200, the space between $10 and $100 will be the same as the space between $100 and $110, rather than having the former be spaced further apart as they would if they were treated as numeric values).

We will certainly consider the request to add formatting for the x-axis values to a future release.

Thanks for the feedback.

Shane Groff

Access Engineering

[Tom_Buckley]

Thanks Shane, I think I've narrowed down where the inconsistency is coming from - I can get the bottom axis to format as currency and sort properly when I'm pulling directly from a table using a simple query as described (also note I'm trying to graph two currency values both as primary values). However, when using a Union query (in my case pulling from 3 tables, and yes the data appears correctly when viewing it), the graph will not render the second value at all, and also the bottom axis does not sort properly until I add the first value. If I replicate that same dataset to a temporary table, then use a simple query against it, it appears to work fine with both values rendered on the graph properly as currency. So near as I can figure the graph doesn't appear to like Union queries.

Didn't mean for this to turn into a trouble ticket, but I do appreciate the input. BTW, my version of Office shows as "Microsoft Office 365 Pro Plus", version 1902 build 11328.20318 click-to-run.

[Snick-bjr]

I've been checking and downloading the office 365 updates regularly due to the need for using the new chart function(s) in Access.

One area / functionality I have not been able to get working on the new charts is the use of what is labelled "Legend (Series)".

I'm able to use the same query with the old chart functionality, and it will show the data as expected.

I also went into the data source under properties and looked at the query automatically generated by the chart. The data in there looks to be correct, however the chart does not reflect that. As soon as I select a a Legend, the chart stops working.

I'm trying to understand if I'm doing something wrong, or if the funtionality just isn't there yet.

Has anyone been able to get the new Charts wo work with Legends selected?

I'm using Version 1907 (Build 11901.20176 Click-to-Run)

Any feedback is appreciated.

[Shane Groff]

@Snick-bjr 

Yes, this should work in version 1907 (for O365/Office 2019)

Here's an example from Northwind using the Customers table:

The RowSource for the chart is just 'Customers' and the TransformedRowSource (generated for you automatically) is:

TRANSFORM Count([ID]) AS [CountOfID] SELECT [State/Province] FROM [Customers] GROUP BY [State/Province] ORDER BY [State/Province] PIVOT [Job Title]

All I did to generate this was select State/Province as the Axis field, Job Title as the Legend field, and ID (Count) as the Value field.

Can you be more specific about what your data looks like, and what happens when you select a field for the Legend?

Shane

[Snick-bjr]

@Shane Groff

Thank you for your response.

To test what you said, I downloaded the query I was trying to graph and then uploaded it to a table.

And as you said, I was able to graph it using the new Chart and selecting a Legend along with Axis (Catergory) & Values (Y axis).

It graphed as expected.

So I guess the issue may have something to do with the chain of queries I'm using to create the data in first place.

I've designed it to filter in three steps, where step (0) is the raw data in a table.

(0) raw data

(1) Query that is a straight "Select" query where I'm joining 4 tables and filtering on 5 specific fields that the user defines by use of a form. For some of the defined fields, there are multiple (7 or 😎 filter choices.

(2) A second query, with only the 1st query as input, that uses "Totals" functionality in the query and generates some flags that I use in the final query for manipulating the data.

(3) The third and final query, also using the "Totals" functionality, is built off of the second query that is joined with one table to force a specific ordering of what will be displayed on the graph. There is also a number of DSUM calcuation I do in the query.

I then place a chart on a form and select the 3rd query as input.

As soon as I select an item for the legend, the graph blanks. Even if I switch to "Form" view. The chart is blank.

I can go into the the query created by the chart (Select the chart, view properties, select "Data" tab on the properties sheet, and then select "Transformed Row Source". That brings up the query created by the Chart.) And I can see all the data is generated correctly as expected.

But the chart for some reason is unable to display it.

Seperate question related, I noticed you some how put a graph onto your response.

In my initial response, I tried to past a screenshot into the comment, but it didn't seem to take.

How do I put graphics into the comments?

Thank you for your time.

[Shane Groff]

@Snick-bjr 

Are you saying that you created a table with the data from query (3) then used that as the input, rather than using the query, and that produce the expected results?  

If this is something you can share, you could send me a link to your database in a private message, and I should be able to determine why the chart is not showing data.  

To get the chart into my message, I copied from Access (you need to be in form view, rather than design view), then pasted into Paint, and saved as a file, then you can just choose Insert Photos from the message toolbar.

[Snick-bjr]

@Shane Groff 

Yes, I did create a table from Query 3 and used that as input which created the expected results for the chart.

Let me go through the data base and see if I can make it non-confidential.

If so, then I'll just send you the database. It's under 12 Meg.

How do I send you a private message?

Understand on comment on using the message bar to insert photo, or other items, into the comments. Didn't think to look there. Thank you.

[Shane Groff]

@Snick-bjr if you click on my name, it should take you to my profile, and ther will be a 'Send Message' button.

[Snick-bjr]

@Shane Groff 

Thank you.

I took a deep dive into troubleshooting.

Right now I've confirmed it doesn't work with the first query.

And even after I've stripped it down to nothing and removed all of the linked tables, still the same problem.

However, when I create a new (simple) query and pull from the base table, the chart graphed OK.

So next I'm going to try rebuilding the first query from scratch.

I'm wondering if there isn't some underlying connection that was updated in the query builds that I'm missing.

I've been updating the Office, but been carrying the queries from the older versions.

I'll let you know how it turns out.

[Snick-bjr]

@Shane Groff 

It seems to have something to do with the "Parameters".

I took the stripped down query that didn't work. The only thing I hadn't deleted yet was the Parameters.

I have quite a few parameters defined due to the links with the form for defining what to look at.

After I deleted all of the Parameters, the Chart worked fine when I selected the Legend.

I'm not sure, but I think (or hoping) the Parameters were needed to use the old style graphing.

I'll be testing that next...

[Shane Groff]

@Snick-bjr 

Interesting find.  I'm assuming that your SQL for the query specifies the values of the parameters?  (Otherwise, you would get prompted for the parameters when the query was executed).

When you specify a legend for a chart, we use a crosstab query (SQL TRANSFORM) to generate the data (each data element in the legend field generates a column in the output data).  When you have a crosstab query with a parameter, we can't determine the set of columns without knowing the value of the parameter, and we need to know what the output columns are to generate the chart.

It looks like even if you do specify the values of the parameters in the query SQL, we still won't correctly resolve the set of columns.  You can see this limitation in other places as well, but it is obvious in a chart since the chart just goes blank.

I'll open an issue to track this, we may be able to at least give a better indication of what the issue is so it will be easier to know how to address it.

In the meantime, removing the parameters is the right way to address this.

Shane

[Snick-bjr]

@Shane Groff 

Just starting to run some more tests.

I'm wondering if the timing has something to do with it.

The reason I say that is when I open the Chart's query, the values are there. But they don't show up on the graph.

I created a new query that matches everything in my 1st query without using any Parameters and without the Criteria being pulled from a form, basically no filtering. And the Chart seems to be able to handle the selection of a Legend.

If I add a hard Criteria, "ZS" in the Criteria section, the chart will still display the Legend OK.

However, if I change the criteria to a variable pulled off of a form, then the chart just shows blank.

example: [Forms]![Frm_FilterSetup_rev01]![txtBx_SalesDivision1]

I'll have a little more time today to play with this. Then with tomorrow being the first of the month, I may get tied up with other things for a few days.

I'll try to put together a database that emulates the problem that I can send you.

But I may not be able to get to it until early next week.

Thanks again for your time and patience.

[Snick-bjr]

@Shane Groff 

I think I understand the issue along with usable workaround.

However, I do think it would be nicer if it just worked as expected 😊

Issue:

l  When using Parameters that are variable(s) and are selected by the user from a form, if a cross tab query is anywhere in the chain of calculations to get to the final data, it cannot be displayed on a Form or Report. (The Report aspect has not been tested, but assuming it would be the same issue.)

Ø  If one tries to display a table in a Subform or such, the table will not display

Ø  If one tries to use the new (2018-2019) charting capability and selects a “Legend (Series)”, the chart will not display

l  The underlying problem is that when there is a crosstab query with a parameter, the set of columns cannot be determined without knowing the value of the parameter.

Ø  Without knowing what the output columns are it’s not possible to generate the table or chart.

Ø  FYI – understanding that the flexibility is very limited, but the old charting functionality can handle it.

Workaround:

l  Once the final data / query is obtained the data can be written to a temporary table for displaying and graphing.

Ø  This requires quite a bit of coding to assure that the graph & table are always in sync with what the user has selected.

Ø  However, it does seem to give the final result of being able to fully use the updated (2018 – 2019) charting capability added to Access.

From your comments, it sounds like the issue is known.

If you would like for me to create a database that creates the phenomenon, let me know.

Otherwise, I’m assuming it’s not needed.

Related but different question on the formating in the new charts

When I click on the chart, select "Properties" / "Format" I found selections for:

(1) "PrimaryValues Axis Format"

(2) "Primary Vlaues Asix Display Units"

(-) However, I can find anything that lets me set the "Decimal Places".

Does a setting for Decimal Places for Axis Format exist, and I'm just missing it?

If / when the new (2018-2019) charting functionality is updated to handle CrossTab with Parameters, please let me (and others) know.

Thank you again for your time and explanations.

BrianR

[Snick-bjr]

@Shane Groff

Just a follow-up in that I've figured out the answer to my follow-up questions concerning decimal points.

In stead of selecting a input from the drop down list, I can input my own format. i.e.: #,###

This gives me the format on the axis I was looking for, decimal place = 0 under the Access Properties gui.

[Shane Groff]

Sorry I didn't get back to you about the formatting, but you found the intended solution.

I have what I need to reproduce and understand the problem.  If we don't get a fix in sooner, then we may do so along with other improvements to the new charts.

Thanks again for trying out the charts, and giving us details about the issues you encountered.

Shane

[Snick-bjr]

Question on accessing the "Data Series" lines in a chart via vba.

I checked the MS development center online, https://msdn.microsoft.com/en-us/vba/access-vba/articles/access-modern-charts-properties

It implies that as of now (Sept.'19), accessing the different Data Series via VBA to do things like switch the line visability on or off, set line "Dash Type", set "Series Fill Color", etc is not avaible yet.

Can anyone confirm if this is a correct understanding, or if there is some way to access the Data Series Formating controls (not sure if they are controls or something else)?

Any feedback is appreciated.

[Shane Groff]

Hi Brian,

You can set the properties on data series in VBA.  You access the series property using the ChartSeriesCollection.

For example, if you have a series named "SumOfPrice", and you want to set the dash style, you can use something like:

Forms!FormName!ChartName.ChartSeriesCollection("SumOfPrice").DashType = acDashTypeDot

Each element in the ChartSeriesCollection is a ChartSeries object, and you can see the properties for ChartSeries here:

https://docs.microsoft.com/en-us/office/vba/api/access.chartseries

Shane

[Snick-bjr]

Thank you, I just did a quick test.

That's exacly what I was looking for.

[TOC72]

Hi,

I have been trying to replicate the display of percentages in the data label on a pie chart in the modern pie chart without success. In standard charts, I would select Chart > Chart Options > Data Labels > Percentage. In the modern pie chart property sheet I have tried selecting Percentage in the Primary Values Axis format but this just  adds "00.00%" to the count number on the pie chart. 

Is it possible to replicate in modern charts?

Any help would be appreciated.

Tim

[Snick-bjr]

Tim,

I went to try and see if I can create this for testing. And realized that your question doesn't have enough data for me to replicate it.

If you can help with some more data, I'll try taking a shot at it.

(1) Are you using a table or a query as your source?

(2) Is the data you are graphing off of already in % format, or are you having the table generate the percentages from numbers (where the total is set to 100%)?

(2a) Is it possible for you to show a small example of the way your data is formated?

BrianR

[Snick-bjr]

Question concerning working with pie charts

Before, Shane explained that to set the properties on data series in VBA one access the series property using the ChartSeriesCollection.

example: Forms!FormName!ChartName.ChartSeriesCollection("SumOfPrice").DashType = acDashTypeDot

However, with a pie chart, there is only one data series.

And assuming that the data series is broken into the following categores {X1, X2, X3, ..., Xn}

I'd like that over a number of different charts, each category {Xi} is consitently shown with the same color.

Does anyone know if there's anyway to access the fill colors used in a pie chart with VBA?

any suggestions or pointers are appreciated.

Snick

[Shane Groff]

Pie Charts don't currently support manually setting the colors of the slices.

It is a good feature suggestion.

Shane

[Snick-bjr]

Thank you for letting me know. 

(I replied to your email my Tuesday, forgetting that's set for no reply.)

I've logged the request under Help / Feedback / I have a suggestion ... for Access

[gazelle]

I have a query about pie charts - I'm in the process of converting an old .adp database to .mdb, and am nearing the finish line.  However, it seems that the .adp pie charts allowed unlimited (or at least a high number of) data points, whereas the new version seems to cut off at 20.  I've looked high and low for a setting somewhere that will increase the limit, to no avail, and I can't seem to find any mention of others having this problem.  I have pie charts that theoretically could have up to 75 points - I understand that's not ideal, and maybe even an "Others" bucket in the legend would appease my users, but I can't have it just cutting off at 20.

Does anyone have any suggestions? Thanks in advance!

[Snick-bjr]

Hi Gazelle,

I don't know if I can answer your question or not yet. I need some clarification on what you mean when you say "data points".

When I first read it, I though you were talking about the number of sections in the pie.

But in reading it again, you seem to be saying there just a lot of data points, and nothing concerning the number of sections the pie is split up into.

What exactly do you mean when you say the number of data points cuts off at 20?

Also, are you getting some sort of error when you try to create or use the chart?

Snick

[dspooner]

Is there no way to customise the colours of different charts? It looks pretty terrible with all my pie charts orange and blue...

[Shane Groff]

@dspooner No, there is currently no way to specify custom colors for pie charts.  This is a good suggestion, though.  If you haven't already done so, consider creating anew request at https://access.uservoice.com/.

Thanks for the feedback,

Shane

[mrt1_]

I have designed a line chart in MSACCESS 2019. I selected DISPLAY DATA LABEL to displays values. The chart displays these values. I save the chart and reopen the same chart which which displays a different product. However the data value are not displayed. I checked the DISPLAY DATA LABEL and found that it has not being selected.

The chart only displays the data label for the product-id, which I have selected.

What must I do to ensure that the chart shows data labels for all product id's

mrt1_

[haribabu369]

what is minimum RAM required for modern charts ? because when i run access file in 8GB RAM charts are not loaded.

[markt66]

Nice to see these new charts and great work by the MS team. Just one question. Is there a way to create Box & Whisker charts in Access. If not is this something you might include in the future? Thanks, Mark

[IanCampbell]

Saw an earlier post with the same predicament: unable to add %'s as data labels in the modern pie chart. It appears only count is supported? I even created a query with an expression to derive the percentage but still cannot use anything other than count for the labels. 

[Snick-bjr]

General comments for using the new charting with VBA. (26 Oct.'20, Japan time)

(-) There is a direct answer related to getting the % values to correctly display on a pie chart - without using VBA at the bottom of this post.

1st, I’m not sure how or when new functionality is added. However, I’ve continued to download the latest office 365 whenever I run into an issue when working with Access.

Recently I’ve found that I’m now manually (VBA) able to access more of the chart items then I could a few months ago. I can’t say for sure if it’s just me figuring something out that I didn’t understand before, or if there really has been a change in the underlying coding. I think that at least for some of the issues I’ve run into, there has been some updates that have let me access parts of the charts’ object that I couldn’t before.

A good place to start is at the following Microsoft link:

https://docs.microsoft.com/en-us/office/vba/api/access.chart

And try play around and become comfortable with using the items listed under the “Properties” listing.

Some are simply one dimension structures and others are more complex.

A useful substructure is the, ChartSeriesCollection. This will let you access many of the items you see on the “Property Sheet”.

This is done through the following:

1. Start with a Control object:
1. “Dim objChart As Control”
2. And you set that object to the chart you want to work with.
1. Set objChart = Controls(str_ChartNameYouWantToWorkWith)

You now can access most, if not all, of the items on the chart.

• Simple items examples that I referred to as one dimensional above.

                    objChart.PrimaryValuesAxisRange = glb_AxisRange_Auto

                    objChart.ChartValues = "% of Total"

• Now if you want to access items that are listed under the “Property Sheet” you need to use the ChartSeriesCollection object.

                    objChart.ChartSeriesCollection("Name Of Field Being Graphed").DisplayName = "Revenue"

Now directly to the question concerning showing the % in a pie chart, without using VBA.

First I created a simple Table, “Table1”, as shown below:

Then I created a form with pie charts.

• If you leave everything as default and simply check “Display Data Label” as shown in the above example by IanCampell, the percentages do not display correctly.

However, if you go into Properties / Format and set the “Primary Values Axis Format”, it will display as shown above.

The decimal places shown is determined on how you have formatted it on the table, under “Field Properties”.

May everyone have a pleasant rest of your day and many more.