Connect reporting tools to a smartsheet REPORT

Smartsheet reports is hardly what i would call "reports". It just combines data from multiple sheets into a single sheet based on creiteria. Great. But that is just a start. That is just raw data...there is very little insights you can draw from that and it's precisely the stuff management does NOT want to see (raw data).

What we need is to use the data from a proper REPORT (a chart, graph, or whatever). The problem is that all the reporting tools i've explroed ask for a specific sheet ID. That is completely useless. New sheets are created every day and we need a way to have a report include data from any sheet that satisfies the a criteria.

Smartsheet charts sort of does this and you can point it to a report, however the charts functionality itself is extremely limited.

Has anyone accomplished real reporting into a reporting tool using a smartsheet REPORT as the data source (not an individual sheet).

Thanks.

Find more posts tagged with

Reports

Comments

J. Craig Williams

Cousin Joe,

Have you seen the Sights feature?

It is an add-on but you can get a limited trial.

https://www.smartsheet.com/product-tour/sights

Craig

Joe Williams

Yes and i was very disappointed with it. It does not allow anything even remotely close to what i'm speaking of.

All sights does is display raw data. Management is not interested in spitting out raw data in a fancy dashboard full of useless raw data. How does that add value? Sometimes the designers of smartsheet are perplexingly idiotic. We need proper reports, which means interpeting raw data into meaningful information, not just spitting it out.

And no, we cannot have another sheet calculate the metric and use cell linking because that assumes in advance you already know which sheets you need to pull from. It does not solve the problem. People have suggested that to me in the past and all that demonstrates is that they don't understand the problem and probably have terrible metrics in their organization to begin with.

The key concept here is raw data can come from ANY SHEET, and we cannot know in advance which sheets because new sheets get created every single day. Everyone collects data into a view via a smartsheet report. And then you're stuck. The raw data collected into a report is not itself reportable into a metric. You cannot feed the data from a report into any tool that i know of except smartsheet charts, which is a very limited tool. Hence a severe limitation on any real reporting or analytics capability.

Sights offers no improvement from that situation.

JohnHinkle

Have you tried using the connections/integrations to conenct Smartsheet with Tableau, Power BI, or directly to Excel to use their charts/graphs features?

JohnHinkle

Or using the API to build a custom connection to another service to do what you are looking for.

Joe Williams

Smartsheet APIs and data connectors do not allow connection to reports, only individual sheets. I have already studied them in detail. No help there.

From my perspective, it should be painfully obvious to the people at smartsheet that this is a huge issue. I'm shocked, seriously shocked, that no one has brought this up before. Either everyone is doing exteremely basic stuff, or no one at smartsheet has used their critical thinking skills to realize they essentially have zero dynamic reporting capabilities. Wow.

From an enterprise metrics perspective, that is also basically a disaster. Any new sheets created will never get included into metrics unless the metrics report itself is updated to include the new sheets. What a waste of time/resources! People create sheets EVERY SINGLE DAY and we cannot run around looking for new data sources to include in reports, hoping we find them all. Only smartsheet reports can pull from all sources dynamically, and then you're stuck. You can't use the API to look at that report because it's not technically a sheet. I've tried.

The only solution i can think of is to literally auto-email the smartsheet report out periodically in excel, write a script to get the data out of excel and into a reporting tool, and go from there. All that work, and STILL the report won't be in real-time. This is absurd.

J. Craig Williams

I am having a hard time imaging an organization that needs to build NEW sheets every day.

That also sounds like a waste of time too.

But, it may your system is the right way and I just don't get it.

Craig

Joe Williams

It isn't hard to imagine.. people are building new meeting agenda/action item sheets for each weekly meeting, a new sheet for each active project plan, a new sheet for major event, a new sheet for quarterly department reviews, new sheets for complex construction plans, and so on. As long as they use pre-defined templates we have for those purposes, it should all work no matter how mnay sheets people think they need. Are we really going to tie the hands of our department managers, tell them they have to collapse all data into a single sheet (which quickly gets unweidly if you've ever tried it) - all because smartsheet reports suck? The system should be flexible to allow that people are going to have new sheets when they need them. Reporting should be dynamic and handle it.

Moot point though. The answer is it can't be done in real time and that's it. I did manage to connect the dynamic excel outputs to reporting tools. It only refreses periodically, but sure beats basically having no robust metrics at all.

What i don't understand is how other organizations think they're doing "reporting" when they can't even gaurantee all relevant users smartsheets are being included in the data feed. They must be doing either something extremely rudimentary or (probably more likely) have a completely busted solution and don't even know it.

J. Craig Williams

Joe,

I set up a very limited set of meeting agenda sheets for each project.

Often ONE.

Not one per week. but one for the life of the project or particular phases or deliverables.

When the meeting is over, the current agenda becomes the meeting minutes.

When the minutes are approved, the sheet is exported and attached back to itself as the previous meeting minutes.

The minutes are then cleaned up and becomes the next meetings's agenda.

I am currently starting one new project every few weeks (that I'm in lead on).

Even when I was very busy, I was starting one or two per week.

Those are created from the master/template.

In fact, the whole workspace is created from a mastert/template.

The meeting agenda is also the action item list for items that are determined from the meeting. The reports key off anything assigned to someone that is not complete, regardless of which sheet it is it.

There is a place for action items determined in the current meeting and action items from previous meetings. Completed action items are on the agenda until discussed and then archived (see above).

This is not an administrative burden unless one considers being prepared for a meeting and following up on items raised a burden.

I've been using Smartsheet for nearly 3 years now and do not consider my system busted. I have reported to executives (I used to report to the CEO) and have not heard 'well, I wish I had known THAT' very often or 'can you give me less information please'?

If you are creating everything over and over again, perhaps your system is the one that needs to be analyzed for ineffiencies.

Craig

Joe Williams

Craig,
You decided to insult and not focus on the issue at hand. I’ll return the favor, while still focusing on the issue.
Your description of agenda management is rudimentary… As is your perspective on enterprise work management.
We’ve used a much more sophisticated, yet static agenda sheet since day 1. After moving past your first 4 paragraphs of fluff, I realized your passive insult of “consideration of burdens” would be the highlight of this latest correspondence. So instead I was inspired to respond, even though my first review of this was 2 months later.
Craig, please tell me, what’s easier than opening a smartsheet template & setting the date on a milestone that triggers the dependencies of the recurring operations plan, special event project plan, new construction site design|build plan, on boarding/training employee plan, quarterly cash flow schedule, etc.. ???
A single operation could require 4-10 new sheets per week, then scale to 20 operations. It’s obvious your experience and brain hasn’t conceived those ideas. Mine has.
Despite your impressive 3 long years working with smartsheet, you still haven’t been exposed to enterprise work management system requirements. Your systems may not be busted, for your perspective. I’m afraid your understanding of scaleable systems is highly limited and you still don’t know what you don’t know.

Kennedy Stomps

Joe, I understand your frustration and have passed your feedback along to our Product team directly. In the meantime, we do have a Pivot Table application that can be purchased as an add-on to Smartsheet that may assist with your reporting. If this is something you would be interested in, please let me know via email at Kennedy.Stomps@Smartsheet.com and I will personally connect you with a Sales representative for more information about this feature.

Additionally, while I appreciate the passion in this discussion, I think that the conversation has started to become personal and unproductive, so I'm going to close this discussion to futher comments.