How do I send a report that has a page break by sales rep, to each sales rep?

Jaymer

Assume a database table of leads.
Each lead is assigned a sales rep, and has a status, one value being open.
It’s easy to make a report For all open leads, that groups and page breaks by sales rep.

For a weekly meeting, I want to send each sales rep a copy of his open leads.

So the SET OF sales reps can be different each time I run it.
Some reps won’t get anything.

I would be surprised if there’s some built-in way to do this. Then I have definitely overlooked this mechanism for years.

I think I basically have to make two passes… One to build a list of the sales reps that had records in the report, and then a second loop which cycles through each rep, and Emails a report to that rep.

I already did this with some different data, and I called a stored procedure, which does a simple select distinct on sales rep ID, and then I combine all those IDs into a comma delimited string and store in the system settings.
Then I use a WHILE loop (Mark’s looping method) to use each ID to call the subtask, which prints and emails the data for that rep.

Alternatively, I could loop through the employee file, which could be Big, but let’s say I have sales reps marked, which narrows down the list, and I could do a FIND and check SEARCH_COUNT To see if that rep Had Open leads, and if so, run the sub task.
Some people might prefer that since it is keeping it all in aware, which is easier for some.

Do you see another strategy?

Jaymer

Jaymer Alternatively, I could loop through the employee file, which could be Big, but let’s say I have sales reps marked, which narrows down the list, and I could do a FIND and check SEARCH_COUNT To see if that rep Had Open leads, and if so, run the sub task.

@joben yes, that’s what I have here. Same thing.

Just wondering if there some super innovative way I hadn’t thought of.

joben

I don't have the config tool in front of me right now. But a report can be set to use the current context as input.

Perhaps you could start the whole thing with a process like PICK ONE OR MORE FROM 'Sales Reps'?
Then the next process is called. The next process will use SalesReps BO as input.
So for each Sales Rep that has been picked in the first process, we want to create a report, right?
And here is the tricky part, I think we have to EXPORT DOCUMENT SalesRepReport to the server file system, because I know not how to save it directly to a BO attribute. So maybe we use the IMPORT DOCUMENT action afterwards to write the document to an attribute inside the RegularUser BO.
The final step in the process would be to CREATE OutgoingEmail and use the document attribute as Attachment, then finish off with a SEND action to the current RegularUser in context?

It was a bit hard for me to understand some of the parts, but I think that setting the report to "determine context at runtime" can be useful.

Jaymer

Thx, but this is a Scheduler report
No user interaction.

joben

Is the main concern that you only want to fetch the Sales Reps that has open leads in the context?

Instead of PICK:

Create a weekly scheduled task to run Process 1.

Process 1

FIND ALL SalesRep
Process2

Process 2 (SalesRep as input BO)

If EXISTS Lead WHERE (Lead.Status='Open' AND Lead.SalesRep=SalesRep) Then
Process3

Process 3
All the report generating stuff here, which eventually leads to the OutgoingEmail and SEND action.

jannes

I work with FastReport, where reports are called with parameters, including $user (in this case, the Sales Rep) and $database.

The report uses an SQL query like this:

SELECT pe.*
FROM $database.persoon pe
, $database.backofficeuser bu
, $database.persoonzoek pz
WHERE bu.loginname = $user
AND pz.ps_BackofficeUser_RID = bu.id
AND pz.ps_persoon_rid = pe.id

Additionally, I use Word templates that can be customized by users.
The template contains merge fields such as <<Persoon.Naam>>.
The Word merge is executed from AwareIM, generating a DOCX, which is then converted into a PDF via an external program. The PDF file is then attached to an email (as defined in an email template).

One challenge is that this method can be prone to failures under heavy server load caused by multiple users, as MS Word must be installed on the server and is required for the merge process.

Therefore, I use a third solution, which leverages an external Word-merge module. The merge fields are defined in Word, and bands such as master-detail structures are specified via comments.

This module rapidly generates a PDF from a Word template, without relying on MS Word during the merge process. This is particularly useful for large runs, such as emailing a high volume of invoices.