Why can't I pick the data I need for a chart from a report

Lewis Kelly

I have a chart that is pulling the information from a report that has additional columns which are used to make sure only the correct rows information is pulled from the base sheet. However, as I'm trying to do this the Chart will only select the whole report and not give me the option to select the chart data in the report. I have done this on a previous Chart but cannot workout what has changed as I can't find any options to make it work the way it used to.

Mary_A

@Lewis Kelly Select Columns allows you to pick which columns you want to chart.

Lewis Kelly

Hi Mary,

I just had a look and the issues is that the system won't let me get that far.

The report I have has 2 non numeric columns. One is a colour dot column as I use the report as a Legend table on a Dashboard, (something that should be an option as part of the Chart Widget). The other is an identifier that makes sure I adding the correct rows to the report as this changes month to month.

The frustrating part is that this is testing version but the original version works just fine and still allows me to highlight the data I want to chart which I created less than 6 months ago. I just cannot work out what changed or a good reason for SmartSheets to take this functionality away as it just makes the whole process much harder than it needs to be.

Lewis Kelly

This is a quick update as the problem seems to be fixed. The first this is that I found that Power Automate was adding a leading apostrophe (') which meant SmartSheets saw the value as text and not a number. I got a suggestion from the Power Automate Community (I wasn't sure where the issue really sat) to add some additional cells that removed the apostrophe using VALUE(SUBSTITUTE([Column]@row, "'",""). This seems to work fine and allows for the Report to pull the number through to the Dashboard.

I also noticed that the charts now allow you to select the range when pulling from a grid but select everything when pulling from a Report. I'm not sure if this is new as I didn't notice it before. It turned out to not be part of the issue I had but it was a bit confusing.