Automated Report Generation

[falkuwari]

Hi all,

I've been looking for ways to automate wellness reports I'm working on. However, I have no experience in automation or script writing whatsoever...

I was recommended by a colleague to create a master document in MS word, and automate using MS access. I tried to look up tutorials, but I couldn't figure out what to do since all were in table forms rather than text and figures.

The reports are working on consist of text and figures and each figure is for a risk level for different diseases (normal, lower risk, higher risk), each participant will have different results and I want a way to automate report generation rather than do it manually.

Hope you can help with this, thank you!

 

[sykes]

Hi, and welcome to Mr. Excel!

If I've understood your question correctly, this sounds like it's a perfect job for mail merge.
It's a way of getting data from a data source (Excel, Access etc) and populating another document with that data.
For example, if you have a list of 15 participants, you could list their names down a column, in Excel (the top row would normally be the "Header" row where you name each column).
In the next column, you could list their associated IDs.
Next column, disease risk level.
Etc.

If you now open a Word document, and find the "Mailings" section you can start a mail merge - using the wizard, if you've not done one before.
Under the "Select Recipients" section, select to "Use an existing list" then select your Excel sheet as the data source. This makes a data connection between the two documents.
Now, in your Word document, you can "Insert a Merge Field" which allows you to select from the column headers you used to name your Excel columns. This connects that "Field" to all the records in that column in your Excel file; you can then do the same for any of the other fields you want to draw the data from into your document. Normally the field name appears in your document at this point, but if you want to see what it'll look like when complete, you'll find a button somewhere which says "ABC Preview Results" and selecting this will toggle either the field name, or the first record from that column of data. There'll be another button which will then allow you to step through all of the records in your Excel "database."
Once complete, you can choose to either print off the documents one by one (stepping through the records you want each time) or, you can complete the mail merge, which will generate a separate report page for each recipient - so in this case you'd end up with a 15 page document (assuming that your original document was only a single page). You could then choose to print them all, or only the ones you wanted etc etc.
You can also choose to only merge certain records as well.
As long as you save your document before closing, when you re-open it, you'll be prompted as to whether you wish to reconnect to the data source, which will update any changes you've made in your excel data etc.

This sounds like what you want, and will save a huge amount of time - for a little initial effort.

This will give you a taste of what you can do - just research mail merge on the internet, and there'll be a whole ton of help on it.

 

[falkuwari]

Hi, and welcome to Mr. Excel!

If I've understood your question correctly, this sounds like it's a perfect job for mail merge.
It's a way of getting data from a data source (Excel, Access etc) and populating another document with that data.
For example, if you have a list of 15 participants, you could list their names down a column, in Excel (the top row would normally be the "Header" row where you name each column).
In the next column, you could list their associated IDs.
Next column, disease risk level.
Etc.

If you now open a Word document, and find the "Mailings" section you can start a mail merge - using the wizard, if you've not done one before.
Under the "Select Recipients" section, select to "Use an existing list" then select your Excel sheet as the data source. This makes a data connection between the two documents.
Now, in your Word document, you can "Insert a Merge Field" which allows you to select from the column headers you used to name your Excel columns. This connects that "Field" to all the records in that column in your Excel file; you can then do the same for any of the other fields you want to draw the data from into your document. Normally the field name appears in your document at this point, but if you want to see what it'll look like when complete, you'll find a button somewhere which says "ABC Preview Results" and selecting this will toggle either the field name, or the first record from that column of data. There'll be another button which will then allow you to step through all of the records in your Excel "database."
Once complete, you can choose to either print off the documents one by one (stepping through the records you want each time) or, you can complete the mail merge, which will generate a separate report page for each recipient - so in this case you'd end up with a 15 page document (assuming that your original document was only a single page). You could then choose to print them all, or only the ones you wanted etc etc.
You can also choose to only merge certain records as well.
As long as you save your document before closing, when you re-open it, you'll be prompted as to whether you wish to reconnect to the data source, which will update any changes you've made in your excel data etc.

This sounds like what you want, and will save a huge amount of time - for a little initial effort.

This will give you a taste of what you can do - just research mail merge on the internet, and there'll be a whole ton of help on it.

Hi Sykes,

This is really helpful thanks! another quick question, is there a way to pull up a whole page related to the risk?

to make it clear, let's say participant 123 has a higher risk related to diabetes. I already have a template for how this page should look like if the participant has higher risk to diabetes. Is there a way to pull up this template whenever a participant has a certain result related to a trait and generate the whole report accordingly.

Thanks,

 

[sykes]

Once again, not entirely sure if I understand your whole situation. It's quite difficult without seeing your work.
However, from what you've said, if you already have a "Template" for higher-risk diabetes patients, and wanted to only merge higher risk patients' records to that template, as long as you had a column in your Excel database for "Diabetes Risk" (or just "Risk") with perhaps entries for high, medium & low (or to make it quicker for data entry, just H/M/L) then you could insert your fields into the template, then, before merging, use "Edit recipient list" and at the top of each field column there's a little drop-down, from which you'd un-check "All" and select "H" or "High" - which would instruct the mail merge to only draw the "High" records from the database.
You could do this for all of the fields - thus e.g. select only female, high-risk diabetes patients, or even (if you had an "Age" column) filter for above, below, or equal to a certain age. Once set up, it's pretty much limitless what you could do.

One thing I didn't mention before - best to make a "dummy" copy of your real work (your template, for example) and experiment on that - until you've got it how you want it. I tend to archive original files anyway - when I'm going to make significant changes - so that you have a fall-back route if it all goes horribly wrong!
Also, if you're working on a copy, you'll probably be more confident to experiment, and try different settings etc - safe in the knowledge that ****-ups won't matter.