Monthly Formatted Reports

[mystasz]

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?

[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?

[abe_ibanez]

For our reporting, we actually use a Python script that uses openpyxl to do all the formatting after we create the new files. 

If you want to do it all within Alteryx, I would recommend doing your formatting with the Reporting tool. You can use Table tool to define your styles (Bold header, Left Justify, Data format, etc) and then output the formatted table to Excel. 

 

[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 · Accepted solution]

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: