Autonomously generate report and send it by e-mail

  • 19 March 2021
  • 0 replies
  • 157 views

Userlevel 6
Badge +18

Goal

Based on a customer story.

Every day at 06:45 a procedure runs for the customer and during that procedure, errors can occur. These errors are recorded inside the application and viewed by one particular user. This user only opens the application to look at these errors. If there are no errors at all, the user still has to look inside the application to find out. It would be more handy if the user gets an e-mail with a Report of all the errors.

 

Solution

By using the System Flow functionality, we can autonomously let Indicium take care of generating a Report and sending it to the user by e-mail.

In this example I will show you what is needed to achieve this.

Prerequisites:

  • Indicium (Universal) - hereafter called Indicium
  • Application in IAM
  • SMTP server for sending the e-mail - this example uses a free SMTP server by sendinblue

Chapters

  1. Create a View in the Software Factory
  2. Create a DevExpress Report with the DevExpress Report Designer available in TCP
  3. Create a Report in the Software Factory
  4. Create a Process Flow in the Software Factory
    1. HTTP connector
    2. Write file
    3. SMTP connector
    4. Process Schedule
  5. Synchronize the project to IAM
  6. Result

Note that this is a basic example and a lot can be improved upon

1. Create a View

When using Reports it is highly likely information has to be combined. Some Reports use a Header (one row) view and a Details (many rows) view. This example focuses on one view containing the Header and Details.

In Datamodel, create a View that has all the columns needed:

Datamodel

The code used for this example:

select r.relation_name              as company_name
, sol.shop_order_id as shop_order_id
, sol.shop_order_line_id as shop_order_line_id
, r.logo_upload as company_logo
, '<img src=data:image/png;base64
, '+CONVERT(varchar(5000), r.logo_storage, 1)
+'>'
as company_logo_blob
, p.product_name as product_name
, sol.quantity_in_basket as quantity_in_basket

from relation r

cross join shop_order so --cross join for the example

join shop_order_line sol
on sol.shop_order_id = so.shop_order_id
join product p
on p.product_id = sol.product_id

where relation_id = 1 --My main relation / Could be replaced by parameter

Note, for the logo I used this - link

 

2. Create a DevExpress report

When succesfully downloading and opening the available DevExpress Report Designer, create a new Report using the Wizard (Ctrl+W).

Next, select the created view and the columns to make them available to use in the Report

Select Finish, now the Report can be designed. The example Report looks like the following

Designer
Preview

Save the Report (.repx) to a desired place.

 

3. Create a Report

To use the DevExpress report, a record for this report is needed in the Software Factory.

For this example api_report has been created and the created DevExpress report has been set as File. Report action was set to Export PDF and the printer is PDF.

 

4. Create a Process Flow

Now we create a Process Flow without user interaction; a System Flow. Be sure to check which process actions are supported. 

The Process Flow:

The example uses 2 Process variables:

  1. content: varbinary(max)
  2. file_path: nvarchar(2048)

1. HTTP connector - link

   Input

Sending a request to Indicium to start Generating the Report is what the HTTP connector will be doing. Give it the correct URL to Generate the report and set the HTTP method to POST. The input content will be JSON so set the Content-type to application/json

Content will be a JSON string that holds all Report parameters that should get a value. In the example, only one parameter is present and will be given the value 8 as this is the correct ID of the shop order.

As Authentication, Basic is sufficient. Username/Password are the login credentials to the IAM application. In the example, that is SA with password ********* :wink:

HTTP connector - Input parameters

 

   

   Output

Only one parameter is needed to capture in a Process variable, namely Content. The HTTP connector will return in Content the varbinary string that is the Generated report. This varbinary string is captured in the Process variable content

HTTP connector - Output parameters

 

 

2. Write file - link

The content retrieved from the HTTP connector has to be written to Disk. The Write file Process action can do that.

File location is the location where the file is written to + the file name + the extension of the file. Be sure to end this value with .PDF 

Set Content as File data as this is the content of the Report.

Write file - Input parameters

 

 

3. SMTP connector - link

Generating a Report is one thing but it what really would benefit users is the ability to send an e-mail. With the SMTP connector you can do just that. Many companies have their own SMTP server to send e-mails with. In this example, a free one has been used.

Input parameter Value/explanation
SMTP server address

Your SMTP server address

e.g. smtp-relay.sendinblue.com

SMTP server port

Your SMTP server port

e.g. 587

Username Valid username to use SMTP server
Password Self-explanatory
From address

Address were the email will come from

e.g. wisethinking@smtp.com (fake)

From name

From name

e.g. Wisethinking

To recipients

E-mail-address(es) to send the e-mail to

e.g.   a@a.com; b@b.com; c@c.com

Subject

Subject of the e-mail

e.g. Your latest report

Message

Body of the e-mail.

Can be HTML - Use together with Allow HTML

Allow HTML

Yes/No

Set to Yes if Message contains HTML

(Deletable) Attachments

File path to the Report

e.g. C:\inetpub\wwwroot\IndiciumUniversal\gen_reports\test.pdf

Use Deletable attachments if the file needs to be deleted after sending. Else use Attachments

 

 

4. Process schedule - link

A schedule is needed for automating the run of the Process Flow by Indicium. This schedule is set to run at 05:45 UTC. Be sure to calculate what time to fill in.

At the time of writing this, Amsterdam time is one hour ahead of UTC, meaning 06:45 CET is 05:45 UTC. Be aware of Daylight saving time.

Schedule

 

 

5. Synchronize

Generate and validate definitions and then Synchronize to IAM. In IAM, create an Application for the Project version, if not done already, and double check if the System Flow is active.

 

6. Result

E-mail
Report attached

 


0 replies

Be the first to reply!

Reply