Can some one point me in the right direction?

[RPettit]

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

[RobertOdera]

HI, @RPettit 

From what I can understand:

1. If you're one superuser creating scheduled reports for multiple folks (multiple e-mails), you might want to explore batch macros.

2. If you've got multiple self-service users and you want them to generate their own reports upon demand, you might want to explore an App.

Kindly create super-light, washed (+generic Field Names e.g. ColA, ColB), sample file inputs based on your Sources scenario.

Kindly explain what dimensions, values, and parameters need to be updated to meet your use-case.

That way, you will crowd-source approaches that you can scale (because for the most part, the mechanics will work just as well for 5 records as for 5000 records).

Still, be sure to emphasize that the ideal flow needs to be dynamic (again on the dimensions, parameters that need to update, scale).

The better the question, the more likely we move from making the solution possible (brute force), making it work, making it better to a shell that you can use!

Cheers!

[RPettit]

Thank you, I'll work up a sample.

[RobertOdera]

You're most welcome, @RPettit 

[RPettit]

Thank you again for the willingness to assist me.

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.

Richard