Formulas in Reports/Reference reports for Dashboard

J Johnson

I read in the community (the posts were a few years ago) that you cannot enter formulas into reports or that you cannot reference reports for a dashboard. Is this still the case? I having living data that really needs to be manipulated to get it prepared for a widget. I'm new to smartsheet and it seems like reports are the only way to drill down to get to the end product I'm trying to achieve, but that won't allow me to reference from there for my dashboard. (My formulas are only working when very basic. I have a large living sheet, about 45 columns and MANY rows added daily)

Genevieve P.

Hello J,

To answer your first question, we don't currently support creating formulas from reports: any formula entered from reports will display as plain text rather than performing a calculation. (FAQs about Formulas)

As a workaround, you can create formulas in the source sheet. Then you could build a report to pull only the rows that contain the specific formula result you were looking for. Another idea would be to have a helper column in your sheet (for example, a hidden checkbox column) that you can use as the criteria for the rows you wish to display in your report.

 

Secondly, there is a Report Widget that allows you to display a report in your dashboard. It creates a window in to see all of the report information, is this what you were looking for? Please note that you must have Admin-level sharing permissions or higher on the source report to add it to a dashboard with the Report widget.

Another way to reference a report would be to create a Chart from a report. In this instance, the chart would select the entire report as the data. Or, if you only want to display specific cells via the Metric widget, these cells would need to be selected from a sheet, not a report.

If you are having trouble referencing your data within a dashboard after reviewing the Help articles linked above, it would be useful to know a bit more information in regards to what data you are looking to pull from your source sheet and how you want your dashboard to be set up.

 

Thanks!

Genevieve

Nick Burrus

Has there been any movement on allowing us to create formulas, or at the very least references to reports? For example I want to count the number of at risk tasks for a person across over 300 project sheets. However, this is a lot of manual work, versus something if we can make a report have a cell reference list (like column reference count) like we can on regular sheets, it'd magically be easy to do this.

Reports having formula capabilities, or being referrable in formulas/cell linking/references will lift Smartsheet Dashboards to the next level.

We don't want the new fancy appearances. We want functionality.

Genevieve P.

Hi @NBurrus

Yes! Reports have been updated since this Community thread was created; they now have the ability to Group and Summarize data. There are a set few formulas you can apply using the Summary feature and COUNT is one of them.

For example, if you set a Report to Filter for At Risk Tasks, then you can GROUP by the person, and use the COUNT Summary feature to count how many rows (across 300 sheets) are associated with this one person.

Here's more information on this: Configure Grouping to Organize Results in Report Builder

Here's a webinar you may find useful: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

helen helen

Hi @Genevieve P,

The group and summary function is great. I need to group and count rows in Reports by month based on a date field (where has specific date and time). Is there a way to do this? Currently, it seems it count the exact time rather than the month where the date belongs to.

(By the way, I cannot use achieve this purpose by creating new columns in raw sheet as the Card View of the raw sheet is being used by multiple teams where adding too many inferred columns that are not relevant to their work confuse them. And as of what I understand, there is no way to hide certain fields on a sheet from its Card View either.)

Thank you!

-Helen

Genevieve P.

Hi @helen helen

If you need to see all the months in one Report, then the way to do this would be to add a helper column in your sheet to extract just the Month from that Date column. Then you can Group by this helper column.

Example formula:

=MONTH([Date Column]@row)

I understand about not wanting to add in fields to confuse users in Card View, however this helper column would contain a column formula, which means it wouldn't show up as a "lane" and it wouldn't be editable from Card view.

You can exclude this field from the initial card view by un-checking it from Card settings:

Then if users go to Edit the card itself to see all the fields in the sheet, this column formula helper column will be un-editable:

If this doesn't work for you, an alternative would be to create multiple Reports where each Report filters by the Month, then you can summarize the entire Report as it will only pertain to that month.

Cheers,

Genevieve

Nick Burrus

https://community.smartsheet.com/discussion/comment/294775#Comment_294775

Awesome! I'm hoping this can be reportable to a dashboard in the future which can allow us to display key metics for example # of active projects, etc. Total # of at risk etc.

Nick Burrus

I submitted another enhancemnt request today in hopes that we can one day add formulas or use the summary data in reports to make text widgets that have live updating numbers.

For example total tasks in a report is 80. I want to be able to PUT that number 80 on a client dashboard so they can see there's 80 outstanding tasks. But, when the PMs add 3 new tasks, that number is now 83, the dashboard being hardcoded is out of date. Across hundreds of clients this is way too time consuming and it's holding us back.

We actually had to start researching another company for a government contract that requires this number! We're probably gonna have to go with Zoho entirely for this until reporting his this capability for that major client.