ReportBy Notification Examples

(1)

ReportByEmail

Notification Examples

(2)

ReportByEmail – Notification Examples

RBE Examples ... 3

RBE Example 1 – RBE Example 1 test with subject field tagging... 3

RBE Example 2 - Notification with Excel sheet ... 6

Setup basic notification values ... 6

Upload Sheet to RBE Server ... 6

Verify ... 8

Recipients ... 8

RBE Example 3 - recipients–parameter transfer between RBE and Excel ... 10

Excel functionality with parameter transfer ... 12

RBE Example 4 – SQL-recipients with output in HTML ... 15

RBE Example 5 – Report in PDF Format ... 17

RBE Example 6 – HTML report for more recipients ... 19

RBE SQL Query Employees – RBE SQL Query Employees ... 23

RBE Returned Parameters ... 30

ReportByEmail notification ... 31

(3)

RBE Examples

This manual shows different examples are listed and can be followed easily. Screen dumps of the sheets are supplied. If you are in doubt how things work, please check the sections earlier in this manual to see detailed information about how things work.

The examples presented here can also be downloaded from ftp.reportbyemail.com, where a large number of standard templates are located.

RBE Example 1 – RBE Example 1 test with subject field tagging

It’s time to test the first notification. Send a simple notification to yourself and follow the following example data.

Right-click and create a new notification:

(4)

Press the “Test notification button” and if you have included yourself in the recipient list or in the CC: or BCC-fields, check your email client:

(5)

(6)

RBE Example 2 - Notification with Excel sheet

This example requires the Microsoft Access 2007 Northwind database and the excel sheet as specified earlier.

Setup basic notification values

Upload Sheet to RBE Server

From any sheet click on Upload in the upper right corner and browse to the folder, where you have downloaded the Excel sheet to:

(7)

Click Open:

(8)

RBE server can read the file locally.

Verify

After uploading the Excel sheet, test that the Excel sheet is in working order and the number of queries within it.

The window should show exactly the number of queries, which are defined within the Excel sheet. Notice that you can drag and thereby adjust the width of each field in order to better read the values.

(9)

Fill out the Recipients field with your email-adress and press “Test notification”. You’ll receive an email with the result of the query:

Notice that:

 The subject field was defined as [Notgroup] [Notname], which was converted into the string in the subject field using the “path” of the notification and the name of it.

 The Excel sheet is attached, e.g. the user can open it and work with it.

 The Excel sheet contains “random” values!

The last fact is because this Excel sheet has been designed to get a number of input parameters, specified in the notification. See the following example to get more meaningful values out of the sheet.

The data returned by ReportByEmail will, when no parameters are specified typically be the parameters used within the Excel sheet during the design process.

Notice that it’s very important to design Excel sheets correctly (With a number of parameters) in order to use the product in an optimal way.

(10)

RBE Example 3 - recipients–parameter transfer between RBE

and Excel

If more parameters are needed in Excel in order to perform an operation, these can be transferred from the Recipients window.

It’s compulsory to have a column named Email, but more columns can be added:

Create a new notification, by pointing to the same Excel sheet as in the previous example:

(11)

Right-click on Email and choose “Add column”.

Right click in the column2 column. Choose Edit header text. Enter a name like startdate. You can add many fields, but usually two to three values are needed. Enter Startdate:

(12)

You now need to insert date-values for the recipient.

This demo-sheet needs three parameters, so insert more columns and name them as described above.

(13)

In Excel you need to name fields with the same names as defined in RBE. In the example Excel sheet the following fields are necessary:

Emailemployee Startdate Enddate

Cells within the Excel sheet is filled out with these values initially, the Excel sheet calculates all the queries and the output sent according to the RBE Settings.

Add the last columns and enter data as shown below:

Notice that there always has to be an “Email”-field to specify an recipient. The other parameters are optionally. Within the Excel sheet you need to use the “defined names” functionality to name a cell in Excel the same as listed above.

(14)

The Excel sheet looks like this:

(15)

RBE Example 4 – SQL-recipients with output in HTML

Make a notification exactly similar to the RBE Example 3 and change the output to HTML:

(16)

(17)

RBE Example 5 – Report in PDF Format

Define the same report again as in the previous example, but select output in PDF format:

(18)

Inspect the attachment:

(19)

RBE Example 6 – HTML report for more recipients

Create a similar notification, which have more recipients with output in HTML:

Notice the use of [Emailemployee]. This way the manager receiving a number of these notifications as a CC or BCC recipient can easily distinguish between the different employees.

(20)

Use the “Test notification” button. You’ll receive three emails, but since no data exists for

[email protected], the email is empty and useless:

(21)

ReportByEmail – Notification Examples Change the “Send Always” to “Send If data”:

(22)

ReportByEmail – Notification Examples Now use the “Test notification” to run the query again.

(23)

RBE SQL Query Employees – RBE SQL Query Employees

The examples above works fine, if the company always has the same employees and no-one is hired etc., but that’s not the real world. Therefore ReportByEmail supports looking up the recipients in data tables.

In the Northwind database a table consists of the employees in the company. Therefore the best way of designing a report, that will work in future (If the employee table is updated will new employees etc.) is to lookup the recipients in the table.

Make a new notification called RBE SQL Query Employees:

(24)

ReportByEmail – Notification Examples Lookup recipients:

(25)

All your datasources should be defined as DSN (See manual regarding ODBC connections), so click on the listbox ODBC and select RBEDEMO and select “Use trusted connection”:

You can test using the test button:

If not successful, check the manual regarding ODBC-connections and setup of rights. Click ok:

(26)

Now the datasouce is selected, the Query needs to be inserted. Here you need to have information how the database is designed. See the tips & tricks manual of how to design these queries. A easy way to start is the insert the following:

select * from Employees

This will show all data from the Employees table. Insert the text in the Query field and push the “Show Result” button:

(27)

The field we need as a parameter for the Excel sheet is the users email address. To select only this, modify the Query field to the following:

(28)

Select '[email protected]' as email, "E-mail Address" as EmailEmployee, '1-1-2006' as Startdate, '31-12-2006' as Enddate from Employees

Now all the columns have same names as in the previous examples and it’s time to test the notification.

You’ll receive 8 reports, although the Northwindtraders company has 9 employees, because one person didn’t sell anything within the specified period.

(29)

One of the recipients didn’t get an email, because the workbook was invalid or the validation query returned no data. This last part is the “Send-If” check, that results in no email to this person.

(30)

RBE Returned Parameters

You can use Excel to return parameters to ReportByEmail to format the subjectline and/or add dynamic results into the subject field and the body of the ReportByEmail notification.

This feature gives more flexibility to the end user when it comes to extending the generated Email Subject and Body. If a cell in the Excel workbook is named, its value can be retrieved after the report is generated so that it can be used in formatting the email.

For example, we define a worksheet to have B3 named as “input1”, C3 named as “input2”, and D3 named as “output”. Furthermore, the last cell has a formula of “=SUM(C3, D3)” which means that its value will be derived by summing up “input1” and “input2”.

Excel sheet:

(31)

ReportByEmail notification

Notice that the named field Output is NOT listed on the Recipients input list.

“input1” is 2 and “input2” is 3. Once RBE processes the worksheet, it gets these two values and places them in the cell that they are named after. Following that, “output” would then have a value of 5 (2+3).

(32)

The values will then be reused to format the Email’s subject and body, resulting in the following:

Notice that the body is listed first in the email, before the HTML output of the Excel sheet. If you only need calculations in the subject and/or body field, you can design the Excel sheet with an empty sheet as the first sheet and have the calculations done on other sheets. This would “blank” out the “Input1 …. Input2….” And “2….3….” lines in the report above, only showing the body line of “The sum of 2 and 3 is: 5”. You can see this in the “RBE Return subject” example.

(33)

RBE Return subject

This example shows how to construct a subjectline in Excel. Sheet1 is empty in this example, but usually some kind of result would be used.

Excel sheet:

(34)

ReportByEmail – Notification Examples Months is the input value.

Calculations in the Excel sheet:

Today is the builtin Excel function to return the date today. Lastmonth is calculation of the last day of previous month: =EOMONTH(DATE(YEAR(B3);MONTH(B3)-1;1);0) X months back is calculation of the first day “Months” back: =DATE(YEAR(B4);MONTH(B4)-B2+1;1)

The values to the right of the two previous values are: =TEXT(B5;"mmmm")&" "&TEXT(B5;"åååå") When using English formats åååå should be yyyy

Now text versions of the startmonth and endmonth are available in C4 and C5. Subjectline is calculated:

=IF(B2=1;C4;C5&" - "&C4)

If only one month, the Subjectline named field looks like this:

september 2009 If months is 4:

(35)

ReportByEmail – Notification Examples Notification Email

(36)