Solved

COVID-19 - Report generation (MS Word) and data handling - URGENT

  • 18 April 2020
  • 4 replies
  • 33 views
mb_fdfa
Badge +1

Dear all,

I'm hoping some of you in confinement have time to help me out in my battle with a major COVID-19 project involving most of our organisation and representations abroad. This was set to be revolutionary for a world where hundreds of emails and a parallel universe of excel files are as "digital" as many feel at home with.

 

Unfortunately, despite moving heaven and earth in a very short time, it looks like failing at the very last step as the standard reporting tool used in our institution seems to have some kind of bug and can't take the heat.

 

Of course this whole issue surfaced at 4.50pm on a Friday...

I know FME Server can do the job, I just don't have all the knowledge (or time to hunt for clues) required. So as was suggested on the Safe Live Chat yesterday, I want to see if anyone out there is prepared to help me with various bits.

Software: FME Desktop + Server 2019.2.2.0 (Build 19817)

Background:

  • Data entered by our offices abroad in SharePoint
  • JSON export from SharePoint is input for reporting (and mapping)
  • Data entered is to be generated into a Word file (Word preferred but I guess PDF might be accepted) and automatically emailed back to the office (if status = submitted and ideally only if any of content has changed since last submit).
  • Database available: MS SQL
  • GIS: The whole lot is also to be visualised in web GIS - but that is sorted :-).
  • Schedule: this bit is fun - need to have it ready to prove it can do it on Monday! No risk, no fun...

     

     

Topics so far (in order of priority):

  1. Formatting a report output in MS Word using the MSWordStyler and including all attributes.

     

    I have a rough version with the required content but even after asking Safe on the live chat last night, I couldn't get hold of any samples which I could work with to get nicely formatted output.

     

    I would need to be able to produce a report for each of about 200 countries at regular intervals as the content changes (data is to be entered in SharePoint, I read in a JSON file exported from there).

     

    Does anyone have experience with making something decent with this styler/writer?

     

    Is there a way of defining the margins used in the output word file?

     

    Found: http://docs.safe.com/fme/2019.2/html/FME_Desktop_Documentation/FME_ReadersWriters/msword/msword_writer.htm

     

    Any way of controlling the font in a table and any documentation on what on earth the long list of different sytles actually look like?

     

    Would PDF output have any advantages?

     

     

  2. Comparing the content of the JSON exports to the last export to see if anything (and preferably what fields) has changed. At each run of the report generation tool, I write the data into a DB table, so that could be used to compare states of the data.

     

    Only just starting on this bit but any inputs from anyone with comparable experience would be appreciated.

     

     

  3. FME Automation to email the results - I know it will be possible and my FME Server is set up to email. I just need to work my way through adding attachments to emails.

     

    I plan to add an input field for 1-3 email addresses and use those to define where the reports are sent.

     

     

Sample input data JSON:

Sample input data CSV:

Sample report as basis for improvement of tables:

 

Sample report which was going to be possible with another tool:

 

I know it's a lot to ask/hope for, but I'd rather try than just give up. FME has saved me before, so why not now... Made significant progress since I posted originally but there is still plenty to improve on.

 

Thanks to anyone who has read this far!

 

 

Cheers,

 

MB

 

Swiss Humanitarian Aid
icon

Best answer by debbiatsafe 20 April 2020, 22:55

View original
1 Attachment

4 replies

jdh
Rank
Badge +22

For 1, I found I had a lot more control creating the word document in a pythoncaller with pydocx https://python-docx.readthedocs.io/en/latest/#

 

2. Have you worked with the ChangeDetector? If so what is it missing?

 

3. The FME hub has a custom transformer called the FMEServerEmailGenerator that creates the json used by the FMEServerNotifier to send emails. This includes the ability to send attachments. The files need to exist, but you can use a FeatureWriter to generate them during translation.

debbiatsafe
Rank
Userlevel 3
Badge +17

Hi @mb_fdfa

1. I would recommend using a base file for greater control over formatting in the output Word file. For example, you can change margins and modify the look of MS Word Styles in the base file and these changes will be reflected in the output Word document when the base file is used within the MS Word writer.

You can view the list of table styles by opening a Word file that contains a table created by FME and going to Table Tools > Design tab > Table Styles (see screenshot). You can also modify Table Styles to control font and formatting of a table style.

I have attached an example workspace which uses a base file where the margins have been narrowed, font size and colour changed in several styles, and the Table Grid table style was modified to have a grey background for the header row in addition to font changes. wordbasefileoutput.fmwt

Note: working with PDF tables and paragraph text can be difficult so I wouldn't say PDF output would be an advantage.

2. I agree with @jdh that the ChangeDetector should work for your purposes. I also recommend taking a look at this article on change detection using FME for some other options.

 

3. I would recommend taking a look at this article on Sending Emails from Automations as it may help

 

mb_fdfa
Badge +1

Hi @mb_fdfa

1. I would recommend using a base file for greater control over formatting in the output Word file. For example, you can change margins and modify the look of MS Word Styles in the base file and these changes will be reflected in the output Word document when the base file is used within the MS Word writer.

You can view the list of table styles by opening a Word file that contains a table created by FME and going to Table Tools > Design tab > Table Styles (see screenshot). You can also modify Table Styles to control font and formatting of a table style.

I have attached an example workspace which uses a base file where the margins have been narrowed, font size and colour changed in several styles, and the Table Grid table style was modified to have a grey background for the header row in addition to font changes. wordbasefileoutput.fmwt

Note: working with PDF tables and paragraph text can be difficult so I wouldn't say PDF output would be an advantage.

2. I agree with @jdh that the ChangeDetector should work for your purposes. I also recommend taking a look at this article on change detection using FME for some other options.

 

3. I would recommend taking a look at this article on Sending Emails from Automations as it may help

 

Hi @debbiatsafe

Thanks a lot for your help and useful links on all topics!

 

Much appreciated.

 

MB
mb_fdfa
Badge +1

For 1, I found I had a lot more control creating the word document in a pythoncaller with pydocx https://python-docx.readthedocs.io/en/latest/#

 

2. Have you worked with the ChangeDetector? If so what is it missing?

 

3. The FME hub has a custom transformer called the FMEServerEmailGenerator that creates the json used by the FMEServerNotifier to send emails. This includes the ability to send attachments. The files need to exist, but you can use a FeatureWriter to generate them during translation.

Hi @jdh

Thanks for the useful info. Useful to know about integrating pydocx.

I'm familiar with the ChangeDetector. It's not quite so straighforward in this use case but I'll hopefully find a way and will start with this transformer.

MB

Reply


Community Stats

28,669
Posts
109,361
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.