Grouping Reports by Specific Field, but with multiple sources

[Najoseph97]

Hi all, Appreciate any help or guidance you may have. I've searched through the community page and googled a ton, but cannot seem to find a solution.

I am building out a monthly reporting automation. The goal of this report is to bring in data from 4 unique data sources, group it by Business Unit, and then send out unique Business Unit-specific reports/updates to the relevant BU leaders. 

I was able to build out the report so that I have a final report with the 4 sections (data table and text). However, I'm realizing that I am unable to filter this by the Business Unit. All the resources I'm seeing are saying to use a join or union tool, but because they are 4 separate data sources I'm not finding a way to make this work while still making my process work. Most of the data is unique except for the Business Unit which appears in all 4 data sources. 

I've attached the workflow here. I know its probably super mess, but I've been teaching myself as I go. 

If anyone has any idea of how to make this work, please let me know. I really appreciate any guidance! 

[Qiu]

@Najoseph97 
Can you attach the Input file if possible?

[KGT]

In order to filter by Business Unit, you would need that field to still be in the data, and so selected as a grouping field at each table/layout etc.

[Najoseph97]

Hi!

Yes, here are the relevant attached files in the workflow.

[Qiu]

@Najoseph97 
I think you need to figure out how to join these 5 report together for something you need. then we can output report based on the BU.
I am sorry but I was not able to figure out the relations between these 5 reports.

[alexnajm · Accepted solution]

As @Qiu mentioned, there needs to be consistency in what column you join on seemingly - if its business unit, make sure it persists throughout!

@KGT provided a great suggestion down this route - grouping in your Reporting tools will help

[Najoseph97]

Hi again! So I was able to reconfigure my workflow so that all 4 of the input files are bringing in the Business Unit field throughout the workflow. My next question is: do I have to join before I create the tables or after? I created the tables and am attempting to do a join, but I'm not really able to do much with my report then. If I group by and select just one of the 'Business Unit' fields, its not brining in the full break down. But if I filter by all of the business units, its not bringing in the correct data either.

[alexnajm]

@Najoseph97 it is really hard to continue helping with screenshots and such a large workflow. So I'd recommend posting workflows for the best help

It is also hard to guess what you want your end product to look like, so can you sketch it out for us?

Otherwise off hand you need to group at the top of your layout - that's where you can get it by business unit

[Najoseph97]

@alexnajm I have attached the updated workflow below. 

For additional context, what I am trying to do is create an automated email report for some business leaders, where I'd bring in business-unit specific data into that email. The way I have it set up, I have the 4 relevant reports being filtered by business unit - so I know the end data is filtered/separated correctly. But what I'm running into is how to then bring those relevant 4 sections together in an email. 

I know in the report function you can do a 'group by' - but I can't seem to get it to look good. 

I know you and @KGT mentioned grouping at the beginning. When I try and group its coming out funky and then limiting my ability to run additional formulas (like in Section 3) where I have % breakdowns for Business Units from that specific data input file. 

I also attached a quick little mock up of what the email would look like when you brought the sections together. 

Any ideas on how to make this work?

[Najoseph97]

As a final follow up after a bit of work and plenty of trial and error, what I did was combine all 4 inputs with a join, and then added a4 'select' tools in order to pull out the appropriate data into each of the groups. Ran a summarize tool in each of the subsequent 4 workflows in order to group by the appropriate business unit, which then allowed me to bring it all together via a union tool at the end with business unit being the shared 'group by' field.

Appreciate everyone's patience and help as I navigated this beast as a new Alteryx user.