Condition in Event Studio

[blackrobin81]

My Requirement is as follows :
We have a package. In that package there are 3 namespaces namely a,b,c Each namespace has about 10 query subjects with one measure and all the three query subjects are not related to each other.
We want to write a condition in event studio based on which the report would be triggered.

Pre-requisistes for the conditions are as follows.
1) If namespace a has a query subject named revenue with value = 100000
2) namespace b has query subject sales with value = 5000
3) namespace c has a query subject quantity with value = 4000

If value of all three query subjects (given above) in each of the namespace reaches the thereshold value (value specified) then the report should be triggered. i.e if
If revenue = 100000 and sales = 5000 and value = 4000 then only report should be triggered.

If any one of the query subject dose not match the threshold value then the report should not be triggered
e.g if revenue = 100000 and sales = 3000 and quantity = 4000 then report should not be triggered.

We want to write a condition only when all three query subjects match their threshold value.

Please kindly help as this we are going LIVE in 2 days time and we have not found out a solution.

 

[blom0344]

Query subjects do not 'contain' values. They may generate a value when used in a query/report based on the underlying data present.
I would solve this within the database by using a view like:

conceptual:

CREATE VIEW V_THRESHOLD
AS
SELECT SUM(THRESHOLD_VALUE) FROM
(SELECT 
CASE WHEN SUM(REVENUE) >= 100000 THEN 1 ELSE 0 END
AS THRESHOLD_VALUE
FROM REVENUE_TABLE
UNION ALL
SELECT 
CASE WHEN SUM(SALES) >= 5000 THEN 1 ELSE 0 END
AS THRESHOLD_VALUE
FROM SALES_TABLE
UNION ALL
SELECT 
CASE WHEN SUM(QUANTITY) >= 4000 THEN 1 ELSE 0 END
AS THRESHOLD_VALUE
FROM QUANTITY_TABLE)

Add the view to the model.

Now define a new event with event condition [THRESHOLD_VALUE = 3]
Add a task that runs the report based on new events.
Schedule an agent with the required frequency to check for instance every hour

Ties Blom

 

[blackrobin81]

Hi Ties Blom,
Problem is we dont have access to the database...is there any way by means of which we can add a case condition in event studio.... By writing query subjects i meant the members...

Can u plz explain what do u mean by adding a condition [Threshold_value] = 3.Am completeley new to event studio.
We can only trigger the job provided all the threshold values are met. If any one member does not meet the threshold value then the job is not triggered

 

[blom0344]

You can take the view definition and create an SQL query subject in the model, no problem.
The idea of the view definition is, when the value = 3 then all conditions are met.
So, the entire logic is down to 1 value only.

It is a matter of defining the event condition in Event Studio. I suppose if you want to use ES you know how to define events, tasks and schedules?

Ties Blom