Am I unable to create stacked bar charts from reports?

Hi,

I'm currently trying to build a personal dashboard so that each person in our team (~75 users) is able to see their own individual metrics. One of the things I'm trying to build is a view where we can see their time spent on individual tasks as a percentage for each month over the last 12. Naturally with this being a personal dashboard, I want to make use of the 'Current User' filter option for these visuals, so I'm not sure a metrics sheet would be viable here as I don't believe I can make use of this filter without the source data being a report. Similarly, it seems particularly onerous to create an individual sheet for each team member given the size of the team in order to replicate this view.

I'm looking into how I can build this metric as it seems like an extremely common graph which should be easily created in any data visualisation tool, however I can't see how it's feasible in Smartsheet currently? This is extremely disappointing. Are we seriously unable to build charts with only 1 level of grouping from reports?

Find more posts tagged with

Reports

Dashboards

Metric Widget

graphs

Widget

Accepted answers

jmyzk_cloudsmart_jp

Hi @MikeyK93

I added columns to put hours worked for each series in the stacked columns chart like the one below.
(Billable, Non-Billable, No Task Type)

https://app.smartsheet.com/b/publish?EQBCT=a32364c6fd774c5aa5d7f55a980c0218 (Link to the publised demo dashboard)

As shown in the image below, I also added Year and Month columns to use as filters and groups in a report

Column	Formula
Billable	=IF([Task Type]@row = "Billable", [Time (Hours)]@row)
Non-Billable	=IF([Task Type]@row = "Non-Billable", [Time (Hours)]@row)
No Task Type	=IF(ISBLANK([Task Type]@row), [Time (Hours)]@row)
Year	=YEAR(Date@row)
Month	=MONTH(Date@row)

https://app.smartsheet.com/b/publish?EQBCT=a99f8ab3808d4b9ca77e1bb023183871 (Link to published demo sheet)

The same dashboard accessed by app@cloudsmart

All comments

Leroy Noriega

You can build one report and filter it by current user this will allow the Smartsheet user to be able to see their own task. In the dashboard you will need to utilize the report widgets to display the data and cahnge the Viewer Mode to Last Widget Editor.

Paul Newcome

I use grouped reports to build charts frequently. Are you able to provide some screenshots to show the source data, the report, and the steps you are taking to attempt to create the chart?

MikeyK93

Hi Paul,

I use PowerBI and Tableau to build dashboards typically, but we're using Control Centre and subsequently want to be able to leverage our project data into some more visual tools. If it weren't for this, I'd build this in PowerBI. The reason I'm explaining this is just to reiterate that I'm usually comfortable with the concept of proper datasets and how to manipulate data in such a way to build the visual I want, however Smartsheet doesn't seem to facilitate this functionality within a report. I'm fully aware of what visuals I can produce based on report data, but if I want to use specifically a report and not a sheet so as to take advantage of the current user filter to build a personal dashboard, I cannot build a bar chart which allows me to group on multiple columns.

The first screenshot below is a basic stacked bar chart using dummy data built in Excel - this is what I'm trying to reproduce for this dashboard.

So as you can see, I have months along the bottom, and a legend to denote the task being worked, with the number of hours logged by month for each. I'm not seeing how I could feasibly create this visual in Smartsheet using a report filtered on Current User, as I'd have to group by 2 different categories - firstly, I'd need to group by date, and also by task. Here's a sample of the data below, the column headers that I've highlighted are those that I would be looking to build the stacked column chart with - highlighted columns include Date, Task and Time (Hours). I'd want to aggregate time spent on each task for each date.

So I build a report to do this like below, but the end visual will not group by both date and task. It will just give me the total hours logged for each month. So in the below example, I'd want a stacked column with the highlighted values for each task in the column, but instead, for May it'll just report 34.5 hours logged.

This really irks me. This is such a common, trivial request that I'm not understanding why I'd need to go through the trouble of building a metrics sheet for functionality which should just outright be available - unless of course I am missing something obvious in which case, I apologise for the rant. But a metrics sheet wouldn't allow me to have this data readily available depending on who is viewing the dashboard either way, so it just looks to me like there is no real solution here?

Thanks,

Mike.

jmyzk_cloudsmart_jp

Hi @MikeyK93

I added columns to put hours worked for each series in the stacked columns chart like the one below.
(Billable, Non-Billable, No Task Type)

https://app.smartsheet.com/b/publish?EQBCT=a32364c6fd774c5aa5d7f55a980c0218 (Link to the publised demo dashboard)

As shown in the image below, I also added Year and Month columns to use as filters and groups in a report

Column	Formula
Billable	=IF([Task Type]@row = "Billable", [Time (Hours)]@row)
Non-Billable	=IF([Task Type]@row = "Non-Billable", [Time (Hours)]@row)
No Task Type	=IF(ISBLANK([Task Type]@row), [Time (Hours)]@row)
Year	=YEAR(Date@row)
Month	=MONTH(Date@row)

https://app.smartsheet.com/b/publish?EQBCT=a99f8ab3808d4b9ca77e1bb023183871 (Link to published demo sheet)

The same dashboard accessed by app@cloudsmart

MikeyK93

Hi @jmyzk_cloudsmart_jp ,

This is really good, thanks for your help. Really easy to restructure the data to work in this manner too.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.