I am trying to create invoices from a data set of customers and invoice sublevel lines that is cross tabbed by project code numbers where the values are sum of billable amounts. I then output the data into separate excel files based on customer names.
The problem I am running into is that the cross tab list all project codes, but not all customers are associated with each project. Currently the output list all project codes, include the ones the customer is not involved in. Therefore I am hoping to have a dynamic way to filter out null columns for each customer before sending to separate excel files.
The trick is it has to be dynamic, because customers change and project codes change each invoice run. I already know how to output to separate files so please focus on the dynamic filter part.
Hi @jkell - you should wrap your cross tab and output into a batch macro. This will create the right number of columns per batch (customer) and only include the correct projects. I've attached a really simple sample, please use this as a guide only.
To be clear, you should not cross tab your data prior to the batch macro.
@Blake Thanks for your response! This simplified version is what I'm looking for.
In order to make use of this I my more complex version, I have a batch marco configuration questions for you. It looks like macro input tool has only 2 input options, text or file. Is there no a way to have it configured to a preparation tool (sort)?
I have proceeded under this assumption and used a block until done to output the workflow to a file. Then 2nd output goes to batch macro and uses the file as the input.
The way I'm doing it works just fine but wondering if this is the way you'd proceed in a more complex workflow?