How can I create a report that takes user input before the report is generated?

Kurt Robohm · 03/10/23

I am looking to create a report that takes user input prior to running, and uses that input as a filter for the resulting output. Is this possible?

Simple example, I have a report template that I want to allow the user to put in a date to find all matching rows based on date, then generated the report from those matching rows. The user input would occur before the report is generated. Ideally provide some simple interface to ask the user for the date they want to search on.

Jeff Reisman · 03/13/23

@Kurt Robohm it's kind of messy, but doable.

First you need a sheet that's going to pull the values from your source sheet; this sheet will be the basis for the report. I'll refer to this as Lookup Sheet.

Next, your source sheet needs to have numeric-value RowIDs. Use an auto-number column with no prefix or leading zeros, if possible. It needs to be numeric.

On the lookup sheet, create a RowID column, a "Directions" column, Date, (or, if using a date range, add Start Date & End Date columns), and then Columns for the lookup data you're bringing in from the source sheet.

Enter a lookup date in the Date1 cell (top row).

Top row, RowID column, enter a formula to find the minimum RowID value in the source sheet that matches your criteria:

=MIN(COLLECT({Source Sheet RowID range}, {Source Sheet Date range}, Date@row))

RowID column, row 2:

=IF(AND(ISNUMBER(RowID1), (RowID1 + 1 <= (MAX(COLLECT({Source Sheet RowID range}, {Source Sheet Date range}, Date$1))))), RowID1 + 1)

This formula says: the RowID1 is a number, and RowID1 + 1 is less than or equal to the max row number in the source sheet that matches the date criteria, then set this cell to RowID1 + 1.

You'll want to copy this formula down the whole RowID column, for as many rows as the most results you think you'll have for one date. The RowID1 in the formula should automatically update to RowID2, then RowID3, etc., for each row as you go down.

Now you should have RowIDs starting with the minimum RowID for that date and ending with the max RowID for that date.

For your columns bringing in the data from the source sheet, use INDEX/COLLECT using the RowID value and the date value as criteria. (Need the date value in case you have different dates mixed into your RowID sequence - like if you want data for 3/12/23, and rows 1-10 are dated 3/12/23, rows 11-14 are dated 3/13/23, and rows 15-25 are dated 3/12/23 - you only want to pull in date for the 3/12/23 rows and skip rows 11-14, see?)

=IF(ISNUMBER(RowID@row), INDEX(COLLECT({Source Value range 1}, {Source RowID range}, RowID@row, {Source Date Range}, Date$1), 1))

Copy this formula down the column, then move to the next lookup column:

=IF(ISNUMBER(RowID@row), INDEX(COLLECT({Source Value range 2}, {Source RowID range}, RowID@row, {Source Date Range}, Date$1), 1))

In the top row of the Directions column, instruct your users as to where to put the date value. Mine is for a code, but you get the idea:

Now lock all rows below the first row, and all columns except for the Date column.

Create your report based on this lookup sheet, with a filter set to exclude any rows where the first lookup value column is blank, sorted by RowID ascending.

Kurt Robohm · 03/13/23

Wow, thank you for taking so much time to pull this together! I'll give it a shot.

Jeff Reisman · 03/13/23

https://community.smartsheet.com/discussion/comment/366998#Comment_366998

If I hadn't already done this a bunch of times, I would have skipped this question, LOL.

tmichel · 09/12/23

Hi... this info is very helpful, but I am consistently being hung up on the date range in the sample formula.

Our use for this is simply to enter a unique value (Ticket Number), and return the following info associated to that unique value: Description and status (date does not matter)..

Can you help with a simplified formula?

Genevieve P. · 09/12/23

Hey @tmichel

It sounds like a regular INDEX(MATCH should work for you!

Here's a Help Article that goes through how to build this:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations?_ga=2.106230662.461147055.1694529915-686803770.1684510742&_gl=1*1s2vqi1*_ga*Njg2ODAzNzcwLjE2ODQ1MTA3NDI.*_ga_ZYH7XNXMZK*MTY5NDU0ODEwNC4zMi4xLjE2OTQ1NDkyMjAuNDAuMC4w

So in your case, something like this:

=INDEX({Description Column to return}, MATCH([Ticket Number]@row, {Ticket Number Column in other sheet}, 0))

Cheers,

Genevieve

Jeff Reisman · 09/12/23

@tmichel To add to Genevieve's excellent advice:

INDEX/MATCH is used for when you want to find a cell value based on a single matching criteria, ex. "Return the Description from the row where the Ticket Number matches the Ticket Number on this row."

INDEX/COLLECT is used for when you want to find a cell value based on multiple criteria, ex. "Find me the Description from the row where the ticket number matches this row's ticket number and where the date is not in the past 40 days."

tmichel · 09/12/23

https://community.smartsheet.com/discussion/comment/395017#Comment_395017

Very helpful! Thank you!

Ken Y · 03/28/24

This really helped me out. I am a huge fan of column formulas and I found a method that allows me to use them. The idea is the same as above with the exception of the "Input" fields. Instead of placing them in the sheet, I used sheet summary fields. In my case, I wanted the user to select a Storage Type and a Storage Number and pull back the data.

Using this method changes the setup as follows.

RowID Column is just a sequence number. Create as many rows as needed.

Your next Column should be the first column you want to return from your Source Sheet. The formula would look like:

=IFERROR(INDEX(COLLECT({Source Column to Return}, {Source Column for Storage Type}, [Storage Type]#, {Source Column for Storage Number}, [Storage Number]#), RowID@row), "")

You can make this a column-based formula. For other columns, just copy and replace the Source Column to Return.

How can I create a report that takes user input before the report is generated?

Answers

Categories