Three levels of detail on single calendar view

edited 12/09/19 in Smartsheet Basics

All - 

We have data that is organized into a Target->Category->Item hierarchy, for example:

Target: Big Event in Las Vegas

Category: Digital Blogs

Item: Blog about XYZ

There is a "Target Date" for the top level Target, and an item "Due Date" for individual items.

I am trying to fashion the data in a calendar view so that execs will only see the Targets, managers the targets and categories (latest due date for an item within the category is the category date) and individual contributors can see all their items.

I have a single data source sheet that essentially is at the detail level, the data in the columns for Category and Target are replicated for however many items are common to them. I get this data as an extract from a project management tool and then import it into SmartSheet.

I have figured out a way to filter just those rows that are relevant to each manager level by incorporating new columns that contain an 'X' in the rows that should be visible for each user type. 

I can't figure out a way to create these three views this using one source sheet of actual data. Each type of view (a sheet view or report) MUST show the primary column. Therefore it seems to me I would have to have 3 sheets of essentially the same data, with different primary columns that show the data relevant to their viewers in order for the calendar views to work appropriately.

Is my only other choice to fabricate rows at the Target and Category levels and just have the data I want to show in the Target and Category views in the primary column? I think this will become a maintenance nightmare.

Help appreciated!