Rank Filter Issue

[Lynn]

Hello all,
I have a report that is attempting to find the bottom 10 performers based on a calculated percentage. It uses a relational package published in dynamic query mode. We are using 11.0.5.

The technique I normally do is to create a query item that shows the rank, and then use that query item as an after auto aggregation filter. Imagine my surprise when this resulted in no data at all returned, even though I can clearly see the ranked items were properly determined before applying the filter.

The query has a few other before auto aggregation filters which don't cause any harm, but it does have another after auto aggregation filter which seems to have something to do with the problem. If I disable that filter or set it to before after auto aggregation I get all 10 ranks returned properly. Obviously I can't just eliminate that filter or change the usage.

I've mocked this up using the samples package which I understand is also published using DQM. I've looked at the SQL but don't really understand what might be causing the problem nor how to solve it. In my samples mock up, I end up with only ranked items 1 to 7 rather than no data at all, but I think it is a comparable illustration because I'm losing data that should be coming back in my result set.

As further explanation of the attached, there are three lists side-by-side:

- The first shows all product types along with planned revenue, revenue and a percentage of the two. This calculated percentage is then used in a rank ASC query item. I've highlighted products with planned revenue less than $99M as I want these excluded from the ranking.

- The second list shows the same thing, but with those low revenue products filtered out using an after auto aggregation filter. This eliminates golf accessories, first aid, and safety that would otherwise be ranked among the bottom 10.

- The third list is the same as the second, but with the additional filter to include only ranks <= 10. You'll notice I only get the first 7 rather than all 10 that I am expecting.

I have tried playing with the aggregation settings on the rank item as well as using a summary filter with a scope and other variations of the rank filter expression but have not hit on a solution.

If anyone has the time or interest to take a look I will be most appreciative. I'm down to desperate thoughts like using a conditional style to hide all the > 10 rank rows. Either that or perhaps retirement might be the right decision ;)

[Lynn]

By way of update, I found a CQM package for Go Sales on version 10.2.2 and the situation is exactly the same. I was thinking it might be a difference between CQM and DQM but that is not the case. I've attached a 10.2.2 version of the report spec here.

I've been looking at the SQL but have not figured out why it is behaving differently than what I expect.

[Lynn]

Hello again.

I have learned that ranking over a package item rather than a calculation works exactly as expected even with another "after auto aggregation" filter in the query. It seems that the combination of the calculated item in a rank and the other "after" filter leads to a result that doesn't look sensible to me.  I have looked at the generated SQL but am none the wiser for the effort since it looks like appropriate SQL.

As a work around, I removed the rank filter and created a new query as reference from the original. I then put the rank filter into this second query and all is as expected. Attached is a revised spec with this work around in place.

Although this gives the result I am after, it is an overly complicated solution IMHO and not one I will enjoy explaining to an average business user who is just learning how to author reports. I can't really explain why the first approach doesn't work as expected nor why this extra layer of obfuscation is necessary to solve the problem.

If anyone can explain further or provide a more elegant solution I would be most appreciative.

Thanks!

[bdbits]

*** listening to the crickets chirping ***    ;D

Not sure what is going on there, but I agree that is a poor user experience. Since you have a demo of the problem off of Go Sales, I would open a ticket and see what they have to say about it. Worth a shot, I think.

I am not where I can get to Cognos to view your examples (and hence SQL), but I wonder if the SQL might look OK because there is local processing going on. If so looking at the SQL might not be giving you a complete picture. If you copy/paste the SQL into a native query tool, does that return the results you would expect?

There just has a to be a better way, as I would think this is a pretty common scenario for some businesses.

[Lynn]

Thanks! I was beginning to feel a bit lonely  ;D

I looked at both the native SQL and the Cognos SQL but couldn't discern the issue. I will take your advice and open a ticket. I'll adapt my example to include a working example using a package query item and a non-working example using the calculation.

If I manage to find a better way I will post back.