Hi, all. I have a question regarding filters. This is probably an elementary question, but I don’t remember how this works. I have a result set that has the following: Dim1 Dim2 Dim3 Count. The count in the cube is always 1. When I do my report, I only use 2 of the Dimensions, so my Count sums up. I now want to filter on records in the report where the Count is greater than 1. When I filter on Count > 1, I get no records, since all records in the cube have a count of 1. When I try to filter on =sum() > 1, I get an error that I can’t use aggregates in a complex filter. When I try using a variable, the variable is not available to filter on. How do I do this?
Option 1:
Modify your data provider, remove the unused dimension and add condition “countmeasure > 1” (this will create a having clause in the underlying SQL)
Option 2:
Create a local report variable “Flag” defined as:
IF Countmeasure > 1 THEN 1 ELSE 0
Then rank on your new local report variable “FLAG” (display only the top 1).
Well, I must be doing something wrong. I tried this, but if I define my variable as a dimension, the only values I get are 0. If I change it to say 'If Sum(Countmeasure) > 1 THEN 1 Else 0, my variable has the right values, but I can’t rank on it, because it gets changed to a measure and I can’t change it back. Any ideas what I’m doing wrong?
Got it, thanks. It generally worked, although I got a few extra records, and am not sure why. But I’ll look at it further and figure it out. But what an odd way to have to do it! I wonder why Business Objects doesn’t have a function to allow you to filter on the report level, rather than the cube level. That would seem to be a almost essential function. I may put in an enhancement request for that. Thanks for your help!
It’s done that way to prevent an infinite loop. You can filter on the report level, you just can’t filter on an aggregate. That’s because the filters define the aggregate, not the other way around.
Process:
Return data to the cube
Apply filter
Display data
Calculate aggregates
If you tried to apply a filter after displaying the aggregates, you would loop back to step 2 and have to recalculate your aggregate. At which point you would have to loop back to step 2 and apply your filter all over again.
One work-around is to use the “rank as a filter” trick that has been documented many times on the listserv / Bob. Or you can do a sub-query so that only the values you are actually interested in are returned. Or - as you suggest - BusObj could rewrite their report engine to be smart enough to avoid the loop and provide this feature. But that will take a while.
Thanks for the explanation. I wasn’t clear of the steps and that does help explain it. I guess what I was thinking is that perhaps BO could expand their tool/code to create a sub-cube at the report level, so filters could be applied there. And it seems like there would be other things, like some variable operations, that could benefit from this as well. The way I look at it, if people have had to figure out tricks to work around it, there is a need for a clearer way to do it in the tool. Silly me, huh?
(BOB member since 2002-06-20)
(BOB member since 2002-08-08)
But that will take a while.
(BOB member since 2002-06-06)