My chart made from a report will not show the date information on the horizontal axis.

Cory Bush

When I make a chart from a report the date does not show up. If I make it from a grid sheet the date is there. Using a sheet means I have to update it manually each day. How can I get the date to show up on a chart using information from a report?

Ben Goldblatt

Hi @Cory Bush,

I suspect the issue you're running into has to do with the column placement in your report. If you use a Report as a source item in a Chart, all of the data in the report gets selected for your widget. So, for example, if the Primary column in your report is set to the left of your "Date" column, the Primary column values would then take precedence for what gets displayed in the horizontal access in your chart. If the Primary column in the report is blank, no values will appear for that axis.

There isn't currently a way to remove the primary column and hiding the column won't change the behavior in the Chart widget. What you can do though is move the Primary column to the right of the last column in the report, making the "Date" column the leftmost column to drive the horizontal axis for your chart.

More information on working with Reports and Chart Widgets can be found in the following Help articles from the Learning Center:

• Report on Data from Multiple Sheets
• Charts in Dashboards

I hope this helps!

Thanks,

Ben

Ben Goldblatt

Hi @Cory Bush,

I suspect the issue you're running into has to do with the column placement in your report. If you use a Report as a source item in a Chart, all of the data in the report gets selected for your widget. So, for example, if the Primary column in your report is set to the left of your "Date" column, the Primary column values would then take precedence for what gets displayed in the horizontal access in your chart. If the Primary column in the report is blank, no values will appear for that axis.

There isn't currently a way to remove the primary column and hiding the column won't change the behavior in the Chart widget. What you can do though is move the Primary column to the right of the last column in the report, making the "Date" column the leftmost column to drive the horizontal axis for your chart.

More information on working with Reports and Chart Widgets can be found in the following Help articles from the Learning Center:

• Report on Data from Multiple Sheets
• Charts in Dashboards

I hope this helps!

Thanks,

Ben

Cory Bush

That fixed the issue. Thank you

mstanger

@Ben Goldblatt , I love the tip but my report is not letting me move the Primary column. What's the secret? (I'm not using any groups.)

Genevieve P.

Hi @mstanger

Are you using Summary at all? When using either Grouping or Summary the Primary column will be locked as the first column in the Report.

mstanger

That's it! Now I can put dates on the X-axis of a column chart--but the chart doesn't add together the units sold on the same date. How do I make that happen?

The report:

The chart:

Genevieve P.

Hi @mstanger

Thank you for clarifying what you're looking to do!

In this instance you'll need to adjust the Source data to have a date on each row, even if it's a duplicate date. Then when each row has a date, GROUP by the date in the Report, then use the SUMMARIZE function to SUM together the value in the Affordable Units column. This will SUM per date and then the Chart will automatically grab the data from those Summary rows.

mstanger

Update: following this announcement -- https://community.smartsheet.com/discussion/81983/new-create-a-chart-from-reports-with-grouping-and-summary#latest -- I grouped the report by date, and now I can chart the bars as expected, except that I don't know how to get the X-axis to show dates. (As a "bonus" I'd like to stack (break up) the columns by Sheet Name (project name).) I don't see an option for choosing the column Finish.

mstanger

https://community.smartsheet.com/discussion/comment/310699#Comment_310699

Thanks, @Genevieve P. ! I wrote my update before seeing your reply, but I think I did what you suggested. Not sure how to get the dates to appear on the X-axis.

Genevieve P.

Hi @mstanger

My apologies! I completely forgot - there's currently a bug with Grouped Dates in a Report, where the dates don't appear in the Chart correctly. I know our team is actively working on the bug, and this should hopefully be resolved in the next week or two.

I still think this is the best solution if you're able to wait for the fix?