HTML Page with Link to Download Excel File - FME Flow App

Live Demo | Data Download

Introduction

This FME Flow(formerly Server) App provides an interface for a user to stream and download a custom selection of data. The user can choose which year of job openings by industries they are interested in to stream and download. Follow along with the provided data or try it with your own Excel data and workspace.

FME Flow App: Labour Market Outlook, BC
 

How it works

The app allows for user input to select the year of interest. With the Data Streaming service, the output data is streamed back to the user in the web browser as an HTML page. Additionally, the data is also downloadable using the Data Download service via a webhook URL.
 

Step-by-Step Instructions

In this demo, the “jobsBC App” is an FME Flow App for citizens to look at the labor market outlook in British Columbia. To build an FME FlowApp, a workspace must first be designed and published to FME Flow. 

The jobsBC.fmw workspace powers the “jobsBC App”. In this workspace, a Published User Parameter is used to define the year of interest, which is available as user input in the FME Flow App. When the user submits a year between 2021 and 2029, the workspace is run by FME Flow. In fact, the workspace is run twice by FME Flow. The jobsBC App runs the workspace for the Data Streaming service, which streams the HTML page. The webhook runs the workspace a second time to output the Data Download service zip file.  

FME Workbench: jobsBC.fmw

Note: If you are using the sample workspace, you can skip to Part 2. Ensure the paths to your source file and destination files are set to point to your own data or the sample data. 

Part 1: Author Workspace

1. Download Sample File
Download the Samples zip file from Files. Open FME Workbench to create a new workspace. Drag and drop the Excel file (Job-openings-by-industry-and-occupation-for-bc.xlsx) onto the canvas.
 

2. Data Transformation
Clean up the data according to the purpose of the web application and how the user will interact with the data. For the data used in this demo, we want to remove the following attributes with the AttributeManager transformer: NOC, Variable, Geographic Area, 1st 5-year sum, 2nd 5-year sum, 10-year sum, and fme_feature_type

Note: The Clean/Prep Data bookmark in the image above can be tailored to your own data. 


FME Workbench: AttributeManager Transformer Parameters
 

3. Filter Data
The “Description” attribute from the Excel file describes the Total for each industry as well as occupation names. To keep it simple for end-users, we will only provide a high-level total count of the job openings outlook for different industries. With the Tester transformer, test for “Description” equals “Total”. This will bring down the total features count from 11,506 to 60; which means that there are 60 industries that the user will be looking at. 

FME Workbench: Tester Transformer Parameters
 

4. Create User Parameter
Add a second AttributeManager transformer to the canvas. Connect the Tester's Passed port to the input port of AttributeManager_2. In AttributeManager_2, add a new attribute, Job Opening. This attribute will use a User Parameter as its Value. Click on the drop-down arrow for Value, then expand User Parameter and click on Create User Parameter.

FME Workbench: AttributeManager_2, add a new attribute, and set the value to User Parameter JobYear

Set the Parameter Identifier to “JobYear”, and the Prompt to “Enter Year of Interest (2021-2029), and leave the Default Value blank, then click OK. Once the JobYear User Parameter is created, click the drop-down arrow again, and select Open Text Editor. Add @Value() around the $(JobYear) parameter so that it looks like this:

@Value($(JobYear))

5. Data Management
Finally, with the AttributeKeeper transformer, click on the drop-down arrow and click Select Attributes to open a dialog box. Select the Industry and Job Opening attributes to keep.

FME Workbench: AttributeKeeper, Attributes to Keep 

Industry attribute values can be sorted alphabetically using the Sorter transformer if desired.
 

6. Create HTML Report
With the HTMLReportGenerator transformer, style the HTML page. In this demo, the page title is set to Labour Market Outlook BC with three page contents: Header, Table, and Custom HTML. To present the data in a different way, such as a pie chart or add images and maps, they can be added here as well. See the CUSTOM HTML content from the HTMLReportGenerator transformer in the sample jobsBC.fmw workspace as an example. 

For the table column settings, the Column Contents are set to Industry and Job Opening with their respective column names. To specify the year for the Job Openings, add the User Parameter JobYear to the column name: Job Openings for $(JobYear)

FME Workbench: HTMLReportGenerator, Page Contents Table

The webhook for the Excel file data download service will be embedded in the Custom HTML section, which allows for greater access to stylize HTML pages using Custom HTML. Add the following into the Custom HTML by selecting Open Text Editor:

<p><a href="<YourWebhook>">Download Excel Sheet</a></p>

Note: the webhook will be created in Part 2.


FME Workbench: HTMLReportGenerator, Page Contents Custom HTML

7. Write Output Data
The last step to creating the workspace is to add the two writers, HTML and Excel. Connect the HTML writer to the HTMLReportGenerator transformer, then connect the Excel writer to the AttributeKeeper transformer. For this demo, the webhook will run the workspace and create a new Excel sheet for users to download based on the users’ year input. To ensure a new file is created, in the Drop Existing Sheet/Name Range of the Excel writer parameters, select Yes. Set User Attributes to Automatic.

FME Workbench: Excel Writer, Drop Existing Sheet


FME Workbench: Excel Writer, Attribute Definition Automatic
 

Part 2: Create Webhook

1. Publish Wokrspace to FME Flow
Before publishing to FME Flow, save the workspace as jobsBC.fmw. Next, click the “Publish to FME Flow” button or select File > Publish to FME Flow from the menu bar. Connect to your FME Flow and publish the workspace into a new repository named “Demo”.

Ensure “Upload data files” is enabled and click “Select Files…” to include the Job-openings-by-industry-and-occupation-for-bc.xlsx spreadsheet. Select OK and click Next.

In the last dialog box of the Publish to FME Flow wizard, click on the Edit button beside the Data Download service. For the “Include Writers in Download:” parameter, only select the Excel writer [XLSXW]. Now click on the Edit button beside the Data Download service. For the “Include Writers in Stream” parameter, only select the HTML writer. Click OK and Publish.

FME Workbench: Publish to FME Flow Wizard, Register Services


FME Workbench: Publish to FME Flow Wizard, Register Services, Edit Data Download


FME Workbench: Publish to FME Flow Wizard, Register Services, Edit Data Streaming

2. Run Workspace
Log into FME Flow with your credentials and go to the Run Workspace page. Select the Demo repository and the jobsBC.fmw workspace that was just published. Select Data Download for the service parameter and enter any year between 2021 to 2029. Click on the arrow to expand the Advanced section and select “Create a Webhook”.

Note: In the following steps, the year you selected will be replaced with your User Parameter, $(JobYear) in FME Workbench. In this demo, we chose the year 2023. 

FME Flow: Run a Workspace, Create a Webhook

3. Create Webhook
In the Create Webhook page, disable the “User Can Upload” parameter. Scroll down and click OK.

FME Flow: Create a Webhook, Disable User can Upload

Download the webhook to save the information. This is the only time you will be able to access the token and instructions to use this webhook, so don’t skip this step! Before leaving this page, copy the Authorization with Query String URL, which includes the token at the end.

FME Flow: Create a Webhook, Copy Authorization Query String

4. Embedded Webhok in Workspace
Go back to the jobsBC.fmw workspace in FME Workbench and open the HTMLReportGenerator transformer to edit Custom HTML.

FME Flow: HTMLReportGenerator, Edit Custom HTML

In the Text Editor, replace <yourWebhook> with the copied Webhook from FME Flow, so that it looks similar to this: 

<p><a href="http://<yourFlowHost>/fmedatadownload/Demo/jobsBC.fmw?SourceDataset_XLSXR=%24(FME_MF_DIR)job-openings-by-industry-and-occupation-for-bc.xlsx&JobYear=2021&DestDataset_HTML_3=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.html&DestDataset_XLSXW=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.xlsx&opt_showresult=false&opt_servicemode=sync&token=cb6a663689dccdd94c6271cda9b1e5c1b273f1d3">Download Excel Sheet</a></p>

Next, find the JobYear parameter in the webhook link. Replace 2023 with the User Parameter, $(JobYear), which will look something like this:

<p><a href="http://<yourFlowHost>/fmedatadownload/Demo/jobsBC.fmw?SourceDataset_XLSXR=%24(FME_MF_DIR)job-openings-by-industry-and-occupation-for-bc.xlsx&JobYear=$(JobYear)&DestDataset_HTML_3=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.html&DestDataset_XLSXW=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.xlsx&opt_showresult=false&opt_servicemode=sync&token=cb6a663689dccdd94c6271cda9b1e5c1b273f1d3">Download Excel Sheet</a></p>

5. Save and Republish Workspace
Select OK and save the workspace. Select File > Republish to FME Flow.
 

Part 3: Create Flow Workspace App

1. Create Workspace App
In FME Flow, select “Create Workspace App” from the “Flow Apps” page. If you are using the data provided in this demo, paste the following into the description section:

Annual labor market outlook for British Columbia. Data presented in a webpage. Select "Download Excel Sheet" to download the zip file.

**Data Source**: Labour Market Outlook, Labour Market Information Office, Ministry of Advanced Education & Skills Training, Government of British Columbia.

**About the Data**: Published by the Ministry of Advanced Education and Skills Training - Labour Market Analytics, Forecasting & Information
Licensed under Open Government Licence - British Columbia
Each year a report forecasting BC's labour market needs over the coming decade is produced by the BC government. The report looks at employment supply and demand by occupation and industry for each of the province's regions.

Fill out the rest of the parameters according to the table below and select OK.

FME Flow: Build a Workspace App, Parameters

After clicking OK, the URL for the workspace app will be available. 
 

2. Try the FME Flow App
Copy and paste the URL for the workspace app into a web browser. Test the Flow app by entering a year.

Congratulations, you have successfully created an FME Flow workspace application that both streams your data and downloads an Excel file from one workspace. The URL runs your FME Flow App, and grants users access to the HTML page and the data at their convenience.

Additional Resources

Getting Started with Flow Apps
Tips for Automating Excel Data Processing Tasks with FME
5 Ways FME Can Improve Your Excel Data
FME Flow Data Download Demo
Tutorial: Getting Started with Excel

Data Attribution

The data used here originates from data made available by the British Columbia Government. It contains information licensed under the Open Government License - British Columbia.