Is there a way around not being able to filter on measures?

[toptenor]

I work for local government. We place a number of measures within a report, user-defined, to capture such things as minimum start dates where the same person has multiple start dates, or if a Service User (SU) has had a care plan (CP) copied to them for a number seperate transactions.

For this second example we would put a user-defined measure that firstly sums the number of <CP copied > entries (0 = not copied, 1 = copied) using the formula “=Sum(<cp copied>) In <Person Id>”. We cannot make this a dimension – the Business Objects program insists on it being a measure.

Then we would use a formula “= If <CP COPIED SUM> >=1 Then "Some CP copied" Else "No CP copied to SU" ” to give us a column that shows whether some care plan has been copied or not. Again this formula will only give a measure and cannot therefore be filtered in the final table. We therefore have to find other ways to show SU’s that have never had a CP copied to them (usually using alerters).

Does anyone know of a way to define these variables without them being measures or is there a way to place a filter on a measure?

We use Business Objects 5.1

 

[skrandel]

Don't filter them. Use a ranking trick.

Define a variable. = If <CP COPIED SUM> >=1 then 1 else 0

Put a ranking on SU an choose the top 1 based on your new variable.

It works very well.

Steve Krandel
VERITAS Software

 

[toptenor]

Thanks Steve,

We already know this method - just get fed up with writing endless variables and wondered if there was another way around it!

Jon Meredith
(toptenor)

 

[bertdijks]

Sometime ago I had the same problem, this was my solution:

Replace {= If <CP COPIED SUM> >=1 Then "Some CP copied" Else "No CP copied to SU" ” }
with {= If <CP COPIED SUM> >=1 Then 1 Else 0} and use the ranking function to show only the highest values. (top 1 will probably work, otherwise try top 2 or top 3)