How to create report with rows instead of columns?

ShinyLife

Why does Smartsheet only allow you to make a report with columns and not rows? I'm using my sheet to coordinate data for events. Each row has a specific event, each column has different details for the events (date, location, hotel, meals, etc) and I need to take information from only certain rows to create a report for each event, not have columns with every event's information for the whole year. Is there a way to do that?

I can kind of do what I'm wanting to do with the report using a dashboard instead. If I use the metric dashboard widget, can select it to display data from certain cells. However, when I add a form to my master smartsheet for my client to enter information, it creates a new row for the event with the information they entered. Is there a way to automatically add the new row's data to the dashboard so that I can see all of my information for one event that automatically populates? Or do I have to manually enter information from the new row? 

Paul Newcome

There are a few different solutions that come to mind.

 

Are you able to post some screenshots with sensitive/confidential information removed/blocked/replaced with "dummy data"?

ShinyLife

To better explain, I'd like to be able to create a dashboard with all of the information for a specific conference consolidated in one place for quick and easy viewing rather than scrolling through a giant sheet. The test dashboard I created in the screenshot only has one widget (I'm using the metric widget because it's the only one that allows me to select a specific row rather than all of the rows on my sheet since I can't create a report with rows). On my completed dashboard, I would have several widgets with different information in each (i.e. staff meals, shipping info, etc.)

I send out forms to people to provide additional info for conferences, but Smartsheet creates new rows each time a form is filled out and they don't automatically show up on the dashboard in the widget, I would have to manually go and insert all of the information into one row for it to appear in the widget. I need the dashboard to automatically update the new information that comes in via forms without me having to take extra time to do it manually every time.

Another thing that could make life easier is to create a dashboard where people can edit the sheet right from the dashboard rather than sending out forms, but I can't figure out how to do that, or if it's even an option in Smartsheet. Any thoughts?

ShinyLife

I added a comment with screenshots.

Andrée Starå

Hi,

To have an editable sheet in the dashboard you'd add it thru the web content widget.

Would that work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

You'd use the Edit by Anyone publish option and copy the link and use that in the web content widget.

Paul Newcome

We have lots and lots of options. For the metrics widget, it is going to depend on the overall setup of how things are updated.

 

Would you like people to update the sheet directly or would you rather they add information and the latest data for each event gets compiled in a different sheet?

This second option would allow you to pull the data for the metrics widget that will automatically update on the dashboard with the latest data because that's what the cells would be populated with.

 

But the first option would allow you to have a single row for each event already where people would just populate the data.

 

To give access to the sheet as a whole, you have a few options.

1. Publish as edit by anyone and send out the published link.

2. Publish as edit by anyone and use a Web URL Widget on a dashboard to display it alongside the key metrics and then send out the link to that dashboard/share the dashboard.

3. Share the sheet directly to people.

4. Publish the form on the dashboard next to the key metrics. Have all of the data compiled in the background (off of the dashboard) so that they can submit the form right there and it just looks like they updated the metrics widget.

.

There are so many different options that it really depends on your end goal.

Who will be updating the sheet?

Who will be viewing the key metrics?

Who ACTUALLY NEEDS access to the sheet?

How would you prefer the sheet get updated? Directly? Via form where they can't change already entered data? Update Request where they basically access the row for a specifically requested event and can change anything they want for that event?

.

Reading through again... It seems like if you were to replace your form that you send to people with update requests, they would be able to update the same row without having to compile the data, but that gives a chance for someone to overwrite previously entered data.

 

ShinyLife

I don't want to create a new sheet for each event. I already have all of my events for the year on one sheet. Sending update requests is too cumbersome for the people I'd be sending it to because this sheet is massive - like 150 columns and 100 rows. They would get frustrated having to scroll through all of the columns and rows when they perhaps have 10 cells of information to fill in. That's why I like the forms, I can ask people for information in smaller chunks. I just don't like that the forms create a new row for each form that is filled in because that information won't update in the dashboard since it is on a different row. It seems like it would be easier to have an option for people to edit information directly from the dashboard widgets that would populate on the sheet. Or that a report widget would let me select rows rather than columns. It's endlessly frustrating to me that I can't create a report where I can select certain rows.

 

Paul Newcome

My apologies for the confusion. I wasn't meaning to create a new sheet for each event. If you would like to point out where that particular miscommunication came from, I'd be more than happy to explain.

.

When sending update requests, you can specify which rows get sent by using conditions and you can specify which columns get sent in the advanced options.

.

There are actually numerous options to allow people to edit through a dashboard widget. Most prominently would be publishing the sheet and then using a Web Content widget.

.

If you wanted to continue using forms, there are a few different ways you can compile the information. The easiest that I can think of right off would be to use a second sheet that has each event listed once in it's own row. You could then use a series of basic cross-sheet references to pull the most recently added data for each event for each field to keep an easier to read list up to date.This can also be done on the same sheet.

.

As for selecting rows for your report...

 

I am not sure what you mean by not being able to select certain rows. As long as you can establish a set of criteria for which rows you want to pull, you can specify that criteria in the report builder and only pull the rows you want.