Feature request: Enter formula in report

Stan Ward

I know that Smartsheet does not currently support creating formulas from a report. Any formula manually entered in will display as plain text rather than performing a calculation.

 

I am not sure how the would work, but it would be nice to have a way to enter a formula into a report and have it calculate.  I envision this as maybe being the opposite of using the single quote character to prevent a formula from being evaluated.  Instead, there would be a reserved work to instruction a report to perform a function, something like FUNC() or REPORTFUNC() which would then evaluate the formula in between the parenthesis. 

 

That is, for a Smartsheet Sheet, if you type =1/2 in a Text/Number column, it  appears in the cell as 0.5.  If I do that in a Smartsheet Report, in the same cell as the sheet, it appears as the text of =1/2 (that is, equal sign symbol, the numeral 1, the forward slash symbol, and the numeral 2).  And if you go to the sheet after typing =1/2 in the cell, it appears as '=1/2.  I understand why that make things easier for making Smartsheet work.  What I want is a way to override it without needing to go to the sheet to enter the formula.

 

Maybe it could be entered as REPORTFUNC(=1/2) and Smartsheet would know to evaluate that simple formula.

 

I guess the complication comes when you enter formulas with ranges.  What range are we talking about?  In the report or on the parent sheet for the cell.  Interesting challenge.

 

Anyway, just some thoughts to maybe get the creative juices going among the community.

J. Craig Williams

Stan,

 

I'm playing devil's advocate here and bouncing this off my understanding of how Smartsheet works. 

While the range is an issue (and a big one), I think more importantly might be that the data in the Report comes from different sheets.

Add that to the fact the the cell we want to put the formula in (and I could use this feature too, don't get me wrong) is in one of those sheets, there are so many exceptions that I would hate to be the one on the support desk when this rolled out.

1. does the column referenced exist in the sheet where the formula resides?

2. what do I do about row references? they don't come from the sheets, they come from the rows in the reports

etc..

I think MOST of the functions would have a problem or two. Or many.

So then we are left with what? If I can't trust even a [ColumnA]23+[ColumnB]23 calc, what am I going to do about COUNTIF?

The list becomes very small of useful functions when I can trust the references to the cells. 

And then, the data set is dynamic. If something changes (while I'm looking at a Report, a colleague is updating a sheet which will change the results of my formula) there's not even the little reminder that I need to refresh my sheet -- because the data is not "live" like it is in a sheet, it is built and then becomes a snapshot of the data when I opened the Report.

This is a design decision that won't be easy to modify. 

I imagine that they will need to provide a different methodology than the current Reports to give us what we want.

But I could be wrong.

 

Craig

 

Stan Ward

I agree with you Craig.  This is probably not easily done and, to your point, maybe not something wise for smartsheet to do. 

 

One additional thought.  There are a handful of things that could be done in a report that do not have the dependency issues.  Currently, if I type the letter "t" on its own in a date field in a report, and hit return or tab out of the cell, it converts it to today's date, as it also does in a sheet, so that is functioning in reports.  I am wondering if being able to do a simple computations (e.g, =1/2) or intra-row functions would be possible. I know it is a different mechanism, just putting it out there.

 

And then there is being able to do computations for reports results, e.g., SUM of a report column, which has ben discussed here, started by me, trouble-maker that I am, and maybe not always taking to heart the design principles/decisions that Smartsheet is built upon: https://community.smartsheet.com/discussion/want-sum-calculation-report-formulas-reports

 

In the end, this is a Nice-To-Have feature for me, not a Should-Have feature, and definitey not a Must-Have feature for me.

 

Cheers,

Stan

J. Craig Williams

That "t" feature is a goldmine. I did not know that!

 

I think there are limited features that could easily be developed for reports that would not necessarily impact the design. Again, not privy to the inner workings, I'm only extrapolating from what I know.

 

For the SUM column, why does it need to be in a cell?

If I highlights cells in a sheet, I can see the COUNT, SUM, and AVG (a direct homage to Excel). Three rows that could be hidden or shown at the top or bottom for these in a column would at least give that information to the user. 

 

Craig

Kennedy Stomps

Thanks for this feedback, Stan and Craig. You're right--this is something that could be very tricky to implement well, for all of the reasons you've listed (and likely more!) However, we know that a number of customers are looking to be able to calculate values "automatically" based on the data pulled into reports, so it is something we are considering for future development and I'll add your vote, as well as pass on a link to this post! 

J. Craig Williams

Thanks Kennedy.

There's already some of that functionality built in (I'm thinking of summing the resources for a particular day in the Resource View), so there is code that might be leveraged to get started.

 

Craig

MW

Not having the ability to sum, count, average, in reports is almost a dealbreaker for me.  The dynamic report data is awesome, but not being able to analyze it is terrible.  

 

Also, one cannot link data in a report to another sheet (use the report data as a database), correct?  If I have to hard-wire (manually link) sheet cell data together in another sheet and call that a report then I'm probably looking for a different solution (like excel).

 

Trying not to troll here, but what am I missing?

J. Craig Williams

MW 

 

You are right. Smartsheet is not a database. 

It is a collaboration tool that has useful features like Gantt charts, reports to consolidate data into a single view., and others.

Excel has a lot of power -- but it isn't a database either.

You'll need to link data together in that program or others like it too.

 

You are correct to evaluate any tool vs your requirements. Without seeing those requirements, I can't argue for or against having Smartsheet as part of your solution.

 

I hope you find what you are looking for.

 

Craig