Monthly Formatted Reports

Hello!

I'm trying to schedule my Alteryx workflow so that it uploads a new file for every month, formatted the same way as my template.

Process:

- Each month, my SQL query pulls data from the previous month.

- On April 1st, it would pull all of March 2024. The "Date pulled" column will say 4/1/2024. The file name will be 'User Testing_March 2024' and the sheet name would be March 2024

- On May 1st, it would pull all of April 2024. The "Date pulled" column will say 5/1/2024. The file name will be 'User Testing_April 2024' and the sheet name would be April 2024

I have attached two sample excel documents where the format will be used. This is my template document. I want to make sure the headers are bold, date of birth and date pulled are date format. I also want to make sure the report is always left justified.

Every month, I want it to overwrite to this format so that I don't have to keep going into it monthly to update the format.

I was able to create the below columns using the "Date Pulled" column.

Month/Year = DateTimeFormat(DateTimeParse([Date Pulled], "%Y-%m-%d"), "%B %Y") which gives March 2024.

FileName = 'User Testing_'

FileName + M/Y = [FileName] + [Month/Year] which gives User Testing_March 2024

Any idea how I can accomplish this?

User Testing_March 2024.xlsx

User Testing_April 2024.xlsx

Workflow

Output

Custom Tools

Macros

Common Use Cases

Accepted answers

abe_ibanez

Hi,

I think I figured it out.

You have to use a layout tool between the table tool and the render tool.

Table configuration:

Layout Configuration:

Render configuration:

All comments

abe_ibanez

Hello,

I have a similar report that we run monthly.

For the query, I calculate the start and end date of the data that I need and then use a dynamic input tool to update the query:

Here are the formulas I use for the dates (we query data for the last 3 months):

TODAY = DateTimeToday()

START = DateTimeTrim(DateTimeAdd([TODAY],-3,'MONTHS'),'MONTH')

END = DateTimeFirstOfMonth()

In my query, I replace the filters with the start and end date where data_date >= START and data_date < END.

For feeding the data to custom excel files, I create a variable for the file path instead of just the file name:

path = '..\reports\Customer_Concern_Resolution\'+[REGION_CD]+'\'+[DISTRICT_CD]+'\'+[report_date]+'\'+[DEALER_CD]+'_'+[report_date]+'.xlsx|||Sheet1'

In the output tool, I tell it to replace the entire file path:

I hope this helps.

mystasz

Hi - Thanks for your reply. In our output, what file do you put as the 'write to file or database?'. How would it know to use the template for the previous month?

mystasz

I don't think you can use a basic output while using the reporting tool to format the table. Once the table is formatted and I link it to the output, using the configurations you previously mentioned, I get a column with value like something below:

</style><datatable style=""border: 1px solid #000000;"" width=""100%"" cellspacing=""0px"" cellpadding=""0px"" rowGroupSize=""1"" rowsContained=""2""><views><dataview></dataview></views><columns><col/><col/><col/><col/><col/><col/><col/><col/></columns><thead><tr class=""headerRow""><td class=""column0 Rule0"">NAME_ID</td><td class=""column1 Rule1"">NAME</td><td class=""column2 Rule2"">DATE OF BIRTH</td><td class=""column3 Rule3"">ADDRESS</td><td class=""column4"">CITY</td><td class=""column5"">STATE</td><td class=""column6"">ZIP</td><td class=""column7"">Date Pulled</td></tr></thead><dbody><dr><cell class="" column0 DefaultNumericText"" style="""" >855511</cell><cell class="" column1"" style="""" >MASON SMITH</cell><cell class="" column2"" style="""" >2012-08-04</cell><cell class="" column3"" style="""" >865 PEPPER STREET</cell><cell class="" column4"" style="""" >New York</cell><cell class="" column5"" style="""" >NY</cell><cell class="" column6 DefaultNumericText"" style="""" >11007</cell><cell class="" column7"" style="""" >2024-02-01</cell></dr></dbody></datatable></div

abe_ibanez

Correct, you need to use the rendering tool whenever you use the reporting tools.

Here is an updated workflow that would write the data to the same directory where the workflow is saved.

For the automation, you would need

to select GROUP DATA INTO SEPARATE REPORTS and replace the path with where you want that month's report to be saved.

mystasz

Hi - What exact configurations did you use on the render tool? I selected the below and got "Error: Render (14): The file "C:\Users\Box\File Format Test" is not a supported format."

Output Mode: Choose a specific output file

Output File: C:\Users\Box\File Format Test

Checked box 'Group Data Into Separate Reports'

- Field to Group On = Path

- Modify Filename By = Replacing Entire Path With Group

My path value is "C:\Users\Box\File Format Test\User Testing_February 2024.xlsx|||February 2024" which was created with a formula.

abe_ibanez

Hello,

It looks like the Render tool does not like having a sheet name specified. I had to remove the "|||sheet1" for it to work.

Path = 'C:\Users\user\Documents\test_file.xlsx'

I left everything else as you have it above.

mystasz

Yes - That did the trick. I don't think this solution would work 100% since I need to provide sheet names. It definitely works on the formatting but I'll play around with it and see if there's anything else I can do to get the sheet name.

abe_ibanez

Hi,

I think I figured it out.

You have to use a layout tool between the table tool and the render tool.

Table configuration:

Layout Configuration:

Render configuration:

mystasz

This works perfectly, thanks so much.

mystasz

@abe_ibanez - With the approach we took for this solution, we’re technically putting text into a table within excel which converts all fields to string.

If there was a date column, do you know of a way to retain date format? It seems like all dates switch to string when using the render tool.

I currently have to go into the newly generated excel file and change all string dates to date format using a formula. If you know of a better way, let me know!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335