How to access all columns in a Report when creating a Dashboard Chart?
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!
Best Answer
-
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
Answers
-
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. 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.
-
Ah, wonderful!! I’m glad you got this working 🙂
