Catch All Condition Variable For BCA

Hi,

I need to create an variable that I can use as a condition to be evaluated when scheduling. I have the condition set as < BCA Date > = 99 . 99 is ‘true’ and that must occur before the report runs.

The variable is defined as

= If InList (1 , 2 , 4 , 5) And = <Weekend (sysdate -3)> Or = Then 99 Else 10

Each source is a system I am recieving data from, I recieve this data at various times. At any point in time a source could either have been feed done on that day and would be a 99 or is still waiting to feed being a 10.

I only want this report to run though when all sources are 99. It kind of works upto a point in that if some sources have finished and some havent, I have 99 and 10 BCA throws up a ‘multivalue error’.

To get the point :expressionless: what I want to do it create a variable that will show either 99 or 10, it will only show 99 when all sources have fed down, but will show 10 if all sources have fed down bar 1.

At the moment it sort of works in that when the condition is not met, I get a multivalue error, this is because the variable returns two results, 99 ng true you can run and 10 your cant run it. I have to assign either 99


Rich :uk: (BOB member since 2002-10-04)

Rich,

If you apply the MIN() function before doing your comparison, will that work? If all of your processing is complete, and all values are set to 99, then MIN() = 99 would be true, otherwise a still processing file would give you MIN() = 10.

Just a thought.


digpen :us: (BOB member since 2002-08-15)

Appologies it did work, thanks for the idea.

What I did in the end was leave the original variable as is. I then created another variable called ‘BCAgo’ with the following def

=Min() In Block

This picks the min value from the block so if I have all 99’s and one 10, it will pick the 10. I then use this object in the condition,

BCAgo = 99

Cheers Digpen mucho appreciatoo
:mrgreen:


Rich :uk: (BOB member since 2002-10-04)

Have you tried placing the Min() in the header to see if it generates correctly? As far as I know using an aggregate without an parameters implies the IN BODY, IN REPORT type context and should give you a single value.

The only way an IN REPORT value would determine if the report should refresh is if the report refreshes in the first place… and it would decide on whether to save or not based on the value. Is that correct?

Otherwise… I’d consider basing your data off a view that summarizes your data and maybe an outer select condition that includes HAVING MIN(FILTEROBJ) = 99

With your FILTEROBJ being a case statement that handles your groupings.


digpen :us: (BOB member since 2002-08-15)

Hi Digpen, it did work was just updating my post saying it did when you were replying. See below for how I did in the end using min :mrgreen: :mrgreen:


Rich :uk: (BOB member since 2002-10-04)

Awesome. Glad it worked out.


digpen :us: (BOB member since 2002-08-15)