How to access all columns in a Report when creating a Dashboard Chart?

Sam Mate

Hello!

I have a report that is summarizing project costs, sorted first by the line-item code in the budget and then by Quarter of Completion afterword.

I am hoping to represent this data graphically with a dashboard chart. The goal is to have a Bar Graph that shows the cost in each quarter, clustered by the budget item. When pulling the data from the Report, only the Primary (Sheet source name) and the Cost columns are available. (see pic below)

Essentially, I want to summarize 3 layers of information in the chart:

• Budget Delineation --> Quarter of Completion --> Cost

Hope is to have a color code for each Budget Delineation, then have x-val be the Quarter of Completion and y-val be the cost.

I tried to just pull the data directly from the source Sheet but always get the "Selected data can not be charted" error. I believe this is because many of the rows in the source sheet are blank in those columns, hence the filtering in the Report and the initial attempt to source the chart data there.

How can I make this graph? Do I need to make an entirely new Sheet that pulls the same filtered data from the source Sheet as the report? If so, what is the best approach to do that?

Apologies for the long ranting post. Your help is very much appreciated!

Thanks!

Genevieve P.

Hi @Sam Mate

What I would do in this instance is add a helper column in the underlying sheets that adds together your two text values: Budget Delineation & Quarter of Completion 

You can use a Column Formula to do this, for example:

="Sheet Name / " + [Quarter of Completion]@row

This would enable you to Group your Report by the helper formula column so that you only have one level of Grouping with a header such as:

"Sheet Name / Q4 - 2022"

Then your Graph can use the top level grouping and summary from the Report to create your bar charts, split up by both Budget and Quarter.

Let me know if this makes sense or if you'd like to see screen captures!

Cheers,

Genevieve

Genevieve P.

Hi @Sam Mate

What I would do in this instance is add a helper column in the underlying sheets that adds together your two text values: Budget Delineation & Quarter of Completion 

You can use a Column Formula to do this, for example:

="Sheet Name / " + [Quarter of Completion]@row

This would enable you to Group your Report by the helper formula column so that you only have one level of Grouping with a header such as:

"Sheet Name / Q4 - 2022"

Then your Graph can use the top level grouping and summary from the Report to create your bar charts, split up by both Budget and Quarter.

Let me know if this makes sense or if you'd like to see screen captures!

Cheers,

Genevieve

Sam Mate

@Genevieve P. thats done it!

For full context, I went back into the primary source sheet and added another column as you suggested:

Budget Item & QoC --> Colum Formula =[Budget Delineation]@row + ": " + [Quarter of Completion]@row

Then, because not every row meets the criteria in the sheet (some don't have a cost or are not aligned to a budget item etc) I then made a report that filtered out only the rows with complete data. This report was then the source for the dashboard chart.

Genevieve P.

Ah, wonderful!! I’m glad you got this working 🙂

Ashley McAdoo

@Genevieve P.

Hey Genevieve,

I've been working on charts and dashboards for a couple of days. First question is why do they charts not change when I make changes to the reports? I deleted the Week Number column from the report for this chart, refreshed several times and it's still showing up. I feel like I'm going nuts .

You can see the weeks are still the horizontal axis numbers. Here's my report

What I want to do is chart Actual vs. Planned by Employee by Week Number. Is that just impossible?

Thanks so much,

Ashley

Genevieve P.

Hi @Ashley McAdoo

The Report is grouping by the Week Number, which is why it's showing as your X-Axis in the chart. Even if the column is not visible in the Report, that's the value that the data is being Grouped by.

You would need to group by the Employee instead, however that would then ignore the Week Number. If you need it to be grouped both by the Employee and the Week, what I would do is use a hidden column in my source sheet that adds together those two values:

=[Assigned To]@row + "Week - " + [WEEK Number]@row

Then you can use this hidden helper column to Group so you have a unique grouping based on Employee and Week.

Either that, or you could create 1 chart per-employee, each chart based on a unique report that's filtered by the employee and grouped by the Week Number.

Cheers,
Genevieve

Ashley McAdoo

Hey @Genevieve P.

Thanks so much. One more question, why can't I get it to sort by week number? It's sorting by first name in Assigned To, even though I have it set to sort by week number.

Thank you,

Ashley

Genevieve P.

Hey @Ashley McAdoo

Apologies for the delay!

If you Group by Employee then the grouping will automatically sort by the Employee name, alphabetically. Applying a Sort will only affect the rows within each individual grouping, versus the report as a whole. Here's more information: https://help.smartsheet.com/articles/2482082-configure-grouping-to-organize-results-in-report-builder