If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Top data instance filter

Started by MKlepper, 20 Aug 2008 10:43:55 AM

Previous topic - Next topic

MKlepper

I have a report that displays Subscriber numbers and claim numbers filtered down to five groups. There are anywhere from 1 to 10+ claim numbers per subscriber number. Some of these claim numbers are completely different while others only differ in the last to digits (xxxxxxxx00, xxxxxxxx01, etc). I need to filter the claim numbers as such:

If there are multiple claim numbers contained within a subscriber number, the claim numbers that are completely different need to be returned (xyxyyyxy00, yxyyxxyx00, etc) while the claim numbers that only differ in the last two digits filter to only the highest instance.

Example 1:
Sbr No = 123321

Clm No contained in db = yxyxyxyx00, xyxyxyxy00, yyxxxxyx00, xxyyyxxx00

The above should filter as is since they are all completely different.

Example 2:
Sbr No = 321123

Clm No in db = xxxxxxxx00, xxxxxxxx01, xxxxxxxx03

The above should filter to only show xxxxxxxx03.

I have tried the following as my filter: maximum(distinct[Claim Number] for [Subscriber Number]) but this only gives me the maximum for each Sbr No regardless of how the claim numbers look (in Example 1 above, the filter returns only xxyyyxxx00 instead of all claim numbers).

Does anyone have any ideas on how I can accomplish this?

PlayCognos

#1
just a suggestion:

You could try using Substring or SUBSTR depending on what your database is (SQL server or Oracle) to pick the first 6 characters. If you do a distinct of that, then for each value selected, you display the max value just like you are doing now.

PC