Group Individual PDF Reports by Region

[djodts]

Hello, 

I have a workflow (and attached a very simplified version) where I am able to run a batch macro to print a PDF for every store.  In the attached example (if you want to try, you need a C:\Reports folder) a report will be printed for each store including the regional roll-up which is simply an aggregation of all stores for that region.  What I am looking to do is have the region print as page 1, then the subsequent stores printed and attached as page 2+ for as many stores as there are for each region.  One PDF may contain 6 pages while the other contains 25.  Is there a dynamic way of doing this? 

My problem is that the data that I am feeding in my live version is that the reports will look slightly different (always one page) because it sorts and ranks certain expenses vs last year so from one store to another, the report data will be different.  This leads me to only want to feed one store's data through the calculation/workflow at a time via the batch macro, which of course generates one PDF at a time.  This is great for sending to each store.  But for a regional director who wants to see the region along with all other stores, I am trying to get these all compiled.  Of course, it would be easiest for me to print them all and then have a second workflow kick off that would merge them, but not sure that's possible.  

Another possibility, that I don't think Alteryx has, would be to have some logic so the workflow knows which regions are together (and that the region is to be printed first, then the associated stores), to print them, put the individual pages in a temp staging area, then to combine them in the render tool, but I don't know if that's possible either.

One last thought, and this is where I'm hopeful that someone more sophisticated than me could provide a solution...

Below is a simple example of how the data flows.  Data goes through some logic and then of course prints a PDF... the container would of course be a macro.  I could remove the render tool at the end and insert an output tool and then create what you see in the workflow below it where I would first feed (of course adding some filters and logic) the Regional data, then the stores to the subsequent macros and then have all of those feed to a render tool outside of the macro.  The problem with this is that it's not dynamic and in some instances I might have 7 stores...and in some I could have 200.  I just cannot think of how I can cycle through a set of stores and have those PDFs held until the group is done, print, then go on to the next cycle.

I would appreciate any suggestions.  I think if I can get it to work in the attached simple version, I could get it to work in the much more complicated one that I have.

Thank you

Doug

[joshuaburkhow]

Did you mean to attach a workflow example or are you referring to the image only? 

[djodts]

@joshuaburkhow 

Thanks.  I did select them before, but they clearly didn't upload.  The should be there now.  Thank you for letting me know!

[danilang]

Hi @djodts 

A large percentage of problems that people attempt to solve using macros can actually be solved in a single workflow with strategic application of grouping and summarizing functions.  Your case is one of those.  

Your per-store reports (bottom branch) can all be created by grouping by [Location ID] in the Basic table tool.  Add the filename and render the reports.  The top branch generates the region reports.  First add the [revenues] and [expenses] grouped by region.  Calculated the [Net ]for each region and create the tables that will be used as page 1 of your region reports.  Since you already have the individual store tables created in the bottom branch, add a summarize tool that combines all of the tables for each region using the rarely seen Combine Vertical operation.  Join this to region tables and use a layout tool to combine the region and combined store tables into one layout per region with section breaks.  Add a file name and render.  The region reports now have the region info on the first page followed by the store reports

Note that I didn't need the Store List table because the region information was already in the data table

Dan

[djodts]

@danilang 

Thanks for your reply and the workflow.  On my first run through this (and I'll keep looking at what you uploaded to see if it sparks anything), I think I uploaded too simple of an example as I agree that your solution would have worked if I just had some basic/consistent accounts in my report such as total revenue, total expenses, net, etc.  I have used the filenames to generate and parse reports into their respective files so I can see that.  What makes this challenging is that because of the P&L report that I need to generate is in such a specific layout (multiple tables) and more importantly that there aren't static fields but rather dynamic fields that sort and rank year over year expenses each store's data needs to be fed through one by one hence the batch macro.  I don't believe there is a way to feed all data into the workflow of calculations at once mostly because the report pulls in the top 14 increases in expense variance and in other places the top 14 decreases.  This drives changes in the expense account number and name.  I get that I could still do the same types of calculations by grouping by location, but when I enter the fact that there are very specific layout templates that I have at the beginning, this poses a problem for me as the layout is being "filled" as the data is going downstream via lookups and so on after the sorting.  All that to say, I truly believe that I have to feed one location's data in at a time.  Unfortunately, I cannot upload the actual workflow or any of the real data, but to give you an idea...this is what is actually within the macro doing the calculations.  The red circle is where the data comes in and obviously at the end where the PDF is created.  The orange circles are all of the various layouts/templates to make formatting much simpler as there are a lot of changes line by line.  Not attaching this to have anyone try to absorb what is going on here, but just to illustrate why I feel the need to feed the data one location at a time.  If there is a general change in approach, I'm definitely open to learning...just having a hard time seeing another method.  Thank you again!

[danilang · Accepted solution]

Hi @djodts 

You should get the Top "Find Replace" User award!

There is no reason why you should't use a macro it the use case requires it.  In your particular example you already pass the reports(minus the footer) back out to the calling program.  If you remove the render tool from inside the macro, your should be able to pass your (almost final) reports to the calling program, along with output paths of course.  Since the batch macro will union everything together before returning, what you'll get is list of reports back in the main program. You can then group them to produce your regional reports and then render the regional and store level reports from the main workflow.   You may need to add another output to the macro to return the massaged store data so that your main workflow can create the amalgamated reports.  You'd have 2 output streams, one with the reports per store and another with the massaged data per store.  The reason to split them is to minimize the size of the returned record set.  You can send all the data out one output, but then you get each store report duplicated for every row of store data that you send back

Dan    

[djodts]

@danilang 

Thank you very much.  I was thinking something along the same lines but couldn't quite envision how the end would work.  I'm working on creating a smaller set of test data and will let you know how it works out.  I appreciate the additional insight!

[jacob_kahn]

@djodts,

This is one of the most fantastic workflows I've seen! Beautiful!

Jacob

[djodts]

@danilang 

Thank you again for your time and insight.  This worked perfectly!  I had to adjust my formula for the file naming so I would only get the regions.  I then, as you probably guessed...added another find/replace 🙂 so that I could add a sort order for the pages and bam!  This is super helpful and so much easier than I anticipated.

[djodts]

@jacob_kahn  Thanks.  I'm glad someone appreciates it because when you spend weeks working through this and reports are printed and posted globally, no one really knows the birds nest that this stuff goes through to create that one PDF.  

It certainly was fun to create!