ReportServer Forum

bpeikes

Member

Registered: 2016-10-29

Scheduled email report conditional on number of records

We would like to run a report every morning, but only send it out via email if there is any data in it. Is there anyway to do this?

karolina

Member

Registered: 2014-08-09

Re: Scheduled email report conditional on number of records

What kind of report is it? Dynamic list?

If so, one of the solutions can be found here

karolina

Last edited by karolina (2016-11-13 10:21:27)

bpeikes

Member

Registered: 2016-10-29

Re: Scheduled email report conditional on number of records

Yes, it's a Dynamic List. I don't understand German, so the post is difficult for me to understand. Also, I don't want this for all reports that are scheduled, just some.

karolina

Member

Registered: 2014-08-09

Re: Scheduled email report conditional on number of records

I don't understand German as well, but I use Google Translator.

karolina

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Scheduled email report conditional on number of records

have a look at the "Conditional Scheduling" section in https://reportserver.net/en/guides/user … cheduling/. It is not the most intuitive feature but it should provide the means to implement what you are looking for.

Cheers,
Arno

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Scheduled email report conditional on number of records

Let me try to give an example, as the documentation for this is, admittedly, a bit sparse.

The demo data comes with the table T_AGG_PRODUCT, which contains information about products. Suppose, we want to have a report that checks whether enough items of each product are in stock. The resulting, simple report, could be the following, which simply lists all products ordered by the number of items in stock:

Now, we want to schedule the report, but only send it out if say, there are products that have a quantity count of less than 100. For this, we need to create a "condition report", which is simply a dynamic list, that provides the condition against which we want to test. In our case, we could create a dynamic list with the following query:

SELECT COUNT(*) AS NrOfProducts FROM T_AGG_PRODUCT WHERE PRO_QUANTITYINSTOCK < 100

Here once more as an image:

Now, the condition feature requires us to make a report variant, which in this case is simply a variant that selects all the columns. (Note, that you need to ensure that your report only always returns a single row.)

Now that we have the "condition report" we need to make ReportServer aware of this. For this, we open the terminal (CTRL+ALT+T) and use the command "rcondition create". It expects 4 parameters, a report, a unique key, a name and a description. The easiest to provide the report is via id. In the example the ID is 255145 so we can use the command

rcondition create id:Report:255145 LOW_QUANTITY "Low Quantity" "Some Description"

You can check the created conditions via the "rcondition list" command, and remove conditions with "rcondition remove".

Finally, we have a condition and can now use it, when scheduling. For this, when we schedule a report, make sure to check the box "advanced options" on the first page of the scheduling wizard:

If checked, you will be presented with the "conditional scheduling" options.

Here you can select from the available conditions. In our case there is simply the one that we just added. Once you've selected the "condition report", you need to define the actual condition. For this you can type in a boolean expression, using the fields of the condition report. This could be, for example,

NROFPRODUCTS > 0

meaning that the condition is true, if the report returns a value greater than 0 for the field NROFPRODUCTS.

Once all the conditions are set up, you can define what to do in case one (or more) conditions do not hold. Either skip the execution (which is probably the usual case) or retry the execution.

Hope this helps,

Arno

bpeikes

Member

Registered: 2016-10-29

Re: Scheduled email report conditional on number of records

Thanks, that's exactly what we were looking for as an example.

The only down side, is that if the query for the report is expensive, it means that you have to run it twice, once to get the count, and another to get the data. It would be great if there was a way to reference the count of records in reports from the condition.

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Scheduled email report conditional on number of records

There is a second option: implement the "condition" via a small script. Basically, the outline of the script is

import net.datenwerke.scheduler.service.scheduler.hooks.SchedulerExecutionHook
import net.datenwerke.scheduler.service.scheduler.hooks.adapter.SchedulerExecutionHookAdapter
import net.datenwerke.rs.scheduler.service.scheduler.jobs.report.ReportExecuteJob
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport
import net.datenwerke.rs.base.service.reportengines.table.output.object.CompiledTableReport
import net.datenwerke.scheduler.service.scheduler.helper.SkipJobExecution

def HOOK_NAME = "SkipEmptyListSchedulerHook";

def callback = [
  doesVetoExecution : { job, logEntry ->
	if(job should not be executed)
            return new SkipJobExecution("No data")
  }
  ] as SchedulerExecutionHookAdapter

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, SchedulerExecutionHook.class, callback)

So, basically, you implement the method doesVetoExecution as part of hook SchedulerExecutionHook. One implementation, which checks whether or not the report contains any data (note that this only works with dynamic lists) would be

import net.datenwerke.scheduler.service.scheduler.hooks.SchedulerExecutionHook
import net.datenwerke.scheduler.service.scheduler.hooks.adapter.SchedulerExecutionHookAdapter
import net.datenwerke.rs.scheduler.service.scheduler.jobs.report.ReportExecuteJob
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport
import net.datenwerke.rs.base.service.reportengines.table.output.object.CompiledTableReport
import net.datenwerke.scheduler.service.scheduler.helper.SkipJobExecution

def HOOK_NAME = "SkipEmptyListSchedulerHook";

def callback = [
  doesVetoExecution : { job, logEntry ->
	if(job instanceof ReportExecuteJob && job.getReport() instanceof TableReport){
	   job.doExecute()
	   if(job.getExecutedReport() instanceof CompiledTableReport && !job.getExecutedReport().hasData()){
		  return new SkipJobExecution("No data")
	   }
	}
  }
  ] as SchedulerExecutionHookAdapter

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, SchedulerExecutionHook.class, callback)

For a general introduction to scripting, have a look at https://reportserver.net/en/tutorials/t … scripting/.

Cheers,
Arno

quintg

Member

Registered: 2016-10-19

Re: Scheduled email report conditional on number of records

Hi, I followed the steps (not via scripting) and have set up a dynamic list variant that returns one record with an integer field 'trigger_flag'. This is read from a database. My purpose is to only send out a report when the value is equal to 1. When I set the actual condition to 'trigger_flag = 1' (without the quotes) it is failing with error  'Error parsing '${trigger_flag = 1}...'. Setting the condition to 'trigger_flag > 0' it works but I am curious why I cannot use the equal sign to construct a Boolean expression.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Scheduled email report conditional on number of records

Hi quintg,

have you tried with trigger_flag == 1  ?

Cheers,
Eduardo