Relationship Report - collect multiple sheets' data to one row based on an ID
Options
Come up with a relationship type set up across different sheets in Smartsheet like how MS Access does with their tables. This will allow users to easily create reports using different sheets without having to have the sheets all mirror one another just for the information to flow properly.
You can set up relationships in MS Access across different tables. This way you can create a query that has different information from different tables all compiled into 1 Table, and from there create a report if you desire.
(Beginner Access Table)
(Intermediate Access Table)
(End Result)
In Smartsheet, you can't do that. If you try to create a report from 2 different sheets, this is the result...
Comments
-
In a report, would be great to be able to use two sheets to feed a report and reflect the information from both sheets on 1 single row, avoiding duplicates if the same project is showing on the two sheets. If the project is on both sheets, I'd like to merge them to the same rows. Right now, they are on separate rows, even if the column names and similar matches.
-
This would be a really neat new kind of report to have! We definitely have situations where the same participant ID is in multiple sheets tracking different kinds of work. It could be useful to have a report that showed one row per participant, with participant data from more than one sheet.
-
Hi!
I think it would be a great idea if we could merge rows from different source sheets based on a unique identifier in a report. I know Control Center does this in it's own way using blueprint summaries but this is different.
For example, I use the copy row automation from one sheet, to split into two different sheets. So essentially, this row or project is now duplicated. One sheet is used for financials, and the other for operations. Both sheets share standard project info, but differ in other columns respectively. If I want to combine the two views into one, I would create a report using the two source sheets. But, they appear as two different rows, which is totally expected!
I think if there should be way in the report settings to "Merge rows when _____" and input your own criteria. Like "Merge rows when (column name) Project Name are the same". This way I get to use both of the sheet's different columns, with the same row.
