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 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
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.
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.