Alerter question

building reports webintelligence
1

I suspect that the answer to my question below is a suitable use of an alerter, although it may be better handled through a section or even a break footer?

I have an email notification system that mails out to contacts listed against customer organisations. Each organisation may have zero or more of the following:

[list]- contacts where we have not yet specified a notification status (ie its null)

  • contacts who want to be notified
  • internal staff who want to be on BCC for that customer organisation[/list]

I want a report that lists how many are in each category for each customer organisation and to create an alert at that level if there are none in the category of “Contacts who want to be notified”.

So, in my mind I am thinking I want to specify something like:

if count(notifiable contacts) = 0, then set colour (organisation_name) to red

but what I don’t know is how I set the criteria of the count to just be for that customer.

Hope this makes sense

twofivepie :uk: (BOB member since 2008-10-16)

2

See if this works for you -

if count(notifiable contacts) in = 0, then set colour (organisation_name) to red

vinod_menon :india: (BOB member since 2007-04-09)

3

Vinod,
Thanks for that pointer - I know see how the ‘IN’ works to scope the calculation context, but what I now need to know is how I can do the equivalent of the Excel countif() function, so I can do something like:

if(countif(email_type=“valid”)) in ([Customer]), then…

twofivepie :uk: (BOB member since 2008-10-16)

4

To replicate COUNTIF from Excel I normally do:

=sum(if [sky] = “Blue” then 1 else 0)

Namlemez :djibouti: (BOB member since 2005-03-14)

5

I have tried the sum function in a break footer and it seems to get close to what I am trying to do, but I am getting those pesky #MULTIVALUE errors.

My data and report is organised like this:


Customer   TypeOfContact   NumberOfContacts (coming from a count measure)

Cust1         External            3
              Internal            2

Break footer: sum(if(TypeOfContact = External; 1;0) -> 1
Break footer: sum(if(TypeOfContact = External; 1;0) in Customer -> #MULTIVALUE


Cust2          Internal            2

Break footer: sum(if(TypeOfContact = External; 1;0) -> 0
Break footer: sum(if(TypeOfContact = External; 1;0) in Customer -> #MULTIVALUE

So in this case, the sum function proposed works well. However I would have expected it to sum up the TypeOfContact values in the underlying data rows, but instead it sums up only what has come out in the report, ie 1 or 0. I then extended using the IN context and got #MULTIVALUE, when I would have expected to get 3 for cust 1 and 0 for cust 2?

twofivepie :uk: (BOB member since 2008-10-16)

6

try :

sum(if([TypeOfContact] = "External"; [NumberOfContacts];0)

and if you are to use in clause :

in ([Customers], [TypeOfContact])

ese-aSH :fr: (BOB member since 2007-05-22)