The Attivo Blog

We all know that Event Manager is a terrific data-entry slave for businesses. Need Table 1: column A totaled and copied into Table 2:column B?  No problem. Need Table 3: column B tallied and copied into Table 3:column C? Cake. Need Table 1: column A to somehow related all the way across to Table 3: column C? Might be a little messy in SQL, but Event Manager can handle it with relative ease. But sometimes our linking between tables forces Event Manager into the position of needing to separate rows that later we need to be totaled, or vice-versa.  The quick solution to this would be to populate a free-field on each individual row with that total amount; but it's easy to see that so many "writes" are unnecessary and can cause confusion later on.  Instead, it is much more beneficial to split up the single event into 2 or more separate queries. One of The Attivo Group's customers recently needed to implement an Order Acknowledgement that would be automatically distributed when certain criteria were met.  Event Manager satisfied that initial requirement easily with a simple Order Acknowledgement Event provided with the software.  Then the customer recognized that the canned event contained Total Amounts on the order that deducted back-ordered items. While this logic makes sense to many businesses; this customer preferred to include the total amount of all order lines on the acknowledgement.  When initially contacted about this task, I explored the idea of combining the total process into the existing query, but found that I was always forced to convert linked tables from outer joins to inner joins, thereby removing my ability to display multiple rows on the acknowledgement itself.  With that idea shelved, I split my task in two. First I wrote a query to total all the order lines at the line level, and store that total on the order header.

SELECT sum(line.qty_ordered*line.unit_price) AS "total", header.ord_no, header.ID FROM (line LEFT OUTER JOIN header ON line.ord_no = header.ord_no)   GROUP BY header.ord_no, header.ID

Then the triggered event stored the "total" above in a free field at the header level, virtually next to the "incorrect" amount. 

update header set freefieldX={total} where ID={ID}

Finally, I then added an additional filter to my separate Order Acknowledgement query/event that made it ONLY query orders that had a filled in freefieldX.

SELECT line.id, line.ord_no, line.(...),header.freefieldX, header.ord_no, header.(...), etc... FROM (line LEFT OUTER JOIN header ON line.ord_no = header.ord_no)  WHERE header.freefieldX <> 0 AND header.freefieldX is not null, etc...

Notice I did not group the resulting rows together this time, as I no longer needed them to be totaled; this was already done in the previous event!  I then let the second Order Acknowledgement run as designed, with the single exception of replacing the default TOTAL AMOUNT with the amount stored in freefieldX. While this nearly completes the event's multi-step procedure, there's two small steps needed to be absolutely sure that query/event #1 always runs before query/event #2.  First, I make sure the queries/events are on the same schedule in the system.   Then I set the priority of the first event to be higher than the priority of the second event. These steps prevent event #2 from waiting until the next scheduled time before processing - thus allowing event #2 to process immediately after event #1 completes.

Tip #1 - In business, things will go sideways.  Be prepared.

With all of the data that we're creating every day, especially as things become more automated, how can anyone possibly keep up with all of this?  Are we expected to spend more time reading reports?  That doesn't sound very productive. In business, knowing what didn't happen, or is not going to happen as expected is very important.  Knowing what looks like a problem in the making is extremely valuable.  That's one of the things that event management and business activity managment is all about - getting some advance warning about things that are about to go sideways. A great example is customer credit - something that definitely needs to be managed.  Under the old fashion way, a customer has to show up in the 60 day (or worse) column before someone swings into motion to make some collection calls.  And then you go through the dance - "Can you send us a copy of the invoice? It's waiting approval from so-and-so who's on vacaction until next Wednesday, It's in our check run for this Thursday" etc., etc.  (Call me if you need some more of these...I've heard some beauties).  Using an Event Management system, things can be VERY different.  Let's say one of your customers just went past their terms (like 10 days past 30 days).  And you are about to ship some more product to them, only your event manager system just notified credit, and either the credit department or your system has locked down printing of pick tickets for this customer until the hold is released.  Someone in customer service has also been notified that this customer's order will not be shipped until this credit problem is resolved, and optionally, the customer has also been notified that their order is being held up until the problem is resolved.  Within hours, a few phone calls are exchanged, and the problem is resolved, while you still have some leverage with your customer.  They might be embarassed for the moment, but it was their mistake, after all.  You resolved it with them while everyone was still friends.  They received the merchandise that they needed, and you received the cash that you were owed.  And they also know to not delay payment to YOUR company when the cash requirements report is printed next time.  They know that some vendors watch, and others, not so much.  You are now on their list of vendors that is watching.  The lesson here is that like credit, which will go sideways if you are not watching (guaranteed), there are many things in business where you can be prepared, forewarned or alerted that something important needs your attention.  It doesn't matter what type of software application you are using, or how many different applications or databases might be involved, either.   Business activity management systems look for pre-defined conditions in the database, and then execute your pre-programmed instructions.  The trigger could be something as simple as an email received with a particular word in the subject line.  The response that you plan for might be simply an email to someone that can affect the outcome, or it could be a complex series of outcomes.  In our example above, it might have been a simple email to the controller or credit manager, or a combination of that email plus automated credit holds on all orders and an email notification to the customer.  You define the action to take - whatever is appropriate for the issue at hand. Put these simple and inexpensive tools to work for you - and deal with fewer fire drills every day.  Fire drills are very expensive, and take time away from your productivity.

In addition to being "The Event Manager" here at Attivo, I also write many Crystal Reports, most commonly for ISOMan.  ISOMan is always asking me for a new Crystal Report displaying a different collection of Product Information for him to present to our clients' Quality and Product Managers. Sometimes we deploy these reports via some means of on-demand distribution, like the Crystal Enterprise Suite or the HTML Wizard Recrystallize.  And sometimes we deploy the reports via a scheduled job in Event Manager, sending 1 or more daily reports each afternoon at the close of the workday. Event Manager, as you may know, is fully capable of automatically running any Crystal Report and attaching them as .pdf files to an email, before spinning that email off to a predefined recipient. But what if you ONLY want that report to send off when there is data to display in the report? What if the range in data for the report varies from 0 to 100's of results? In this instance, you will need to create a conditional/scheduled report. Anyone with any experience in Event Manager knows that the first step is to create a query. We need to know WHEN to run the report.  So we begin by creating the query, adding/ linking the tables, and adding the columns and filters.  However, do not spend too much time on the columns. Why? Because we won't be needing any database columns beyond the unique ID to tell our scheduled report to run.  The columns you pick will soon be deleted once we have verified our data.  Once you have created your filters and your preview displays only those results you are looking for, click the SQL tab and check the box stating that you will manually edit the SQL for this query.  First, break your SQL up into it's three main components, the 'select' statement, the 'from' statement, and the 'where' clause. Next, delete everything in the select statement EXCEPT the unique column you selected earlier.

select imitmadt_sql.id

We want to COUNT the amount of times this id comes up. Remember, our REPORT which will get triggered by this query will display all the required columns. This query is to determine IF the report should be run ONLY. Our short query becomes:

select count(imitmadt_sql.id)

Skip the 'from' section and proceed to the 'where' filters you added earlier.  Assuming you created the filters correctly using the Event Manager Filters tab, you will just need to add one more 'filter' to the very end of your SQL statement.

where... imitmadt_sql.activity_cd='F' AND imitmadt_sql.aud_action='A'

Becomes

where... imitmadt_sql.activity_cd='F' AND imitmadt_sql.aud_action='A' having count (imitmadt_sql.id)>0

Note that there is no AND between the last 'where' clause and the new 'having' clause which includes the same count formula we used in the select statement earlier. This keeps this query from triggering when there are no results! Click Preview and confirm that your results should look something like this where '3' is the number of hits that came up the FIRST time you Previewed this query:

"Column1" 3

Click Save and Close and go to Events. Create a new Event that uses the query you just made and select the Reports tab. Add the report that you wanted to be conditional but didn't want to send with zero results. Remember to set the Output Type/Style. Finally, draft your Email in the email tab, add subscribers in the subscriber tab, and schedule the report. That's it, no more reports without data! If there is no report sent, your recipients will know that there was no data generated by that report for that day.