Dynamic column filter to separate excel file output

Question

Hello -

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.

See attached example.

Thanks!

Dynamic Filter - Columns.yxmd

Blake · Accepted Answer

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.