Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
I’m finding lots of great tutorials and help in the community, but I may have read too many posts!
What I have is a need to create a report that is going to grab various elements of customer data from different queries, manipulate the data and use it to create a report. Then it is rinse and repeat for the next customer. Some customers will have very large reports with 2 or 3 thousand rows of data. Others may only have 20 rows. I can get the data for any one customer.
What is the best way to modify the customer ID and date range in multiple queries without actually editing each query every time. I foresee a time when I have to run this report for 100 - 200 customers multiple times a year. Would the interface tools be best? I tried a small test and it seems really slow, but I may not have the best workflow.
I’ve been asked not to describe actual data by my manager. A similar report might be a grocery store with distributors selling goods on different aisles. I need to list out the distributor, the aisle, potential products to be sold on each aisle, actual products available and number of units sold. Then from a different source the number of customers that say the product is a favorite of theirs. I’d like to update a list of distributors, run the workflow and get a report for each distributor
The enclosed package is a portion of my workflow, enough I hope to show what I am trying to do. I saved sample data in excel tables, but in practice I am using a queries to generate the data. Each query has a line ‘where group ‘1233’. The reports are going to be based on the group value in the s_funding_table (1233, 1234, 1235 etc). I am going to receive a list of 100 – 200 group values on a regular basis.
For each value I need to:
Update queries for a specific group value ie 1233
Run the queries to import the data
Edit data and output results to excel (I used customer summary, order summary and order detail in my sample, there are others, but once I get these three, I should be able to add the others)
Save the excel file with the reports as tabs to a shared folder
Move to next group value and repeat.
For any one group value I can manually update the queries and get the desired reports. I have two specific questions.
1) How can I automate the process of updating the queries given a list in excel?
Is this better as a macro or ?
2) How can I add the group name to the file name
I think it’s in the Output change entire file path, but I didn’t get it to work.